SQL Server Central Track at Connections

June 3, 2009 at 6:03 pm (Misc, SQLServerPedia Syndication) (, , )

This year at SQL Connections, there will be a new track, the SQL Server Central track (scroll down). I’ve been honored to be selected to present two different sessions on that track, MUQt or More Unecessary Query tuning (pronounced MUCK) and Scouting Out Execution Plans. I’m on a list with a bunch of speakers that… well, WOW is all I can say. I almost wish I wasn’t presenting (almost) so I can just attend their sessions & learn stuff. Anyway, for those that won’t or can’t make the PASS Summit in Seattle, I hope I catch up with you in Las Vegas (where I’m told that anything that happens will remain in place, or something, is it a transaction rollback do you think?).

Permalink 7 Comments

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.

Permalink 2 Comments