Long Running SQL Query .. Looking for recommendations for improvement.

• jacobmellado

  Grasshopper

  Points: 14

  More actions

  March 26, 2014 at 6:21 am

  #1700251

  Replace in the query:

  WHERE (h.timestamp BETWEEN GETDATE() - 14 AND GETDATE())

  The planner does a table scan with that "Where".

  by

  --> DateIni = GETDATE() - 14

  WHERE (h.timestamp BETWEEN DateIni AND GETDATE())

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 26, 2014 at 6:29 am

  #1700253

  jacobmellado (3/26/2014)

  ---

  Replace in the query:

  WHERE (h.timestamp BETWEEN GETDATE() - 14 AND GETDATE())

  The planner does a table scan with that "Where".

  There's nothing in that predicate which will force a scan. Assigning the results of the date manipulation to a variable first will just remove the ability of the optimiser to sniff the values, will probably make the row estimation less accurate.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass