October 11, 2016 at 10:02 am
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?
October 11, 2016 at 10:11 am
Quick question, do you have a POC (Partition, Order, Covering) index to satisfy the row_number?
😎
October 11, 2016 at 10:12 am
There's not enough information to give an accurate response, check the following that might help.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 11, 2016 at 12:05 pm
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
October 11, 2016 at 2:43 pm
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
October 11, 2016 at 3:12 pm
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.
October 11, 2016 at 3:32 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply