We have a report with multiple sql statements pulling JDEdwards data. This report is extremely slow. We've had dbas review and tune the sql statements being used. I noticed that there are 50+ computes in the code. Would this have an affect on performance?

So the computes are in FOCUS code that is run after the SQL queries?

COMPUTEs are run on the entire data set AFTER all retrieval and sorting is complete. They are not passed in the SQL request. However, this is "usually" a pretty quick process - subject to the quantity of data being processed. The sorting and holding of data from the SQL tables could be a cause of the delay.

If you just run the SQL statements in your procedure vs. running them in a native SQL tool, how does the time compare? Also, are you running the queries in passthru mode or is WF trying to parse and resend your SQL queries based on a master file description?

Lots of different things that could be causing a problem. See if any of these indicates a problem.

Yes, the computes are in focus code that is run after the sql passthroughs. I'll try your suggestions and repost. Thanks

It takes around 2 minutes for the first large sql statement to execute from within pl/sql developer and also around 2 minutes from within WebFOCUS as a sql passthrough. Also, every hold file, except for the first which is from the large sql statement, is being held as format alpha or format focus index. With the specific params I have defaulted in the report, the main data set generated from the first large sql passthrough is only 600+ rows of data. We are outputting the final report to an excel template.

For large volumes of data wf MATCH (equivalent to sort merge in relational terms) is usually quicker than rdbms joins.

Lots of joins and Oracle tends to lose its way. What is your underlying rdbms.

So is the 2 minutes the long running part or is the stuff after the initial request what is taking too long? Remember that every HOLD is a write to disk so if there are thousands of records or many hold files, that's a significant delay - especially with indexing. However, only 600 records should not be that significant. You do mention that you have multiple sql statements. Where do those come in?

John - interesting comment because I have found the opposite to be true. Maybe it depends on the RDBMS - ours is mostly DB2 with some SQL Server.

We are using Oracle 10g. The sql passthroughs take around 3 minutes total to return. The rest of the report takes another 3 minutes. The 3 sql passthroughs are first, each creating a hold file. Then there are defines and sorts performed on the hold files. There are 3 sort by fields and then computes done on one of the last table commands which has 103 total column headings (including computes) and is output to the excel template. Then there are a few additional table commands using smaller hold files that are outputting to different tabs in the excel template. There aren't any significant joins being done. I just ran the report and it took 6 minutes for 107 records.
Thanks for the replies so far

You can't really go by the end number of records. You could run a report on 10 million records with WHERE TOTALs and get 107 resulting records or run on 200 records and result in 107.

Half of that time (3 minutes) is SQL passthru so that's out of our realm (your DBA may be able to further reduce that time with some optimization). We don't know exactly how many records each of the SQL queries produces. then you create some hold files (read disk I/O time)Then you're sorting/selecting/computing to create 1 report with 103 columns along with "a few additional" reports that are output and putting them all in an excel template. That's a lot of stuff! I don't think you're too far outside normal processing time. If you just output them all to HTML does it take any less time?

This all depends on things you may have in your code (like WHERE TOTALs, selection on DEFINEd or COMPUTEd fields, lots of sorting, etc. Without going through the whole processing stream, I don't know if we'll be able to find any shortcuts. Maybe you could get some ideas from one of the SE's in you local IBI branch.

I'll have the dbas take another look. Thanks to you both for your input.

Check your WHERE statements and index the fields if possible. If you are not sorting the output in Webfocus use TABLEF instead of TABLE. Do you have any LIKE conditions in your WHEREs?

Have you run with xtrieval off to see what is being generated in the focus code?

I find it helpful to get the time each step takes. I put the following code around each SQL Passthru or Focus request and increment &TIME and STEP. This helps pinpoint the problem code.

-SET &TIME01 = HHMMSS('A8');
-TYPE STEP1 START TIME = &TIME01
-RUN
-****************
Code
-****************
-RUN
-SET &TIME02 = HHMMSS('A8');
-TYPE STEP1 END TIME = &TIME02
-RUN
-*