November 6, 2014 at 4:13 pm
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!
November 6, 2014 at 9:55 pm
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;
November 7, 2014 at 12:34 am
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
November 7, 2014 at 1:34 am
Noted JOIN often is dramatically faster
select A.*
from A
left join Reference r on A.ReceiptTS > r.ReferenceTS -- single row table
November 7, 2014 at 1:42 am
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
November 10, 2014 at 4:08 pm
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".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply