Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Trick to Optimize TOP clause in SQL Server

MSSQLTips author Matteo Lorini By:   |   Read Comments (6)   |   Related Tips: More > Performance Tuning
Problem

Have you ever come across a situation where a SELECT query with a TOP clause will perform well most of the time, but as soon as you change the TOP value the same query is 10 to 20 times slower?  In this tip I will show you why this may happen and ways to prevent this.

Solution

The TOP clause specifies that only the first set of rows will be returned from the query result. In this tip I will show how to make the query results predictable by avoiding "Sort Warning".

Let's create our sample table.

--Source code provided by: www.sqlworkshops.com
SET NOCOUNT ON
CREATE TABLE
tab7 (c1 INT PRIMARY KEY CLUSTERED, c2 INT, c3 CHAR(2000))
GO
BEGIN TRAN
GO
DECLARE @i INT
SET
@i=1
WHILE @i<=50000
BEGIN
INSERT INTO
tab7 VALUES (@i,RAND()*200000,'a')
SET @i=@i+1
END
COMMIT TRAN
GO

Let's update the statistic with a full scan to make the optimizer work easier.

UPDATE STATISTICS tab7 WITH fullscan
GO

Let's set statistics time on and execute the following query.

SET STATISTICS time ON
GO
--Source code provided by: www.sqlworkshops.com
SELECT num_of_reads, num_of_bytes_read,
num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
SELECT TOP 100 c1, c2,c3
FROM tab7
WHERE c1<30000
ORDER BY c2
GO
SELECT num_of_reads, num_of_bytes_read,
num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
CPU time = 124 ms,  elapsed time = 91 ms
Before Query execution 
num_of_reads         num_of_bytes_read    num_of_writes     num_of_bytes_written
-------------------- -------------------- -------------------- --------------------
725864               46824931328          793589               51814416384
After Query execution  
num_of_reads         num_of_bytes_read    num_of_writes        num_of_bytes_written
-------------------- -------------------- -------------------- --------------------
725864               46824931328          793589               51814416384

The query ran fast (91ms). The number of reads and writes on tempdb before and after the execution of our query are the same. This means that our query was able to complete the sort in memory without spilling to tempdb.


Now, lets execute the following query. Please note the new value in the TOP clause which was changed from 100 to 101.

SELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
SELECT TOP 101 c1, c2,c3
FROM tab7
WHERE c1<30000
ORDER BY c2
GO
SELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
CPU time = 376 ms,  elapsed time = 726 ms
Before Query execution
num_of_reads         num_of_bytes_read    num_of_writes        num_of_bytes_written
-------------------- -------------------- -------------------- --------------------
725864               46824931328          793589               51814416384
After Query execution 
num_of_reads         num_of_bytes_read    num_of_writes        num_of_bytes_written
-------------------- -------------------- -------------------- --------------------
726880               46881570816          795356               51928072192

The query runs much slower (726 ms). The sort operation spilled over to tempdb. The read/write values before and after the execution of our query are different. If you are running SQL 2008 64bit, the work around to make the sort happen in memory is to change the query, so the optimizer can allocate more memory allowing the sort operation to take place in memory as shown below. Please see my previous tip for further explanation.

SELECT TOP 101 c1, c2,CONVERT(VARCHAR(4500),c3)
FROM tab7
WHERE c1<30000
ORDER BY c2

If you are running SQL 2005 TOP (@variable) does the trick.

SELECT num_of_reads, num_of_bytes_read, num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
DECLARE @i INT
SET
@i=101
SELECT TOP(@i) c1, c2,CONVERT(VARCHAR(5000),c3)
FROM tab7
WHERE c1<30000
ORDER BY c2
GO
SELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
CPU time = 267 ms,  elapsed time = 124 ms
Before Query execution 
num_of_reads         num_of_bytes_read    num_of_writes        num_of_bytes_written
-------------------- -------------------- -------------------- --------------------
729075               47004737536          799045               52165402624
After Query execution 
num_of_reads         num_of_bytes_read    num_of_writes        num_of_bytes_written
-------------------- -------------------- -------------------- --------------------
729075               47004737536          799045               52165402624

As we can see, the query is fast again and there is no activity on tempdb.

Next Steps


Last Update: 7/2/2010


About the author
MSSQLTips author Matteo Lorini
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Saturday, June 28, 2014 - 10:04:31 AM - Amar Read The Tip

Sir

my name is amar.I would like to inform you that i have a customer table in this table have  1 cror data.Whenever i am exuting any query on customer table .It's working very slow and also inform you that i am using P.K on customer ID.

So,would you Please give a some solution for this problem and what would be exaclty done when i will excute any query on the same table .

 

Thanx & Regards

Amar

 

 


Tuesday, September 11, 2012 - 2:48:26 AM - Yav Mar Kyn Read The Tip

TOP predicate capped sample records according to a sorting criterion given by the ORDER BY clause, for example:

SELECT TOP 25 City, Country,
Population
FROM Cities
ORDER BY Population
DESC

... I would list the 25 most populated cities in my table Cities

QUESTION: How would the instruction if to me list the five most populated cities in each country?


Thank you.

P.D. Sorry for preview, error tralate from spanish.


Tuesday, July 06, 2010 - 10:51:38 AM - Devashish Read The Tip
Thanks that was very helpful!!

Tuesday, July 06, 2010 - 6:48:58 AM - dmining06 Read The Tip

   Take a look at the following tips http://www.mssqltips.com/tip.asp?tip=1955 and you will find your answers moreover; find time to follow the best at www.sqlworkshops.com. 

 

Thanks


Monday, July 05, 2010 - 11:04:36 PM - Devashish Read The Tip

That was a great tip!!

I am sort of a beginner in the SQL DBA. It would be great if you could also explain why the change from 100 to 101 records is taking so much time to query for the results. Any useful links on that will also be helpful.

Thanks in advance :)

 


Friday, July 02, 2010 - 7:49:38 AM - dmining06 Read The Tip

Please take a look at the valuable webcast offered for free at http://webcasts.sqlworkshops.com.

The author would like to release more webcasts, but before that they want to build good amount of viewership. I have few topics on
the list. Today's TOP tip is only for SQL 2005, this will not reproduce in SQL 2008.

Thanks




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.