Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Execution time : FOCUS taking 5 times what SQL does
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Execution time : FOCUS taking 5 times what SQL does
 Login/Join
 
Virtuoso
posted
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,


WF 7.7.05M, 8.1.05M, 8.2.0.1M gen240 Windows, HTML, Excel, PDF
In Focus since 2007
 
Posts: 1794 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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?


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
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 ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.01OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 5914 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WF 7.7.05M, 8.1.05M, 8.2.0.1M gen240 Windows, HTML, Excel, PDF
In Focus since 2007
 
Posts: 1794 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1924 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, IBM DB2, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Hadoop.
 
Posts: 311 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WF 7.7.05M, 8.1.05M, 8.2.0.1M gen240 Windows, HTML, Excel, PDF
In Focus since 2007
 
Posts: 1794 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Execution time : FOCUS taking 5 times what SQL does

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.