Focal Point Banner


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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Execution time : FOCUS taking 5 times what SQL does
 Login/Join
 
MartinY
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 versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
BabakNYC
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 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Waz
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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
MartinY
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 versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
BabakNYC
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 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
j.gross
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: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Frans
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, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
MartinY
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 versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders