Help with large queries

quote:
I'm only using a PRINT statement on the fields I need then an summing it down after it's in a hold file

Do you need the detail pulled or do you just need the summation. If you do a TABLE and are summing on 'real' fields, the summary is performed at the data base level and returned 'summed'. If you just need the summation try that. If you have defined fields however, and perhaps are selecting on them, then the processing all occurs at the local level after data is brought back. Sometimes the issue is at the DB2 end as well, might check with your DBA.

Leah

Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004

Ignored post by Leah posted

Platinum Member

posted

I try and do all the summing at the WF end and not the database level so it doesn't have to do more work then it already does. Just from testing it seems like not using the SUMmed option on the inital table request pulls the data faster.

I do have defined fields but they are definated after I have manipulated the hold file to a workable file.

We are just using DB2 as the connector between WF and our AS400 which I think may need to be optimized? I'm the one who installed the DB2 connector so if anything needs to be changed I would have to make the change myself.

Eric

dev: WF 7.6.5 w/IIS + Tomcat

prod: WF 7.6.5 w/IIS + Tomcat

Posts: 153 | Location: Chicago, IL | Registered: February 24, 2006

Ignored post by .eric posted

susannah

Expert

posted

.eric, where is your wfserver actually? and where is your DB2 adapter physically? You don't say what platform you're on. Do you have eda installed on the as400 itself? so that you are running via a REMOTE command?
if so, there's no way 5-10 minutes is reasonable. I run 76 on an AS400, with a variety of different versions on wintel servers and bring down potloads of data in seconds.

Are you perhaps JOINed to some gigantic table that you don't need to be JOINed to? I found that to be the case in a program that i inherited, and the change improved timing from a half hour to pffft.

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Platinum Member

posted

The WF Server and DB2 adapter are on the same server and is connected via gigabit card to our as400 server.

The table I'm running the query is a logical view off 2 seperate files which only contains data/fields needed for a specific report.

While 5-10 minutes isn't all that bad, it is a self-service report which I am always trying to speed up for our users and customers.

dev: WF 7.6.5 w/IIS + Tomcat

prod: WF 7.6.5 w/IIS + Tomcat

Posts: 153 | Location: Chicago, IL | Registered: February 24, 2006

Ignored post by .eric posted

BlueZone

Platinum Member

posted

eric :
If I understand you right, looks like you have your EDA on the mainframe and are retrieving data from AS/400 thru a DB2-Connect communication between the two boxes.

When you have an equivalent SQL statement already available, I suggest that you try the SQL-passthru technique directly as the first step in the Focus code, put the output to a hold file and then let Focus do the hold file processing. As your datasource is on a different box, you may have to give a 3 level or 4 level table name in the SQL.

SQL DB2
SELECT ....
FROM AS4HUN1.AS4DB1.TBL1
WHERE .....
;
TABLE
ON TABLE HOLD AS HOLD1
END
-RUN
-*
TABLE FILE HOLD1
..
-*
Test running the fex with only the first SQL statement,-EXIT. That will tell you if the culprit is the connection or if it the Focus processing via TABLEF.

Sandeep Mamidenna

-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !!

Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006

Ignored post by BlueZone posted

Jason K.

Master

posted

try this...
take your query, and put a count(*) around it. This will force the query optimizer on your 400 to optimize for all rows and also create the ENTIRE cursor set before it returns a value.

so, if you have a query of "Select a.one, a.two, a.three from schema.tablename" then just make it
SELECT COUNT(*) FROM
(Select a.one, a.two, a.three from schema.tablename) MY_SUBQUERY;

You have to label your subquery, the 400 will syntax error otherwise.

If the results from your COUNT(*) take a while to return, then you should definitely look into optimizing the query on your as400.

Simply, if you do a Select * from my_huge_table it will return the first 'page' (my term, not universal) of values within milliseconds, but to query the table is a very different story.

Also, SQL Performance monitors can tell you exactly how long your queries are taking to run on an as400.

Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.

Posts: 611 | Registered: January 04, 2007

Ignored post by Jason K. posted

Platinum Member

posted

If I plug an SQL statment directly into webfocus or the DB2 connector itself it comes back in about 1.5 minutes while the same query run using a TABLEF command takes about 7 minutes. Is there a reason for this?

Also while the SQL query takes about 1.5 minutes through webfocus and comes back in seconds if put directly into the as400, is that normal lag between the two machines?

Eric

dev: WF 7.6.5 w/IIS + Tomcat

prod: WF 7.6.5 w/IIS + Tomcat

Posts: 153 | Location: Chicago, IL | Registered: February 24, 2006

Ignored post by .eric posted

TexasStingray

Master

posted

Eric, Issue the following command before you table request and this will show you the SQL WebFOCUS is generating.

SET TRACEOFF=ALL
SET TRACEON=STMTRACE//CLIENT
SET TRACEON=SQLAGGR//CLIENT
SET TRACEUSER=ON
SET XRETRIEVAL=OFF

This is show you the SQL without actually running the request.
Then you should be able to look at way of tuning it.

Hope this helps

Scott

Posts: 865 | Registered: May 24, 2004

Ignored post by TexasStingray posted

KevinG

Platinum Member

posted

Eric,

I noticed you indicated you are returning 700,000 + records. One of my favorite SQL tools is SQuirreL. It shows stats for performing the query + the stats for Building the output. With large return sets, the building output number can be much larger than the actual query time. Also, returning that number of records to the WF server form the RDBMS can be impacted by the bandwidth between those machines (if different machines). Also there is a tremndous amount of overhead in actually loading that many records into a browser (IE especially...Firefox seems to be a little more optimized).

So, I'm curious...are your measurments apples to apples? Are there bandwidth differences in these comparisons, and are the results being returned to a browser when you are timing them? Are there memory differences in the machines they are being returned on? etc., etc., etc.

Kevin

WF 7.6.10 / WIN-AIX

Posts: 141 | Location: Denver, CO | Registered: December 09, 2005

Ignored post by KevinG posted

Platinum Member

posted

Thanks TexasStingray but I already knew about that, it's producing the same SQL (WF using TABLEF) as the one I'm using in the SQL query.

Kevin, the inital 700k is just the first request, the final report comes out to about 50 lines after the data is cleaned up and summarized. So the HTML isn't slowing it down.

Eric

dev: WF 7.6.5 w/IIS + Tomcat

prod: WF 7.6.5 w/IIS + Tomcat

Posts: 153 | Location: Chicago, IL | Registered: February 24, 2006

Ignored post by .eric posted

hammo1j

Master

posted

Can you post the bones of the request:

1. SQL for the Quick version
2. TABLEF and SQL trace for this.

I don't think we'll get to the bottom of this otherwise - this is quite intriguing!

Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo

Posts: 888 | Location: Airstrip One | Registered: October 06, 2006

Ignored post by hammo1j posted