Performance issue when testing the value of ROW_NUMBER in a WHERE clause

Question

Post reply

Performance issue when testing the value of ROW_NUMBER in a WHERE clause

cmartel 20772

Ten Centuries

Points: 1161
More actions
October 11, 2016 at 10:02 am

#400857

I have a (moderately complex) statement that performs pretty well. Adding a ROW_NUMBER() column to it does not significantly change its execution time. For example,
SELECT * FROM ...
and
SELECT *, ROW_NUMBER() OVER (PARTITION BY ...) AS RowNum FROM ...
execute in about 1 second.
However, executing
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ...) AS RowNum FROM ...
) X WHERE RowNum = 1
Takes about 6 seconds to execute where I strongly feel it should take the same time to execute (or even less time since we are outputting less records and testing an integer is a quick process).
Interestingly, inserting in a temporary table and selecting from this table, like
DECLARE @T TABLE (...)
INSERT INTO @T SELECT *, ROW_NUMBER() OVER (PARTITION BY ...) AS RowNum FROM ...
SELECT * FROM @T WHERE RowNum = 1
only takes two seconds to execute but this is something I would prefer to avoid.
I tried with simple statements and I was not able to notice any performance degradation. Any clue on what is happening?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Quick question, do you have a POC (Partition, Order, Covering) index to satisfy the row_number?

😎

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

There's not enough information to give an accurate response, check the following that might help.

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

Check the execution plans for each query to see how they're being resolved.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Grant Fritchey (10/11/2016)
Check the execution plans for each query to see how they're being resolved.

Be sure to compare estimated and actual row counts in both plans.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

cmartel 20772 Ten Centuries Points: 1161 More actions · Answer 5

The plans are quite similar.

The more I am analyzing this, the more it appears to be a SQL Manager Studio issue. I just discovered that if, within Manager Studio, I create a local table and I simply insert all records into the table, without displaying anything, then testing RowCount in the WHERE clause makes no difference in execution speed.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

cmartel 20772 (10/11/2016)
The plans are quite similar.
The more I am analyzing this, the more it appears to be a SQL Manager Studio issue. I just discovered that if, within Manager Studio, I create a local table and I simply insert all records into the table, without displaying anything, then testing RowCount in the WHERE clause makes no difference in execution speed.

So are you returning a lot of records to SSMS? if so then clearly that can cause a performance issue.

I find that if I want to test server performance only I will create variables (of the proper data type) for all output columns and simply put each column into the proper variable in my SELECT.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service