how to speed up the query

[Guest]

I have a subquery which is running very slow.
here's my SQL statement:

SELECT s.CCC, s.[91+]
FROM qrytagbyccc AS s
WHERE (((s.[91+]) In (select top 3 t.[91+ ]
from qrytagbyccc as t
where t.ccc = s.ccc
order by t.[91+] desc)))
ORDER BY s.CCC, s.[91+] DESC;
(this query runs very very slow!)

qrytagbyccc SQL statement is this:
SELECT IIf([marketplace] Like "atlanta*" Or
[CSC_CODE]="FLD","ATL",IIf([marketplace] Like "houston*" Or [CSC_CODE]="GLD"
Or [CSC_CODE]="CEN","HOU",IIf([CSC_CODE]="NWD" Or
[CSC_CODE]="WED","Tempe",""))) AS CCC,
CCur(Sum([091-120]+[121-150]+[151-180]+[181-360]+[361+])) AS [91+]
FROM tblTag
GROUP BY tblTag.CSC_CODE, tblTag.Marketplace, tblTag.Collector_Id
HAVING (((tblTag.Collector_Id)<>"0" And (tblTag.Collector_Id)<>"10622"));

any suggestion on how to make it run faster?

 

[John Vinson]

I have a subquery which is running very slow.
here's my SQL statement:

SELECT s.CCC, s.[91+]
FROM qrytagbyccc AS s
WHERE (((s.[91+]) In (select top 3 t.[91+ ]
from qrytagbyccc as t
where t.ccc = s.ccc
order by t.[91+] desc)))
ORDER BY s.CCC, s.[91+] DESC;
(this query runs very very slow!)

qrytagbyccc SQL statement is this:
SELECT IIf([marketplace] Like "atlanta*" Or
[CSC_CODE]="FLD","ATL",IIf([marketplace] Like "houston*" Or [CSC_CODE]="GLD"
Or [CSC_CODE]="CEN","HOU",IIf([CSC_CODE]="NWD" Or
[CSC_CODE]="WED","Tempe",""))) AS CCC,
CCur(Sum([091-120]+[121-150]+[151-180]+[181-360]+[361+])) AS [91+]
FROM tblTag
GROUP BY tblTag.CSC_CODE, tblTag.Marketplace, tblTag.Collector_Id
HAVING (((tblTag.Collector_Id)<>"0" And (tblTag.Collector_Id)<>"10622"));

any suggestion on how to make it run faster?

Well... get rid of the very inefficient IIF for starters; change
HAVING to WHERE (so that the criterion is applied before doing the
summing, rather than summing first and then discarding); and see if
you can come up with a table driven solution to get the value of CCC
so that you don't have to use multiple expressions. The fact that
you're using a complex nested IIF to calculate CCC - and then *sorting
by that expression*, which will be recalculated for each row - is
probably at the root of the problem.

It appears that CCC is not a properly normal field: it depends on the
value of CSC_CODE and also on the value of marketplace. This is risky:
what if (perhaps erroneously) you had a record where CSC_CODE was
equal to WED but Marketplace contained "Houstonville"?

If you exclude the marketplace criterion, you could have a small table
with fields CSC_CODE and CCC, with rows like

FLD ATL
GLD HOU
CEN HOU
NWD Tempe
WED Tempe

etc. and you could simply join this in your expression.

John W. Vinson[MVP]