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     How can I speed up my data retrieval and prevent it from timing out?
Go
New
Search
Notify
Tools
Reply
  
How can I speed up my data retrieval and prevent it from timing out?
 Login/Join
 
Gold member
posted
I am querying against a work order database for stores.

My parameters prompt for STORE NUMBER and EQUIPMENT TYPE. (Work orders are written to a specific Equipment type). The other piece of my WHERE criteria is the Work Order Type must be either "CM" or "EM".

Below is my code, is there anything I can do to speed it up? Unfortunately I cannot narrow it down to a date range because I need all data on the equipment (our data goes back to 2003).



TABLE FILE T_PROD_WORKORDER
SUM
SITEID
PONUM
EQNUM
INVOICENUM
VENDORINVOICENUM
VENDOR
T_PROD_INVOICE.STATUS AS 'INVOICE STATUS'
REPORTDATE/HMDYY AS 'WO CREATION DATE'
STATUSDATE/HMDYY AS 'WO STATUS DATE'
REGULARHRS
T_PROD_INVOICE.TOTALCOST/P12.2CM AS 'INVOICE TOTAL'
T_PROD_INVOICECOST.GLDEBITACCT AS 'INVOICE GL'
DESCRIPTION
WORKTYPE
WO3 AS 'SAT'
WO11 AS 'NTE'
WO12 AS 'NNTE'
STATUS AS 'WO STATUS'
BY WONUM
WHERE ( SITEID EQ '&SITEID.Enter SITEID (ex: T0001).' ) AND ( EQNUM CONTAINS '&EQNUM.Enter Equipment Number (ex: BALER).' ) AND ( WORKTYPE EQ 'CM' OR 'EM' );

I am using WF 7.1.4

This message has been edited. Last edited by: Rob M.,


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Reply With QuoteReport This Post
Master
posted Hide Post
The CONTAINS maybe slowing you down. Waht does the SQL that is being generated and sent to the RDBMS.




Scott

 
Posts: 865 | Registered: May 24, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
use TABLEF instead of TABLE.

Also very important is your join structure if you're using that.

Sorting can also slow down performance, especially varchar's.

What kind of database are you using?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 435 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
Originally posted by TexasStingray:
The CONTAINS maybe slowing you down. Waht does the SQL that is being generated and sent to the RDBMS.


I need to use CONTAINS because there may be similarily named equipment. Example: The primary piece of equipment is BALER, but there also could be work orders for the equipment named BALER1, BALER2, etc.

So what are my alternatives?


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
Originally posted by Frans:
use TABLEF instead of TABLE.

Also very important is your join structure if you're using that.

Sorting can also slow down performance, especially varchar's.

What kind of database are you using?



I will try not sorting it, it is an Oracle Database.


My Join looks like this:

JOIN
LEFT_OUTER T_PROD_WORKORDER.T_PROD_WORKORDER.WONUM IN T_PROD_WORKORDER
TO MULTIPLE T_PROD_POLINE.T_PROD_POLINE.REFWO IN T_PROD_POLINE AS J0
END
JOIN
LEFT_OUTER T_PROD_POLINE.T_PROD_POLINE.PONUM IN T_PROD_WORKORDER TO UNIQUE
T_PROD_PO.T_PROD_PO.PONUM IN T_PROD_PO AS J1
END
JOIN
LEFT_OUTER T_PROD_PO.T_PROD_PO.PONUM IN T_PROD_WORKORDER TO UNIQUE
T_PROD_INVOICE.T_PROD_INVOICE.PONUM IN T_PROD_INVOICE AS J2
END
JOIN
LEFT_OUTER T_PROD_INVOICE.T_PROD_INVOICE.INVOICENUM IN T_PROD_WORKORDER
TO UNIQUE T_PROD_INVOICECOST.T_PROD_INVOICECOST.INVOICENUM
IN T_PROD_INVOICECOST AS J3
END
JOIN
LEFT_OUTER T_PROD_WORKORDER.T_PROD_WORKORDER.WONUM IN T_PROD_WORKORDER
TO MULTIPLE T_PROD_LABTRANS.T_PROD_LABTRANS.REFWO IN T_PROD_LABTRANS AS J4
END


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Reply With QuoteReport This Post
Master
posted Hide Post
Have to view the SQL generated?

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

Key is to SET XRETRIEVAL = OFF

This way it will just show the SQL not run it.




Scott

 
Posts: 865 | Registered: May 24, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
STATUS AS 'WO STATUS'
BY WONUM



I do believe TABLEF won't work with a by clause. It's possible you could have your Data Base Administrator set up indexes on your primary selection fields. This helps some in certain types of data bases.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
you may want to try using sql passthru


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Best bet, as Texas suggests, is to get an SQL trace and see if it is as efficient as possible. One suggestion - probably as my personal preference - is to simplify your WHERE. I absolutely avoid compound WHERE statements whenever possible, especially if one of them is something other than a sraight equality test (EQ,LT,GT,LE,GE) like your CONTAINS.
Probably stems from my days with a legacy data source (ADABAS). This just seemed to confuse the data driver to no end and never seemed to get a fully efficient query. Lots of little details I can explain if you would like, but try it and see if it makes a difference. (May not with and RDBMS like Oracle)


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
FYI, when reporting against a relational source like Oracle and the sorting is done by Oracle, AUTOTABLEF is on by default and the answer set returned from Oracle will not be resorted.

I heartily agree with Texas and Darin. Turn those traces on and see what you are sending to Oracle. Make sure that your output format is html (at least for now) and as Texas said, set XRETRIEVAL OFF. When you get your empty report, right-click and do a view source. Scroll down to the bottom and you will see the SQL along with some FOCUS messages telling you whether you have issues or not.

Depending on the kind of relational data you are accessing, optimization can be turned off if you are summing alpha fields as you are doing. This is a definite no-no for Teradata. Don't know about Oracle.

The traces will also show you if you are sending multiple or a single SQL statement to Oracle. Single, good. Multiple, bad.

As you can tell, I work with my users on improving efficiency all the time so I know the drill. Anyway, post the results of your trace, and maybe we'll need the masters later, and then we can see what the problem is.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Try changing your where statement to
WHERE EQNUM LIKE 'BALER%'


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Note: When querying an RDB, if your WHERE fields aren't indexed, you end up performing a sequenctial search through your JOINed virtual table. And, if the JOIN ... TO ... fields aren't indexed you could be doing a sequential search through the child table(s) as well to find the matching row in each subordinate table. If your RDBs are big, this can really impact elapsed time.


WIN/2K running WF 7.6.4
Development via DevStudio 7.6.4, MRE, TextEditor.
Data is Oracle, MS-SQL.
 
Posts: 154 | Location: NY | Registered: October 27, 2005Reply With QuoteReport This Post
Master
posted Hide Post
quote:
SITEID EQ '&SITEID.Enter SITEID (ex: T0001).' ) AND ( EQNUM CONTAINS '&EQNUM.Enter Equipment Number (ex: BALER).' ) AND ( WORKTYPE EQ 'CM' OR 'EM' );


create indexes as follows.
btree: siteid, eqnum
bitmap: worktype

don't use a wildcard at the beginning of a field if you can avoid it. using a LIKE '%x%' (or webfocus CONTAINS 'x').

passing the SQL generated (using texasstingray's method posted above) in enterprise manager, looking at the query plan and seeing where the issues are is the first step. If it says full table scan anywhere, something isn't right (or you're trying to return more than about 20% of the table, in which case the optimizer says 'scan the whole thing, it is faster to scan the whole darned thing than to have to wack the index that many times')

query optimization is a big, broad subject.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Reply With QuoteReport This Post
Master
posted Hide Post
Rob M

I didnt know that Maximo was available under Oracle!

I know the database and I can see the problem straight away.

You must process the POL/PO/Invoice summation separately to the Labtrans summation and use MATCH to relate via the WONUM otherwise you will have a two part join done via a stored procedure which is causing your performance hit.

-* same joins

MATCH FILE T_PROD_WORKORDER
SUM
SITEID
PONUM
EQNUM
INVOICENUM
VENDORINVOICENUM
VENDOR
T_PROD_INVOICE.STATUS AS 'INVOICE STATUS'
REPORTDATE/HMDYY AS 'WO CREATION DATE'
STATUSDATE/HMDYY AS 'WO STATUS DATE'
-*REGULARHRS
T_PROD_INVOICE.TOTALCOST/P12.2CM AS 'INVOICE TOTAL'
T_PROD_INVOICECOST.GLDEBITACCT AS 'INVOICE GL'
DESCRIPTION
WORKTYPE
WO3 AS 'SAT'
WO11 AS 'NTE'
WO12 AS 'NNTE'
STATUS AS 'WO STATUS'
BY WONUM
WHERE ( SITEID EQ '&SITEID.Enter SITEID (ex: T0001).' ) AND ( EQNUM CONTAINS '&EQNUM.Enter Equipment Number (ex: BALER).' ) AND ( WORKTYPE EQ 'CM' OR 'EM' );
RUN
FILE T_PROD_WORKORDER
SUM
REGULARHRS
BY WONUM
WHERE ( SITEID EQ '&SITEID.Enter SITEID (ex: T0001).' ) AND ( EQNUM CONTAINS '&EQNUM.Enter Equipment Number (ex: BALER).' ) AND ( WORKTYPE EQ 'CM' OR 'EM' );
AFTER MATCH HOLD OLD-OR-NEW
END

TABLE FILE HOLD ...

should do it!



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, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
quote:
Originally posted by Leah:

I do believe TABLEF won't work with a by clause.


As long as the sorting is done by the RDMS then TABLEF will work.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 435 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Master
posted Hide Post
I use TABLEF with BY all the time. I have found it to save lots of time, in almost every circumstance, over using TABLE with BY when getting data from an RDBMS. I post the following in my programs for those that follow me:
-* These FOCUS programs have been optimized to run with the Oracle     *-
-* DBMS.  The syntax is not consistent with a pure FOCUS environment.  *-
-* For example, there are many instances where TABLEF is used with     *-
-* one or more BY phrases to produce sorted extract files.  In these   *-
-* cases the Oracle DBMS is sorting the data and by using TABLEF,      *-
-* FOCUS does not resort the data.  Please refer to the FOCUS for UNIX *-
-* Interface to Oracle, Users Manual for a full explantion.  Changing  *-
-* this FEX's without understanding how the FOCUS Interface operates   *-
-* may yield incorrect results and degrade system performance.         *-
-*                                                                     *-


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 956 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
This is very interesting.

Is the rule as simple as this: If the BY statements are all real table columns (no DEFINE fields) then TABLEF can be used with BY?

And this improves performance? This is good news to me.

Thanks,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
I'm not sure if it works in all cases. At least not with BY FIELD ROWS and FOCUS files. You can check the SQL syntax to be sure and if you see this kind of output:

0 NUMBER OF RECORDS IN TABLE= 290 LINES= 290

Then the sorting has been done by the RDMS.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 435 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
It is my understanding that AUTOTABLEF is ON by default and that the output from a relational query will not be resorted by WebFOCUS.

Maybe someone from IBI can weigh in on this.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Master
posted Hide Post
Ginny, in our environment AUTOTABLEF is ON, howerver, while TABLE may not resort the data, it still builds FOCSORT, where TABLEF does not.

Francis, that has been my experience. As long as all the BY fields are real database fields, TABLEF will cause the RDBMS to sort the data and WebFOCUS/FOCUS will not resort and not build FOCSORT.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 956 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How can I speed up my data retrieval and prevent it from timing out?

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