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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at email@example.com and provide your corporate email address, company, and name.
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.4This message has been edited. Last edited by: Rob M.,
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.
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
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.
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004
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)
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, 2007
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.
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, 2005
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.
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, 2006
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: 975 | Location: Oklahoma City | Registered: October 27, 2006