Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ROW_NUMBER(): An Efficient Alternative to Subqueries


ROW_NUMBER(): An Efficient Alternative to Subqueries

Author
Message
Francis Rodrigues-459442
Francis Rodrigues-459442
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 168
There are not any issues to my knowledge that require the NOLOCK hint for ROW_NUMBER(). I just used it for performance reasons.
Francis Rodrigues-459442
Francis Rodrigues-459442
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 168
JJ B (5/12/2009)
Well done article.

Question: I hate to open a can of worms on a different topic, but I'm so curious as to why you specified the nolock hint for the row_number version of the query. Do you know of specific problems that require use of nolock for this type of implementation?

Thanks.


There aren't any problems with the ROW_NUMBER() version of the query that require the NOLOCK hint. I just used it for the slight performance improvement.
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
Francis Rodrigues (5/12/2009)
There are not any issues to my knowledge that require the NOLOCK hint for ROW_NUMBER(). I just used it for performance reasons.

Thanks for the clarification. I guess that's what I was trying to get at. Do you mean that ROW_NUMBER() has such bad performance that you need to resort to using NOLOCK? Or is using NOLOCK just a common practice for your queries to try to preempt performance problems?

Thanks.
wbrianwhite
wbrianwhite
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 152
In what way was simple set logic unable to perform this operation? It looks like this could be solved with left outer joins and is null tests. I don't have the test db mentioned, so my > may be wrong, it could need to be a <, I can never remember without testing it. But something like this should do the trick


SELECT p.ProductID, p.Version, p.MinorVersion, p.ReleaseVersion, p.StandardCost
from Production.ProductVersion p
left join Production.ProductVersion exclude1
on p.ProductID = exclude1.ProductID
and p.Version > exclude1.Version
left join Production.ProductVersion exclude2
on p.ProductID = exclude2.ProductID
and p.Version = exclude2.Version
and p.MinorVersion > exclude2.MinorVersion
left join Production.ProductVersion exclude3
on p.ProductID = exclude3.ProductID
and p.Version = exclude3.Version
and p.MinorVersion = exclude3.MinorVersion
and p.ReleaseVersion > exclude3.ReleaseVersion
left join Production.ProductVersion exclude4
on p.ProductID = exclude4.ProductID
and p.Version = exclude4.Version
and p.MinorVersion = exclude4.MinorVersion
and p.ReleaseVersion = exclude4.ReleaseVersion
and p.StandardCost > exclude4.StandardCost
where exclude1.Version is null
and exclude2.MinorVersion is null
and exclude3.ReleaseVersion is null
and exclude4.StandardCost is null


William Hutton-462896
William Hutton-462896
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 83
wbrianwhite (5/12/2009)
In what way was simple set logic unable to perform this operation? It looks like this could be solved with left outer joins and is null tests. I don't have the test db mentioned, so my > may be wrong, it could need to be a <, I can never remember without testing it. But something like this should do the trick


I think it's just a matter of ...elegance? To me using Row_Number() statements (and CTEs in general) are easier to write and easier to follow in specific situations (provided the one reading knows what they do). And I can tell you just from testing this morning that the Row_Number() query has a performance improvement over the left joins and isnull tests I had been using previously.
Ryan C. Price
Ryan C. Price
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 1173
I think this was a good demonstration of where these 2005+ features can be useful. I have a job coming up where this could really help.

IMO, none of the few alternative queries posted so far are as clear in their intent, which I think is almost as important as performance, and an acceptable trade-off where performance isn't dramatically affected.

Slightly off-topic, but I couldn't help being reminded of someone's suggestion I saw recently of a 'FIRST' aggregate (MS Access has one), which of course would make the query really simple, and probably perform really well. I suspect there must be someone who'd done one using CLR:
SELECT ProductID,
FIRST(Version) AS Version,
FIRST(MinorVersion) AS MinorVersion,
FIRST(ReleaseVersion) AS ReleaseVersion,
FIRST(StandardCost) AS StandardCost
FROM Production.ProductVersion
ORDER BY ProductID, Version DESC, MinorVersion DESC, ReleaseVersion DESC
GROUP BY ProductID
chill-1062987
chill-1062987
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 44
Thanks for a good article, I definitely learned what Row_Number() is - definitely had no idea before.
cantor
cantor
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 443
I have seen similar examples of ROW_NUMBER() usage and the following has always puzzled me:

Why do we include columns in the ORDER BY when they are already in the PARTITION BY ?

i.e. I would expect

SELECT ROW_NUMBER() OVER(PARTITION BY ProductID,
Version ORDER BY ProductID, Version, MinorVersion DESC, ReleaseVersion DESC) AS MaxVersion

to be equivalent to

SELECT ROW_NUMBER() OVER(PARTITION BY ProductID,
Version ORDER BY MinorVersion DESC, ReleaseVersion DESC) AS MaxVersion

I know I must be missing obvious with this but I'm hanged if I can figure out what it is... :-)
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
UncleJimBob (5/12/2009)
I have seen similar examples of ROW_NUMBER() usage and the following has always puzzled me:

Why do we include columns in the ORDER BY when they are already in the PARTITION BY ?

i.e. I would expect

SELECT ROW_NUMBER() OVER(PARTITION BY ProductID,
Version ORDER BY ProductID, Version, MinorVersion DESC, ReleaseVersion DESC) AS MaxVersion

to be equivalent to

SELECT ROW_NUMBER() OVER(PARTITION BY ProductID,
Version ORDER BY MinorVersion DESC, ReleaseVersion DESC) AS MaxVersion

I know I must be missing obvious with this but I'm hanged if I can figure out what it is... :-)

AFAIK, you are correct. There's no reason to include a column in the ORDER BY if it's already in the PARTITION BY, it has no effect.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
wbrianwhite
wbrianwhite
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 152
William Hutton (5/12/2009)

I think it's just a matter of ...elegance? To me using Row_Number() statements (and CTEs in general) are easier to write and easier to follow in specific situations (provided the one reading knows what they do). And I can tell you just from testing this morning that the Row_Number() query has a performance improvement over the left joins and isnull tests I had been using previously.


Do they? How big is it? Is that before or after you make a clustered index on the four significant columns? In figure 4 it looks like a well-indexed table is faster when queried by the sub-query approach. And since this table is going to be used in lots and lots of joins besides the one in the article, it needs to be well-indexed anyway.

The comparison of query plans in the article is skewed because the 14 nested subqueries can be replaced by a single select with 4 left outer joins, and it is that query plan comparison I'd be interested in seeing.

Also, isn't that subquery getting the max release version and standard cost as well, while the Row_Number() version is not?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search