August 16, 2017 at 5:53 am
Interestingly there comes a point where the original query using ROW_NUMBER() becomes the fastest. This is when there are not many QueuedAt values for ObjectType/ObjectId combinations. In my test above the original query performed better with this data in the Test1 table:insert into dbo.Test1 (ObjectType,ObjectId,QueuedAt,myText)
select top(20000000) t1.n,t2.n,t3.n,newid()
from dbo.tally t1
inner join dbo.tally t2
on t2.n between 1 and 100
inner join dbo.tally t3
on t3.n between 1 and 2
where t1.n between 1 and 1200000
order by 1,2,3
August 16, 2017 at 7:31 am
The sample data is quite a bit different to the actual data, it will be interesting to see how that pans out. It's so easy to switch between these two query forms though, that I'll almost always test both and examine the plans before committing to one or the other.
Thanks for posting up the sample script Jonathan, that was fun to play with for a while.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2017 at 10:23 am
ChrisM@Work - Wednesday, August 16, 2017 7:31 AMThe sample data is quite a bit different to the actual data, it will be interesting to see how that pans out. It's so easy to switch between these two query forms though, that I'll almost always test both and examine the plans before committing to one or the other.
Thanks for posting up the sample script Jonathan, that was fun to play with for a while.
Yes, with t-sql it's always horses for courses.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply