ORDER BY Speed

June 3, 2009 at 10:26 am (SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , )


I answered a question on SSC with the comment that while an INT would perform better than a DATETIME in an ORDER BY query, assuming each has a viable index, that the difference wouldn’t be all that terribly substantial. Then I realized, maybe that’s not true. So I ran up a quick test, just to see.

First I created a little test table with the right indexes and loaded it with data:

CREATE TABLE dbo.IntDate
(IntCol INT NOT NULL,
DateCol DATETIME NOT NULL);

CREATE INDEX ixInt
ON dbo.IntDate(IntCol);
CREATE INDEX ixDate
ON dbo.IntDate(DateCol);

SELECT TOP 10000 IDENTITY( INT,1,1 ) AS n
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2;

INSERT INTO dbo.IntDate (
IntCol
,DateCol)
SELECT t.n,
DATEADD(dd,- t.n,GETDATE() )
FROM #Tally AS t;

DROP TABLE #Tally;

Then I ran these two queries. Each one correctly accessed the index, doing a scan, to retrieve the data:

SELECT id.DateCol 
FROM dbo.IntDate AS id
ORDER BY BY DateCol DESC

SELECT  id.IntCol
FROM dbo.IntDate AS id
ORDER BY IntCol DESC

Here are the execution times:

Table 'IntDate'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 70 ms.
(10000 row(s) affected)
Table 'IntDate'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 59 ms.

But what happens if I reverse the order?

SELECT id.DateCol 
FROM dbo.IntDate AS id
ORDER BY DateCol ASC 

SELECT id.IntCol
FROM dbo.IntDate AS id
ORDER BY IntCol ASC

Then results are:

Table 'IntDate'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 52 ms.
(10000 row(s) affected)
Table 'IntDate'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 5 ms.

So, there can be a somewhat significant difference, although at least part of that is explained by the differences in the number of reads. Not exactly earth shattering, but good to know.

2 Comments

  1. Larry Leonard said,

    I don’t get what elapsed time has to do with anything. Isn’t that just the vagaries of your hard drive, index fragmentation, phase of the moon, etc.? I always thought elapsed time was immaterial, and that what mattered was reads, writes, and cpu time. Both your examples show 21 and 26 reads – no difference. Am I missing something?

  2. scarydba said,

    Elapsed time, especially on a system with no load, running tests under controlled conditions, is a great measure for performance, but you are right. It shouldn’t be taken alone. The I/O, reads in this case, and CPU are also important. The principal difference here was primarily because of the I/O and that was because there were fewer pages for the index on the integer value.

    The difference here was, to a degree, negligible, but that’s what I set out to prove. Was there a major difference between the two data types? No. But there was a difference. On the scale of this test, that difference means very little, but on a larger scale, with millions more rows and contention, sun spots and phase of the moon added in, this could make a huge difference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: