As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at email@example.com and provide your corporate email address, company, and name.
We have a report which takes absolutely forever to run, commonly it will time out. When I query the information in oracle though the data comes back very fast. I can only assume there is something going on with WF to hinder its performance.
Any ideas of where I should even start looking? I've checked the forum already, several pieces of documentation on the tech doc library and I'm running out of options.
Thanks for taking a look at this. Appreciated, Shawna
Perhaps you should try enabling tracing in WebFOCUS, capturing the SQL statement and run that instead in your SQL tool. You'll take a look at the execution plan (compared to the one you wrote yourself) and see where the differences are.
Many times the slowdown you notice lie on the fetching of the whole set of rows. If you ask me, 370,000+ rows seems like a significant chunk of data to read over from Oracle to WebFOCUS to then be styled. It may not even be the reporting engine but your browser choking when trying to display an HTML table with that many number of rows.
Many things to consider but again, I would start at evaluating the SQL created by the iWay adapter in the first place and take it from there. You'll want to see that all filters are being passed to the database to handle, as opposed to WebFOCUS trying to read everything and filtering after on the reporting server side, which I've seen happening very frequently in poorly-written WebFOCUS code.
Strange that you do not see it... You are running against an SQL database, right? Well, if it is not there, the next thing you can do is entre TRACE commands in your FEX. Then with "View Source", you can see how WF generates SQL:
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006
The other thing you might want to do is have a look at your agents console and see where the work is being done. Generally at http://servername:8121/bcstart.html See how many rows it's pulling from Oracle. If it's pulling more than you expect then it's almost assuredly applying some of your where clauses after the SQL call on the EDASERVER and that will be slower for a number of reasons.
If you want to get really tricky, get the agent number that the request is running under (from the agent console I mentioned above). Log on to your server as a user and go to that agent's temporary directory. Generally installed in \ibi\srv77\wfs\edatemp\tsxxxxxx where the Xs are the agent's number. Since this thing takes all day to run you have time to go to that directory and refresh it repeatedly to see if temporary files are being created. If Oracle is doing all the work you should see nothing but stderr.log and stdout.log in that folder. If EDA is bringing material back in pieces and reassembling them itself, you'll see other files appear (and disappear) as the process proceeds. That's an indication that something you've coded has pushed the Go-Slow button.
I had an I-Way process with a DEFINE field on one of its sources. I didn't even use the DEFINE field -- it was just along for the ride. For some reason it switched off the pass-through option and I had 11 temporary FOCUS files being created for a painfully simple denormalization routine. Big ones too -- 40GB worth and the process took an hour to run. I moved that DEFINE to the target table and it ran in 60 seconds with no temporary files.
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007