Trick to Optimize TOP clause in SQL Servere

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.comSET NOCOUNT ON
CREATE TABLE tab7 (c1 INT PRIMARY KEY CLUSTERED, c2 INT, c3 CHAR(2000))GOBEGIN TRANGODECLARE @i INT
SET @i=1WHILE @i<=50000BEGIN
INSERT INTO tab7 VALUES (@i,RAND()*200000,'a')SET @i=@i+1END
COMMIT TRANGO

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 ONGO--Source code provided by: www.sqlworkshops.comSELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_writtenFROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1) GOSELECT TOP 100 c1, c2,c3FROM tab7WHERE c1<30000ORDER BY c2
GOSELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_writtenFROM 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_writtenFROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1) GOSELECT TOP 101 c1, c2,c3FROM tab7WHERE c1<30000ORDER BY c2
GOSELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_writtenFROM 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 tab7WHERE c1<30000ORDER 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_writtenFROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)GODECLARE @i INT
SET @i=101SELECT TOP(@i) c1, c2,CONVERT(VARCHAR(5000),c3)FROM tab7WHERE c1<30000ORDER BY c2
GOSELECT 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.