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.,

The CONTAINS maybe slowing you down. Waht does the SQL that is being generated and sent to the RDBMS.

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?

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?

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

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.

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.

you may want to try using sql passthru

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)

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.

Try changing your where statement to
WHERE EQNUM LIKE 'BALER%'

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.

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.

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!

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.

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.         *-
-*                                                                     *-

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,

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.

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, 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.