?
Solved

why ROW_NUMBER() don't use tempdb space

Posted on 2014-08-11
5
Medium Priority
?
194 Views
Last Modified: 2014-08-17
Dear all,

Right now I read this to learn how to improve query:

http://www.sql-server-performance.com/2013/how-to-optimize-sql-server-query-plan/

and I can't see why ROW_NUMBER()  can improve Table Spool operation, any idea ?
0
Comment
Question by:marrowyung
  • 2
  • 2
5 Comments
 
LVL 36

Assisted Solution

by:ste5an
ste5an earned 1000 total points
ID: 40255090
Using the ROW_NUMBER() approach is a different approach. It simply doesn't need a table spool. So "improve" is not necessarily the correct wording. It's a different way with a different plan. And this different plan is better.

btw, in my opinion this is a bad article. It contains some minor problems. Just to name a few: using a table variable instead of a normal table, using only a few datasets. Talking about proability. And mixing up (physical) implementation with the semantic model (primary keys vs (non-)clustered indices).
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40255246
"btw, in my opinion this is a bad article. It contains some minor problems.

I think so and it is trying to sales the dbforgo studio.

so why Row_number() is better in this case ?
0
 
LVL 36

Expert Comment

by:ste5an
ID: 40255317
Cause you'll get a different, simpler execution plan. Which, indeed, often means faster execution (but not necessarily). Without testing it and taking your own measures, it's only theoretical.

So install AdventureWorks and try to apply this kind of queries to an appropriate query. Use SET STATISTICS IO on and compare execution plans to see what happens.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40257448
"So install AdventureWorks and try to apply this kind of queries to an appropriate query. Use SET STATISTICS IO on and compare execution plans to see what happens. "


but from SSMS, include client statistics is ok instead of set statistics IO, right?

just can't see why Row_number() can make the plan simplier, that's what I mean
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1000 total points
ID: 40257471
The effect of using row_number() is that the filtering predicate " rn > 1 " is very simple and importantly it can be evaluated on a "per row" basis
(i.e. without needing to refer to some other list or table).
It is because of this simplicity that the execution plan is "better" : in this case

the alternative example, which uses IN(), requires comparison across 2 sets.
        --Example #1 (of http://www.sql-server-performance.com/2013/how-to-optimize-sql-server-query-plan/)
DELETE FROM @logins
WHERE login_id NOT IN (
       SELECT MAX(login_id)
       FROM @logins
       GROUP BY [user_id]
)

Open in new window

Also as it is dealing with deletions, I believe the optimizer has created a temporary set of data so it can complete the comparisons, and once those have been completed the temporary set can be dropped.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question