October 25, 2010 at 11:53 am
Hi,
I have this query where I build the where clause dyncamically based on user criteria selection, and return the total number of matches.
SELECT count(*) FROM tblGeneralInfo T1
INNER JOIN tblCurrentInfo T2 ON T1.ID = T2.ID
AND INNER JOIN tblReport T3 ON T1.ID = T3.ID AND T3.DateLoaded = (SELECT max(DateLoaded) FROM tblReport WHERE ID = T1.ID)
WHERE T1.ID IS NOT NULL
AND T1.Price >= 5
AND T2.Latest >= 5
AND T3.Total <= 50000000
T1 and T2 has few thousand rows. T3 has close to 100K rows, it is causing the query to run very slowly, I guess the date condition is not helping either. Any suggestion on how can I modify this query and improve the speed?
Thanks
October 25, 2010 at 3:17 pm
Senthil T (10/25/2010)
Hi,I have this query where I build the where clause dyncamically based on user criteria selection, and return the total number of matches.
SELECT count(*) FROM tblGeneralInfo T1
INNER JOIN tblCurrentInfo T2 ON T1.ID = T2.ID
AND INNER JOIN tblReport T3 ON T1.ID = T3.ID AND T3.DateLoaded = (SELECT max(DateLoaded) FROM tblReport WHERE ID = T1.ID)
WHERE T1.ID IS NOT NULL
AND T1.Price >= 5
AND T2.Latest >= 5
AND T3.Total <= 50000000
T1 and T2 has few thousand rows. T3 has close to 100K rows, it is causing the query to run very slowly, I guess the date condition is not helping either. Any suggestion on how can I modify this query and improve the speed?
Thanks
Changing the subquery to a derived table ought to help out quite a bit...
SELECT count(*)
FROM tblGeneralInfo T1
INNER JOIN tblCurrentInfo T2 ON T1.ID = T2.ID
INNER JOIN tblReport T3 ON T1.ID = T3.ID
INNER JOIN (SELECT ID, DateLoaded = max(DateLoaded)
FROM tblReport
GROUP BY ID) ds
ON ds.ID = T1.ID
AND T3.DateLoaded = ds.DateLoaded
WHERE T1.ID IS NOT NULL
AND T1.Price >= 5
AND T2.Latest >= 5
AND T3.Total <= 50000000
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply