I have 3 massive (> 4M rows) tables that I need to selectively join. I can select on key fields, and I'll only extract a few hundred records once I narrow it down, but I would like to see an example of the best way to do this.

I looked at WebFocus "Joining Data Sources" and "Merging Data Sources", but couldn't find a good example. Should I use a 3 way join and let SQL do the work? Should I join the first 2 tables and then join the hold file to the third file?

Sample code to do either (or both) of these approaches would be appreciated.

Thank you.

Re multiple joins;

I do them all the time. In fact the fewer passes between WebFOCUS app server and your database the better.

Here is an example program. Note as 'no short path' this is a vertical join structure. The way we use WebFOCUS is not fancied up yet. Our MRE users run this as a standard report to get address information on their enrolled students.

The pull of initial data is on the term and college which reduces the result set, then the report selection on where total on the app server gives one record as we have multiple address segments potentially.

JOIN
RTPGTBL_UNO_PROD.RT005_RTPG AND RTPGTBL_UNO_PROD.RT010_RTPG
IN RTPGTBL_UNO_PROD TO ALL RTRTTBL_UNO_PROD.RT005_RTRT AND
RTRTTBL_UNO_PROD.RT010_RTRT
IN RTRTTBL_UNO_PROD AS J1
END
JOIN
RTRTTBL_UNO_PROD.RT010_RTRT
IN RTPGTBL_UNO_PROD TO ALL AARTTBL_UNO_PROD.AA002_AART
IN AARTTBL_UNO_PROD AS J3
END
JOIN
AARTTBL_UNO_PROD.AA002_AART
IN RTPGTBL_UNO_PROD TO ALL ADADTBL_UNO_PROD.AD002_ADAD
IN ADADTBL_UNO_PROD AS J5
END
DEFINE FILE RTPGTBL_UNO_PROD
SIDFORM/A11= EDIT ( RT010_RTPG , '999-99-9999' );
ADRS/I1= IF AD215 EQ 'L' OR AD220 EQ 'L' OR AD225 EQ 'L' OR AD230 EQ 'L' OR
AD235 EQ 'L'
THEN 1 ELSE IF AD215 EQ 'P' OR AD220 EQ 'P' OR AD225 EQ 'P' OR AD230 EQ 'P' OR
AD235 EQ 'P' THEN 2 ELSE 3;
TMP_NAME/A32= GETTOK ( STU_NAME , 32 , 2 , ',' , 32 , TMP_NAME );
SFX_NAME/A5= GETTOK ( STU_NAME , 32 , 3 , ',' , 5 , SFX_NAME );
FMN_NAME/A32= LJUST ( 32 , TMP_NAME , FMN_NAME );
FST_NAME/A32= GETTOK ( FMN_NAME , 32 , 1 , ' ' , 32 , FST_NAME );
MDL_NAME/A32= GETTOK ( FMN_NAME , 32 , 2 , ' ' , 32 , MDL_NAME );
LST_NAME/A32= GETTOK ( STU_NAME , 32 , 1 , ',' , 32 , LST_NAME );
EXPNDNME/A72= FMN_NAME || ( ' ' | LST_NAME | ' ' ) || SFX_NAME || '*' ;
CMPRSNME/A32= GETTOK ( EXPNDNME , 66 , 1 , '*' , 32 , CMPRSNME );
FUL_NAME/A32= LJUST ( 32 , CMPRSNME , FUL_NAME );
END
TABLE FILE RTPGTBL_UNO_PROD
HEADING
"University of Nebraska at Omaha"
"Student Name and Address List"
"Selects on Local then Permanent then Other"
"for "&DATE"
SUM FST_NAME AS 'First Name' MDL_NAME AS 'Middle,Name/Init' LST_NAME AS
'Last Name' AD240 AD245 AD250 AD255 AD260 COMPUTE WANTED/I5= IF STU_ID EQ LAST
STU_ID THEN 0 ELSE 1; NOPRINT
BY RT105_RTPG NOPRINT BY RT005_RTPG NOPRINT BY AA003 NOPRINT BY RT010_RTPG
NOPRINT BY ADRS NOPRINT
WHERE RT005_RTPG EQ '&TERM'
WHERE RT105_RTPG EQ '&COLLEGE'
WHERE TOTAL WANTED EQ 1
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END

If you have 3 large tables (millions of rows), and you want to query them selectively (effectively using indexes, hints, optimizations etc.), and each of the queries results in a relatively small number of rows (hundreds) I recommend you to use 3 x PASSTHROUGH and then join, match, and transform the HOLDed results as needed. The final transformations should not affect the overall performance of the report in the significant way, so the order of final JOINs, and the method to use for joining the HOLDed files is not very important from the performance point of view.
You can use 3 x TABLE request if you are sure that generated SQL is optimal (in many cases TABLE request are very effectively translated to SQL).
I would rather discourage you from JOINing large tables in FOCUS and trying to perform the report in one query. Divide it.


SQL SQLXYZ
Optimized Query 1;
TABLE
ON TABLE HOLD AS HLD1 FORMAT FOCUS INDEX fields
END
-*
SQL SQLXYZ
Optimized Query 2;
TABLE
ON TABLE HOLD AS HLD2 FORMAT FOCUS INDEX fields
END
-*
SQL SQLXYZ
Optimized Query 3;
TABLE
ON TABLE HOLD AS HLD2 FORMAT FOCUS INDEX fields
END
-*
-* Now we have just the 3 small FOCUS tables,
-* so they can be quickly transformed any way we want.
-* ...


Hope this helps
Grzegorz

Okay, I tried variations of both suggestions, but still don't have the results that I want. The first approach doesn't seem to work because joining a 4 million row table to another 4 million row table takes too long.

The second approach seems more likely, but I need to use the results of the first extract as selection criteria in the second and third SQL's. Is this doable?

How may rows does the first resultset contain. You may want to see about using WHERE field IN DDFILE.

I use Oracle so there is some specific oracle syntax here but for the most part you could do this...



SQL SQLORA SET SERVER MYSERVER
SQL SQLORA

SELECT * FROM
TABLEA A , TABLEB B, TABLEC C
WHERE A.SELECTED = &ASELECT
AND B.SELECTED = &BSELECT
AND C.SELECTED = &CSELECT
AND A.KEY = B.KEY
AND B.KEY = C.KEY
TABLE
ON TABLE HOLD AS HOLD1
END

TABLE FILE HOLD1
PRINT
WHATEVER
BY
WHATEVER ELSE
END

You can use amper variables in your sql passthrough to narrow the selections. By default you will be prompted for those values or you can use RLP to make the page look pretty.

If any of this is unclear just email me and I'll see if we can make it more specific to your table structure.

This message has been edited. Last edited by: <Mabel>,

JOIN KEY IN FIELD1 TO {ALL} KEY IN FILE2 AS J1
JOIN KEY2 IN FIELD1 TO {ALL} KEY2 IN FILE3 AS J2
now.. its not quite that simple.
Are your files 1-to-1 joins? or 1-to-many?
The {ALL} is for a 1-to-many join.
Now, are your source files Focus databases or something else, line DB2 relationals.?(granularit matching, being the issue).
When you do the first join, your resulatant entity is the combination of the fist two
So, your second join can actually use a key field that is in FILE2, even tho you reference FILE1 as your host...'cause FILE1 is now a wider virtual entity. OK so far?
These joins will give you the intersection of the three files. Tell us a little more about your files. There are also some prejoin conditions you may need to set, depending on your seletion criteria: SET ALL= ON vs SET ALL = PASS.
--
IF you decide not to join the raw files, but rather to make smaller extracts instead (as Gregorz suggests), i would suggest trying your interim HOLD files as just .ftm, and not bother making them into focus files, only because the HOLDing as FOCUS is a 2-step process, takes a bit more time. The HOLDing dumps a flat file and then MODIFYs into a focus file, all under the covers.
You could build your key fields in your extracts and then joining your resultant flat files, as above. Worth a try, if your files are monster large, it might save you significant time.