Hi All,

I was hoping for some help with JOIN performance. I have a simple code structured as follows:

SET ALL=PASS

JOIN TABLE1.KEY IN TABLE1 TO TABLE2.KEY IN TABLE2 AS J1
END

TABLE FILE TABLE1
PRINT TABLE1FIELDS
TABLE2FIELDS
BY KEY
END

I need all the records in TABLE1 regardless of any matching record in TABLE2. TABLE1 and TABLE2 are one-to-one and TABLE1 is Xfocus while TABLE2 is Oracle.

Sounds simple. Except... using SET ALL=PASS makes the fex run for 15 minutes. Taking out SET ALL=PASS makes the fex run in 15 seconds but the parents without children does not print.

I'm running this on version 8.1.05. Has there been some new JOIN features with efficient left outer joins in version 8? What are my other options for a left_outer join without using SET ALL=PASS that is very efficient for Xfocus to Oracle joining? Using JOIN LEFT_OUTER also causes the fex to run for 15 minutes even without SET ALL=PASS.

Thanks for your help.

This message has been edited. Last edited by: FP Mod Chuck,

It's probably better if I go ahead and paste the trace here for the 15 minute response time. As you can see, the query produced is yucky.

 JOIN CLEAR *
 JOIN
 ENGINEERKEY IN TEMP_ENG
 TO MULTIPLE W6ASSIGNMENTS_ENGINEERS.W6ASSIGNMENTS_ENGINEERS.ENGINEERKEY IN W6ASSIGNMENTS_ENGINEERS AS AE1
 -* WHERE  W6ASSIGNMENTS_ENGINEERS.W6ASSIGNMENTS_ENGINEERS.TASK EQ -1;
 END
 JOIN
 W6ASSIGNMENTS_ENGINEERS.W6ASSIGNMENTS_ENGINEERS.W6KEY IN TEMP_ENG
 TO W6ASSIGNMENTS.W6ASSIGNMENTS.W6KEY IN W6ASSIGNMENTS AS A1
 END
 JOIN LEFT_OUTER
 W6ASSIGNMENTS.W6ASSIGNMENTS.CODE700_05 IN TEMP_ENG TO
 W6CODE700_05.W6CODE700_05.W6KEY IN W6CODE700_05 TAG O5NA AS C05
 END
 -* WHERE ORG EQ 'O5';
 -*JOIN LEFT_OUTER
 -* W6ASSIGNMENTS.W6ASSIGNMENTS.CODE700_04 IN TEMP_ENG TO
 -* W6CODE700_04.W6CODE700_04.W6KEY IN W6CODE700_04 TAG O4NA AS C04
 -*-* WHERE ORG NE 'O5';
 -* END
 TABLE FILE TEMP_ENG
 PRINT
 COMPUTE
 STARTDATE/MDYY MISSING ON   = IF STARTTIME  IS MISSING THEN 01012000 ELSE HDATE(STARTTIME,'MDYY');
 FINISHDATE/MDYY MISSING ON  = IF FINISHTIME IS MISSING THEN 12312020 ELSE HDATE(FINISHTIME,'MDYY');
 TASKKEY/I11 MISSING ON = IF TASK IS MISSING THEN 0 ELSE TASK;
 -*NA_CODE/A64V MISSING ON = IF TASK IS MISSING THEN 'Available' ELSE IF ORG EQ 'O5' THEN W6CODE700_05.NAME ELSE W6CODE700_04.NAME;
 NA_CODE/A64V = IF ORG EQ 'O5' THEN W6CODE700_05.NAME;
 -*ELSE W6CODE700_04.NAME;
 CODE700DESC/A64V MISSING ON = IF TASK IS MISSING THEN ' ' ELSE
 IF TASK GT 1 THEN ' ' ELSE
 IF TASK = -1 THEN COMMENTTEXT ELSE ' ';
 -*ENGCOUNT/I6 WITH W6ASSIGNMENTS_ENGINEERS.W6ASSIGNMENTS_ENGINEERS.W6KEY MISSING ON  =
 -*IF W6ASSIGNMENTS_ENGINEERS.W6ASSIGNMENTS_ENGINEERS.W6KEY IS MISSING THEN 0 ELSE
 -*IF W6ASSIGNMENTS_ENGINEERS.W6ASSIGNMENTS_ENGINEERS.W6KEY NE LAST W6ASSIGNMENTS_ENGINEERS.W6ASSIGNMENTS_ENGINEERS.W6KEY
 -*THEN 1 ELSE ENGCOUNT + 1;
 -*ENGCOUNT/I6 = IF ENGINEERKEY EQ LAST ENGINEERKEY THEN ENGCOUNT + 1 ELSE 1;
 BY ENGINEERKEY
 ON TABLE HOLD AS TEMP_ASGN
 -*ON TABLE PCHOLD FORMAT HTML
 END
 -RUN
 02.06.59 BT  (FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
 02.06.59 BT  (FOC2513) OUTER JOIN (ALL=ON or ALL=PASS) CANNOT BE PASSED
 02.06.59 BT  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 02.06.59 BT  (FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED
 02.06.59 AE    SELECT
 02.06.59 AE   T2."W6KEY",
 02.06.59 AE   T2."TASK",
 02.06.59 AE   T2."STARTTIME",
 02.06.59 AE   T2."FINISHTIME"
 02.06.59 AE    FROM
 02.06.59 AE   W6ADMIN.W6ASSIGNMENTS_ENGINEERS T2
 02.06.59 AE    WHERE
 02.06.59 AE   (T2."ENGINEERKEY" = :0001);
 02.06.59 AE    SELECT
 02.06.59 AE   T3."COMMENTTEXT",
 02.06.59 AE   T3."CODE700_05"
 02.06.59 AE    FROM
 02.06.59 AE   W6ADMIN.W6ASSIGNMENTS T3
 02.06.59 AE    WHERE
 02.06.59 AE   (T3."W6KEY" = :0001);
 02.06.59 AE    SELECT
 02.06.59 AE   T4."NAME"
 02.06.59 AE    FROM
 02.06.59 AE   W6ADMIN.W6CODE700_05 T4
 02.06.59 AE    WHERE
 02.06.59 AE   (T4."W6KEY" = :0001);

Try using SET ALL=ON in this case.
ALL=PASS is for reports where you have a selection test. Missing data is considered to pass the selection test.

Hi Anatess,

Are you really still on WF 7.7.03?

Irrespective of that release, I would suggets looking at using Cluster Joins. An example of which can be seen in the WF_RETAIL sample database synonyms.

If you use XRETRIEVAL = OFF with your SQL tracing it will prepare the SQL but not run it. This is a more effective method of debugging requests (not to mention speedier!).

T

Oh, and turn SET ALL = ON off! It's based(?) on older methods of achieving outer joins etc.

Suggestion - only use it when all else fails ... or preferably, never!


T

Many times, when using FOCUS tables with Oracle, I have found MATCH FILE performance (at least time wise) is superior to using JOIN. Have you tried that?

MATCH FILE XFOCUS
....
BY FIELD1
RUN
FILE ORACLE
...
BY FIELD1
AFTER MATCH HOLD OLD
END

How many records in your Oracle table?
Any chance of copying it to a Focus file and then indexing the join field?