November 22, 2006, 03:08 PM
Pam Krattslow join
I'm having issues again with a join processing slowly. When I do this join my query slows right down (I have other joins above that are processing quickly). There is about 250,000 records in permissiontypedef. There is 85,000 records in hold200. This needs to be a one to many join. There are only 2 fields in permissiontypedef and there is an alternate index on just the permtype_desc field. If I do a print of the permissiontypedef file, it's not sorted in any order, so I don't know if that's slowing the process down or not. The groupname is a30 and so is permtype_desc a30.
JOIN GROUPNAME IN HOLD200 TO ALL PERMTYPE_DESC IN PERMISSIONTYPEDEF AS G2
Here is the code above and below the join.
TABLE FILE PERMISSIONS
PRINT PERMISSIONVALUE
COMPANY_COMPANYID AS 'GROUP_COMPANYID'
BY GROUPNAME
ON TABLE HOLD AS HOLD200
END
JOIN GROUPNAME IN HOLD200 TO ALL PERMTYPE_DESC IN PERMISSIONTYPEDEF AS G2
TABLE FILE HOLD200
PRINT
GROUPNAME
PERMISSIONVALUE
PERMISSIONTYPEDEF.OID AS 'PTDOID'
PERMTYPE_DESC
GROUP_COMPANYID
ON TABLE HOLD AS HOLD210
END
It was suggested to do the following so I could add a WHERE PERMTYPE_DESC IN FILE SAVE which I did, but I got a (FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT. The groupname has spaces in it, so the documentation says to put quotes around the field.
TABLEF FILE HOLD200
SUM GROUPNAME NOPRINT
BY GROUPNAME
ON TABLE SAVE
END
-RUN
TABLE FILE PERMISSIONTYPEDEF
SUM PERMISSIONTYPEDEF.OID AS 'PTDOID'
BY PERMTYPE_DESC
BY PERMISSIONTYPEDEF.OID NOPRINT
WHERE PERMTYPE_DESC IN FILE SAVE
ON TABLE HOLD AS HOLD300 FORMAT FOCUS INDEX PERMTYPE_DESC
END
So, I added in quotes but now I get a data receive error and I can't seem to fix. I tried only a single | after groupname and that didn't work either. I tried different kinds of quotes with no luck also.
DEFINE FILE HOLD200
GROUPNAME/A32=''''| GROUPNAME ||'''';
END
WebFOCUS Message[2012]: NO_DATA_RECEIVE_ERROR
Error receiving data
An error has been detected during server to client data transfer.
ERROR: -12: Server disconnected, cause 32026 Eda code -12
Any suggestions as to why this join would run slowly? I only have the data until the end of day today so I'm trying to resolve this issue today.
November 22, 2006, 04:03 PM
smithsPam,
Perhaps the following link on the Technical Support site might help:
Slow JoinYou would issue the command such as
ENGINE SQLORA SET OPTIMIZATION SQL (for Oracle).
You may also get some useful info by turning on tracing, as indicated in the link:
set traceuser = on
set traceoff = all
set traceon = sqlaggr//client
set traceon = stmtrace//client
Hope this points you in the right direction.
Sean
November 22, 2006, 05:20 PM
Francis MarianiWhen I'm working with relational databases, I never code my program without checking the SQL generated by WebFOCUS.
I always turn on SQL traces and always start by first turning XRETRIEVAL to OFF - to check the JOINS, SUMS, BY's etc.
SET XRETRIEVAL=OFF
SET TRACEOFF = ALL
SET TRACEON = SQLTRANS
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
In the code you posted, it appears you're joining a HOLD file to another table. If PERMISSIONTYPEDEF is a relational table, then the SQL generated most likely will NOT be efficient.
I always try to do all my JOINS up-front and not join HOLD files to RDBMS tables.
It is possible that WebFOCUS is accessing PERMISSIONTYPEDEF 85,000 times!
Also, when you use a file as a filter (WHERE statement), there are limitations. For SQL, you can have only 1000 values in a single WHERE statement - you've got 85,000. For non SQL, WebFOCUS has a limit on the number of bytes the file size is.
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 23, 2006, 07:14 AM
Tony APam,
Working on the assumption that you are using an external RDBMS and not FOCUS files, then one thing to remember when using a JOIN to a hold file is that FOCUS will retrieve all the records from the child file in the join and then join them within FOCUS itself. This will obviously result in a table space scan of the PERMISSIONTYPEDEF table.
As you are performing an extract of the PERMISSIONS table to form the content of your HOLD200 file without predicates, I would be inclined to perform the join on the base table and (as Francis suggests) trap the SQL being passed to the distant RDBMS and see if it is efficient (if it is a DB2 target RDBMS then get your DBAs to use DB2EXPLAIN on it).
As you are probably aware, I would still prefer to do all the processing using SQL passthru if you can or get your DBAs to build you an efficient view to match your requirements.
For the present I would suggest that the slowness of the JOIN is the fact that you are holding the data and then joining back to your RDBMS. Not advisable.
T