November 09, 2007, 12:26 PM
Fransuse 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?
November 09, 2007, 12:26 PM
Rob M.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?
November 09, 2007, 12:28 PM
Rob M.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
November 09, 2007, 12:35 PM
TexasStingrayHave 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
November 09, 2007, 01:07 PM
Leahquote:
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
November 09, 2007, 01:39 PM
Spenceyou may want to try using sql passthru
WF 8 version 8.2.04. Windows.
In focus since 1990.
November 09, 2007, 01:50 PM
Darin LeeBest 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
November 09, 2007, 04:57 PM
GinnyJakesFYI, 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
November 12, 2007, 08:42 AM
linusTry 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
November 12, 2007, 09:43 AM
cburttNote: 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.
November 13, 2007, 09:55 AM
Jason K.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.
November 13, 2007, 11:40 AM
hammo1jRob 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
November 14, 2007, 05:32 AM
Fransquote:
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.
November 15, 2007, 09:47 AM
jgelonaI 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.
November 15, 2007, 10:21 AM
Francis MarianiThis 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
November 16, 2007, 04:12 AM
FransI'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.
November 16, 2007, 10:24 AM
GinnyJakesIt 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
November 19, 2007, 12:02 PM
jgelonaGinny, 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.