Hi all,

I'm trying to have some ideas on where to look at and what to search.

I have a TABLE FILE that use a complex cluster master file with several join in it.

When I execute the IB generated SQL directly from my SQL server it takes about 1 minute to retrieve over 1.9M rows.

When I execute from the TABLE FILE itself (from IB client), it takes over 5 minutes to return the same result.

I have deactivated any traces (ECHO and SQL traces) thinking that it could be the issue, but with no luck. It's not that.
I have check the master file and it look ok.

Any other ideas where should I search and how to identify why it's taking 5 times what it takes to executed in SQL ?
It's the first time that I have this situation where the execution time is so different. I already had few seconds due to connexion between servers, but that much it's new.

Regards,

This message has been edited. Last edited by: MartinY,

Are the client and the server installed on the same machine or on different machines? This could be network traffic related.

Also, are you using Tomcat? Are you using AHTML? Are you creating JSCharts? Is the code HOLDING data? How much memory is assigned to Tomcat?

Does it take 1 minute to return all 1.9 million rows, or is it in fact taking 1 minute to start returning the rows ?

Client and Server are on the same machine.
This is just the data extracting part. To hold the data.
Tomcat is at 4G.
SQL is taking about 1 minute to return the whole 1.9M rows when IB takes over 5 minutes to hold the same data.

I have also check if I can change some DEFINE by a COMPUTE or move them in another step.
By doing that I'm now at about 2 minutes in IB vs 1 minute in SQL which is much better but still twice the execution time.

So, you're HOLDing the answer set. Is this HOLD file format FOCUS or some other format or just a binary file? I'm not sure comparing a SQL Select is a fair comparison with TABLE FILE ON TABLE HOLD. There's a cost to writing 1.9M rows into a disk file especially if you're writing it into a DBMS file.

Check the SQL code, and the attendant messages. Is all sorting being passed through to the database server?

If you sort on a DEFINE field that WF cannot pass through, then the sorting absent in the SQL code must be performed downstream on the WF server.

Assuming you used TABLE rather then TABLEF, then (regardless of the file format of the HOLD) there is significant processing in WF: The rows of data received from DB server are loaded into the internal matrix (which takes the form of a FOCUS file), with sort processing (think MODIFY FILE...), and finally pealed off into whatever HOLD format you specify. MODIFY can run very slow if the data rows from the server are not conformably presorted.

With retrieve, do you mean to retrieve it in MS SQL Studio or write it to a csv file?

How high is the fetchsize configured at the adapter settings? Thats at least something to play with, set it to 5000. How is your cursortype set?

Are you using ODBC or JDBC?

Thanks all for your advise.

I finally made several changes into the fex to have it more Adhoc : to avoid too many detailed data depending on user selection.

As per example, instead of selecting every sales by SKU (the lowest level) to then be aggregated by month as the user requested it (so the SKU level is "removed"), I test if the user select to display the SKU and if not, I already extract the data at month level.

Now the whole report is generated within 15 sec.

I had to make many changes and I now have to be sure that those changes still produce the same result as previously no matter the option selected but I'm on the good track.
If user requested the report to be displayed by SKU, it will still needs a lot of time to process but this it's "normal" due to the nature of requested data.

Once again thanks.