ORDER BY Speed - Grant Fritchey

03Jun 2009 by Grant Fritchey 2 Comments

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:

<span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">CREATE </span></span><span style="font-size:x-small;color:#0000ff;">TABLE</span><span style="font-size:x-small;"> dbo</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">IntDate</span><span style="font-size:x-small;color:#808080;">
(</span><span style="font-size:x-small;">IntCol </span><span style="font-size:x-small;color:#0000ff;">INT</span><span style="font-size:x-small;"> </span><span style="font-size:x-small;color:#808080;">NOT</span><span style="font-size:x-small;"> </span><span style="font-size:x-small;color:#808080;">NULL,</span><span style="font-size:x-small;"><span style="font-size:x-small;">
DateCol </span></span><span style="font-size:x-small;color:#0000ff;">DATETIME</span><span style="font-size:x-small;"> </span><span style="font-size:x-small;color:#808080;">NOT</span><span style="font-size:x-small;"> </span><span style="font-size:x-small;color:#808080;">NULL);
</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
CREATE </span></span><span style="font-size:x-small;color:#0000ff;">INDEX</span><span style="font-size:x-small;"> ixInt
</span><span style="font-size:x-small;color:#0000ff;">ON</span><span style="font-size:x-small;"> dbo</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">IntDate</span><span style="font-size:x-small;color:#808080;">(</span><span style="font-size:x-small;">IntCol</span><span style="font-size:x-small;color:#808080;">);</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
CREATE </span></span><span style="font-size:x-small;color:#0000ff;">INDEX</span><span style="font-size:x-small;"> ixDate
</span><span style="font-size:x-small;color:#0000ff;">ON</span><span style="font-size:x-small;"> dbo</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">IntDate</span><span style="font-size:x-small;color:#808080;">(</span><span style="font-size:x-small;">DateCol</span><span style="font-size:x-small;color:#808080;">);</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
SELECT </span></span><span style="font-size:x-small;color:#0000ff;">TOP</span><span style="font-size:x-small;"> 10000 </span><span style="font-size:x-small;color:#0000ff;">IDENTITY</span><span style="font-size:x-small;color:#808080;">(</span><span style="font-size:x-small;"> </span><span style="font-size:x-small;color:#0000ff;">INT</span><span style="font-size:x-small;color:#808080;">,</span><span style="font-size:x-small;">1</span><span style="font-size:x-small;color:#808080;">,</span><span style="font-size:x-small;">1 </span><span style="font-size:x-small;color:#808080;">)</span><span style="font-size:x-small;"> </span><span style="font-size:x-small;color:#0000ff;">AS</span><span style="font-size:x-small;"> n</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
INTO </span></span><span style="font-size:x-small;">#Tally</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
FROM </span></span><span style="font-size:x-small;color:#0000ff;">Master</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">dbo</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;color:#008000;">SysColumns</span><span style="font-size:x-small;"> sc1</span><span style="font-size:x-small;color:#808080;">,
</span><span style="font-size:x-small;color:#0000ff;">Master</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">dbo</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;color:#008000;">SysColumns</span><span style="font-size:x-small;"> sc2</span><span style="font-size:x-small;color:#808080;">;</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
INSERT </span></span><span style="font-size:x-small;color:#0000ff;">INTO</span><span style="font-size:x-small;"> dbo</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">IntDate</span><span style="font-size:x-small;color:#0000ff;"> </span><span style="font-size:x-small;color:#808080;">(</span><span style="font-size:x-small;"><span style="font-size:x-small;">
IntCol
</span></span><span style="font-size:x-small;color:#808080;">,</span><span style="font-size:x-small;"><span style="font-size:x-small;">DateCol</span></span><span style="font-size:x-small;color:#808080;">)</span>
<span style="font-size:x-small;color:#0000ff;">SELECT</span><span style="font-size:x-small;"> t</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">n</span><span style="font-size:x-small;color:#808080;">,
</span><span style="font-size:x-small;color:#ff00ff;">DATEADD</span><span style="font-size:x-small;color:#808080;">(</span><span style="font-size:x-small;">dd</span><span style="font-size:x-small;color:#808080;">,-</span><span style="font-size:x-small;"> t</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">n</span><span style="font-size:x-small;color:#808080;">,</span><span style="font-size:x-small;color:#ff00ff;">GETDATE</span><span style="font-size:x-small;color:#808080;">()</span><span style="font-size:x-small;"> </span><span style="font-size:x-small;color:#808080;">)</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
FROM </span></span><span style="font-size:x-small;">#Tally </span><span style="font-size:x-small;color:#0000ff;">AS</span><span style="font-size:x-small;"> t</span><span style="font-size:x-small;color:#808080;">;</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
DROP </span></span><span style="font-size:x-small;color:#0000ff;">TABLE</span><span style="font-size:x-small;"> #Tally</span><span style="font-size:x-small;color:#808080;">;</span>

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:

<span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">SELECT </span></span><span style="font-size:x-small;">id</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">DateCol </span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
FROM </span></span><span style="font-size:x-small;">dbo</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">IntDate </span><span style="font-size:x-small;color:#0000ff;">AS</span><span style="font-size:x-small;"> id</span><span style="font-size:x-small;color:#0000ff;">
ORDER BY <span style="font-size:x-small;color:#0000ff;">BY</span><span style="font-size:x-small;"> DateCol </span><span style="font-size:x-small;color:#0000ff;">DESC</span>
<span style="font-size:x-small;color:#0000ff;">
SELECT </span></span><span style="font-size:x-small;"> id</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">IntCol</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
FROM </span></span><span style="font-size:x-small;">dbo</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">IntDate </span><span style="font-size:x-small;color:#0000ff;">AS</span><span style="font-size:x-small;"> id</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
ORDER </span></span><span style="font-size:x-small;color:#0000ff;">BY</span><span style="font-size:x-small;"> IntCol </span><span style="font-size:x-small;color:#0000ff;">DESC</span>

SELECT id.DateCol

FROM dbo.IntDate AS id

ORDER BY BY DateCol DESC

SELECT id.IntCol

ORDER BY IntCol DESC

Here are the execution times:

<span style="font-size:xx-small;">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.
</span>

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?

<span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">SELECT </span></span><span style="font-size:x-small;">id</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">DateCol </span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
FROM </span></span><span style="font-size:x-small;">dbo</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">IntDate </span><span style="font-size:x-small;color:#0000ff;">AS</span><span style="font-size:x-small;"> id</span><span style="font-size:x-small;color:#0000ff;">
ORDER <span style="font-size:x-small;color:#0000ff;">BY</span><span style="font-size:x-small;"> DateCol <span style="font-size:x-small;color:#0000ff;">ASC</span><span style="font-size:x-small;"> </span></span>
<span style="font-size:x-small;color:#0000ff;">
SELECT </span></span><span style="font-size:x-small;">id</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">IntCol</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
FROM </span></span><span style="font-size:x-small;">dbo</span><span style="font-size:x-small;color:#808080;">.</span><span style="font-size:x-small;">IntDate </span><span style="font-size:x-small;color:#0000ff;">AS</span><span style="font-size:x-small;"> id</span><span style="font-size:x-small;color:#0000ff;"><span style="font-size:x-small;color:#0000ff;">
ORDER </span></span><span style="font-size:x-small;color:#0000ff;">BY</span><span style="font-size:x-small;"> IntCol </span><span style="font-size:x-small;color:#0000ff;">ASC</span>

ORDER BY DateCol ASC

SELECT id.IntCol

ORDER BY IntCol ASC

Then results are:

<span style="font-size:xx-small;">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.
</span>

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

Larry Leonard

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?

June 30, 2009 at 8:52 pm
scarydba

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.

July 1, 2009 at 6:26 am

Share this:

2 Comments

OK, fine, but what do you think? Cancel reply