I have a cross-connection query that queries a very large file (file_a) and an Excel spreadsheet (file_b). The where clause selects just a few rows from file_a. A simple join runs quickly. However, I want to do a left join so that I get all of my selected file_a rows, plus whatever happens to match with file_b. This takes forever to run. Here is the relevant portion of the SQL:
FROM
SFMRT (MSNMEDSCHMIS), UWPROD.UWPROD.DETAIL_PAYROLL_PJN_TRANS file_a
LEFT OUTER JOIN
fy 18 forward summary.AutoRange_fy_18_forward_summary file_b
ON (file_a.EMPLOYEE_ID = file_b.EMPL ID)
WHERE (file_a.SFS_BUSINESS_UNIT = ‘UWMSN’)
AND (file_a.SFS_FISCAL_YEAR = ‘2018’)
AND (file_a.SFS_DEPTID IN (‘530311’, ‘530312’, ‘530313’)
OR file_a.PROJECT_ID = ‘AAB7574’)
AND (file_a.SFS_ACCOUNT LIKE ‘10%’)
Quest tells me that Toad is set up to obtain all rows from file_a, all rows from file_b, and then join the two sets locally. Since file_a is very large, this explains my experience. My work-around is to first run a query on file_a and save it to Excel. Then I join the the file_a results with file_b…
Does anyone out there have a better solution? I would rather not have to export to excel and re-import. I strongly recommend that Quest makes their cross-connection query smarter to handle situations like this. Thanks to everyone for your help.