Very Slow SubQuery

[newfrontiers]

Hello. I have been trying, unsuccessfully for 2 days now, to improve the performance of the following query:

SELECT C.No, C.BusName, C.SecBusName, C.City, C.State , C.ProspectSt , C.Sales , C.EmpTotal, tblContacts.chrFName, tblContacts.chrLName, tblLastLookup.dteLastLookup, tblLastLookup.chrUserid FROM (C LEFT JOIN tblLastLookup ON C.[No] = tblLastLookup.intNo) LEFT JOIN tblContacts ON C.[No] = tblContacts.[No] WHERE ((C.[No]) IN (SELECT DISTINCT C.[No] FROM C LEFT JOIN tblSIC ON C.[No] = tblSIC.[No] WHERE ((State='NY' AND County IN ('Rensselaer'))) AND (Primsic LIKE '6411*') ) AND ( ((C.BusName) Like 'a*' OR C.SecBusName Like 'a*') AND ((tblContacts.chrLName) Like 'pop*') AND ((C.ProspectSt) Like 'Hot*')))

Is the problem that the subquery and main query use the same table?

Thanks for any assistance as I have a bad headache from banging my head against the wall.

 

[Golom]

Look at the indexes you have defined for the "C" table ... assuming it is a table and not a query.

Each of the fields "C.BusName", "C.SecBusName", "CT.chrLName" and "C.ProspectSt" is being searched and, if they are not indexed, then a full table scan will result.

You might also look into JET Showplan that will help you see how your query is being processed.

 

[gwinn7]

While indexes should definitely be your first step, there is one other item I noticed. Look at your query and see if you can convert the subselect statement into a standard join....

"...((C.[No]) IN (SELECT DISTINCT C.[..."

In my experience, subselect queries can drag query performance. When I had this problem, I simply converted the subselect into a join. Example...

SELECT cola, colb, colc, acol, bcol FROM tableA
WHERE colx IN (SELECT colx FROM tableA WHERE colv like 'A*');

Converted to...

SELECT cola, colb, colc, acol, bcol FROM tableA INNER JOIN TableA as A ON tableA.colx = A.colx WHERE A.colv like 'A*';

The performance on the second will likely be better than the first.

Try that too.

Gary
gwinn7