Query performance hard coded value vs. table driven

• occasional sql

  SSC Veteran

  Points: 237

  More actions

  November 6, 2014 at 4:13 pm

  #291787

  I want to be able to return the rows from a table that have been updated since a specific time. My query returns results in less than 1 minute if I hard code the reference timestamp, but it keeps spinning if I load the reference timestamp in a table. See examples below (the "Reference" table has only one row with a value 2014-09-30 00:00:00.000)

  select * from A where ReceiptTS > '2014-09-30 00:00:00.000'

  select * from A where ReceiptTS > (select ReferenceTS from Reference)

  Many thanks!

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  November 6, 2014 at 9:55 pm

  #1757119

  occasional sql (11/6/2014)

  ---

  I want to be able to return the rows from a table that have been updated since a specific time. My query returns results in less than 1 minute if I hard code the reference timestamp, but it keeps spinning if I load the reference timestamp in a table. See examples below (the "Reference" table has only one row with a value 2014-09-30 00:00:00.000)

  select * from A where ReceiptTS > '2014-09-30 00:00:00.000'

  select * from A where ReceiptTS > (select ReferenceTS from Reference)

  Many thanks!

  Quick thought, use a variable instead of the subquery

  

  DECLARE @ReceiptTS DATETIME;

  SELECT @ReceiptTS = ReferenceTS from Reference;

  select * from A where ReceiptTS > @ReceiptTS;

• Koen Verbeeck

  SSC Guru

  Points: 259155

  More actions

  Follow

  November 7, 2014 at 12:34 am

  #1757133

  Eirikur Eiriksson (11/6/2014)

  ---

  occasional sql (11/6/2014)

  ---

  I want to be able to return the rows from a table that have been updated since a specific time. My query returns results in less than 1 minute if I hard code the reference timestamp, but it keeps spinning if I load the reference timestamp in a table. See examples below (the "Reference" table has only one row with a value 2014-09-30 00:00:00.000)

  select * from A where ReceiptTS > '2014-09-30 00:00:00.000'

  select * from A where ReceiptTS > (select ReferenceTS from Reference)

  Many thanks!

  Quick thought, use a variable instead of the subquery

  

  DECLARE @ReceiptTS DATETIME;

  SELECT @ReceiptTS = ReferenceTS from Reference;

  select * from A where ReceiptTS > @ReceiptTS;

  +1

  Hard to say without execution plan, but it is possible the subquery got executed multiple times instead of just once.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  November 7, 2014 at 1:34 am

  #1757140

  Noted JOIN often is dramatically faster

  select A.*

  from A

  left join Reference r on A.ReceiptTS > r.ReferenceTS -- single row table

• Koen Verbeeck

  SSC Guru

  Points: 259155

  More actions

  Follow

  November 7, 2014 at 1:42 am

  #1757142

  serg-52 (11/7/2014)

  ---

  Noted JOIN often is dramatically faster

  select A.*

  from A

  left join Reference r on A.ReceiptTS > r.ReferenceTS -- single row table

  Hmmm. Can this query lead to an index seek on table A?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  November 10, 2014 at 4:08 pm

  #1757581

  For this query:

  select * from A where ReceiptTS > '2014-09-30 00:00:00.000'

  SQL will use the actual date value to estimate the number of rows returned that will be returned.

  For this query:

  select * from A where ReceiptTS > (select ReferenceTS from Reference)

  SQL will have to use a default estimate of the number of rows that will be returned, typically 30%. If the real % is much higher or lower than that, you could see performance issues.

  The underlying problem is most likely that table "A" needs clustered on ReceiptTS instead of an identity column; if so, that would solve your performance issues without having to constantly rewrite all your queries.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".