 |
|||||
|
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. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| slow join | Login/Join |
| Platinum Member |
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 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
| ||
|
| Platinum Member |
Pam, Perhaps the following link on the Technical Support site might help: Slow Join You 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 ------------------------------------------------------------------------ PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode | |||
|
| Expert |
When 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 | |||
|
| Expert |
Pam, 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
| |||||||||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |