IBI WebFOCUS - Difference (Preference) between Join and Match:
For most of the reports in our WebFOCUS reporting world, we usually pull data from multiple tables and and generate the report. We all know that we can either Join Data Sources(JOIN) or Merge Data Sources(MATCH) to create report from multiple tables.
Which one to choose from JOIN and MATCH?
Now the question is what is the diffrence between the two and which one to choose from the 2 option. To explain this let's understand how does they process.
JOIN: Basically JOIN is temporary, virtual connection between 2 or more physical or logical sources. Data in the first data source (host file) determines which records are retrieved from cross-reference file. When a record from the host file of a one-to-many join has no corresponding record(s) in the cross-reference file, the record is automatically excluded from the report output.
To control this, you can SET ALL to ON, in which case records from the host file, without corresponding records in the cross-reference file, will be displayed in the report output.
If you JOIN two files: WebFOCUS retrieves a record from the first data source (host file) and then searches for a corresponding record (or records) in the second file (cross-reference file), and stores the results in an Internal Table, which is eventually formatted to produce the report.
Note that only matching records are retrieved from the second file.
MATCH: MATCH allows you to merge information from two or more data sources and capture the results (selected records and requested columns) in a HOLD file. If a report needs to be produced, then it must run as a subsequent request off the HOLD file.
If you MATCH two files: WebFOCUS retrieves the requested records from the first data source and writes the output to a temporary work area. WebFOCUS then retrieves the requested records from the second data source and writes the output to a temporary work area. WebFOCUS then merges the results based on the high-order sort fields and writes the merged results to an Internal Table. The results in the Internal Table are then written to a HOLD file.
If a report is required, you must then run a TABLE request against the resultant HOLD file.
So doing all these steps takes a lot more processing, and potentially Disk IO in case of MATCH. Which leads poor performance as compared to JOIN.
If the required indexes are in place, and if you need an Inner Join or a Left Outer Join, then generally you should pefer using JOIN.
However, MATCH is ideally suited if you need more capabilities than JOIN – such as merging data sources which do not have indexes, or such as merging two unsorted flat files, or such as merging ALL the requested records from the first data source with ALL the requested records from the second data source.
Happy Reporting!!!
Which one to choose from JOIN and MATCH?
Now the question is what is the diffrence between the two and which one to choose from the 2 option. To explain this let's understand how does they process.
JOIN: Basically JOIN is temporary, virtual connection between 2 or more physical or logical sources. Data in the first data source (host file) determines which records are retrieved from cross-reference file. When a record from the host file of a one-to-many join has no corresponding record(s) in the cross-reference file, the record is automatically excluded from the report output.
To control this, you can SET ALL to ON, in which case records from the host file, without corresponding records in the cross-reference file, will be displayed in the report output.
If you JOIN two files: WebFOCUS retrieves a record from the first data source (host file) and then searches for a corresponding record (or records) in the second file (cross-reference file), and stores the results in an Internal Table, which is eventually formatted to produce the report.
Note that only matching records are retrieved from the second file.
MATCH: MATCH allows you to merge information from two or more data sources and capture the results (selected records and requested columns) in a HOLD file. If a report needs to be produced, then it must run as a subsequent request off the HOLD file.
If you MATCH two files: WebFOCUS retrieves the requested records from the first data source and writes the output to a temporary work area. WebFOCUS then retrieves the requested records from the second data source and writes the output to a temporary work area. WebFOCUS then merges the results based on the high-order sort fields and writes the merged results to an Internal Table. The results in the Internal Table are then written to a HOLD file.
If a report is required, you must then run a TABLE request against the resultant HOLD file.
So doing all these steps takes a lot more processing, and potentially Disk IO in case of MATCH. Which leads poor performance as compared to JOIN.
If the required indexes are in place, and if you need an Inner Join or a Left Outer Join, then generally you should pefer using JOIN.
However, MATCH is ideally suited if you need more capabilities than JOIN – such as merging data sources which do not have indexes, or such as merging two unsorted flat files, or such as merging ALL the requested records from the first data source with ALL the requested records from the second data source.
Happy Reporting!!!
Comments
Post a Comment