Optimizing dynamic sql query

• Senthil T

  Hall of Fame

  Points: 3367

  More actions

  October 25, 2010 at 11:53 am

  #74139

  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

• WayneS

  SSC Guru

  Points: 95461

  More actions

  October 25, 2010 at 3:17 pm

  #1241960

  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

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2