ORDER BY Speed

Home / SQL Server 2008 / ORDER BY Speed

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:

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

Here are the execution times:

But what happens if I reverse the order?

Then results are:

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?

  • 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.

OK, fine, but what do you think?