How to Speed up a Left Join?

[DataMiner]

Hi,
I am doing a Left Join to try to look up values in a large (about 100,000 records) table. If the value isn't found, I'm using the nz function to supply a value.

This query runs very slowly (takes about 2 minutes). I can understand why... I suppose that for every value it's trying to look up , it has to loop through all 100,000 records before it decides that it's not there.

So, I am just looking for ideas on how to make this run faster.

I do have indexes on all my join fields and criteria fields.
Thanks for any suggestions.

Here's the SQL:

SELECT VM1a.row, VM1a.Column, VM1a.Noun, VM1a.Rev, VM1a.RefDes, VM1a.repcode, VM1a.Cell, VM1a.InspPoint, VM1a.DefectType, CLng(nz([FirstOfTruncatedOpSeq],0)) AS ZOpSeq, Sum(VM1a.DefectQty) AS SumOfDefectQty
FROM VM1a LEFT JOIN BOMOutRefDesOnly ON (VM1a.RefDes = BOMOutRefDesOnly.RefDesOnly) AND (VM1a.BOM1 = BOMOutRefDesOnly.PCAItemNo)
GROUP BY VM1a.row, VM1a.Column, VM1a.Noun, VM1a.Rev, VM1a.RefDes, VM1a.repcode, VM1a.Cell, VM1a.InspPoint, VM1a.DefectType, CLng(nz([FirstOfTruncatedOpSeq],0));

 

[Jon K]

Grouping by 10 columns does take time, particularly when one of the columns is an expression: CLng(nz([FirstOfTruncatedOpSeq],0))


What is the data type of [FirstOfTruncatedOpSeq]? If it is integer or long integer, you can use a numeric expression and get rid of the CLng() function:-

nz([FirstOfTruncatedOpSeq])+0


Is BOMOutRefDesOnly a query that involves grouping and calculations? If it is, you can try changing BOMOutRefDesOnly into a make table query to create a temporary table first. Querying from a temporary table is much faster than querying from a query that involves grouping and calculations.

Hope this helps.
.