Slow Performance when using variable in date comparison.

• ccakinberk

  Valued Member

  Points: 55

  More actions

  March 26, 2009 at 8:53 am

  #207638

  I am getting a big performance hit when I say

  DECLARE @FinalDay DATETIME

  SET @FinalDay= DATEADD(dd,-1*200,GETDATE())

  WHEREOrderDate > @FinalDay (takes 2 secs)

  but this performs much faster without using the variable.

  WHERE OrderDate > DATEADD(dd,-1*200,GETDATE()) (takes less than a second)

  is there anyway I can utilize the first query with better performance since @FinalDate is a varable that will need to change.

  Thank You

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  March 26, 2009 at 9:02 am

  #966748

  This looks like a case of parameter sniffing..

  have a look at this article on how this may affect the query performance and how to fix it

  http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

• ccakinberk

  Valued Member

  Points: 55

  More actions

  March 26, 2009 at 9:21 am

  #966771

  Thank You.

  I was able to fix it by adding OPTION(RECOMPILE);