why ROW_NUMBER() don't use tempdb space

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 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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
ste5anSenior DeveloperCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.