Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Query Optimizations With Dates
From Wiki
When querying for dates do not use convert. The following code will cause a table scan
- WHERE CONVERT(VARCHAR(8),SomeDate,112) = '20080107'
Let's take a look what we should do. First create this table
- CREATE TABLE #temp (SomeDate DATETIME)
- INSERT #temp VALUES ('2008-01-08 10:19:40.703')
- INSERT #temp VALUES ('2008-01-07 13:19:40.703')
- INSERT #temp VALUES ('2008-01-07 12:19:40.703')
- INSERT #temp VALUES ('2008-01-07 11:19:40.703')
- INSERT #temp VALUES ('2008-01-08 11:19:40.703')
- INSERT #temp VALUES ('2008-01-06 15:19:40.703')
- INSERT #temp VALUES ('2008-01-06 16:19:40.703')
- INSERT #temp VALUES ('2008-01-06 17:19:40.703')
- INSERT #temp VALUES ('2008-01-06 18:19:40.703')
- INSERT #temp VALUES ('2008-01-06 19:19:40.703')
- INSERT #temp VALUES ('2008-01-06 20:19:40.703')
- INSERT #temp VALUES ('2008-01-08 21:19:40.703')
- INSERT #temp VALUES ('2008-01-07 22:19:40.703')
- INSERT #temp VALUES ('2008-01-08 23:19:40.703')
Add an index
- CREATE INDEX ix_TempDate ON #temp(SomeDate)
Hit CTRL + K and run these two queries
- SELECT *FROM #temp WHERE CONVERT(VARCHAR(8),SomeDate,112) = '20080107'
- SELECT *FROM #temp WHERE SomeDate >= '20080107'AND SomeDate < '20080108'
Look at the plan, see the big difference?
If you prefer to see the plan in text you can run the SET SHOWPLAN_TEXT command
- SET SHOWPLAN_TEXT ON
An here is the output if you would run the same two queries
- |Index Scan(OBJECT:([tempdb].[dbo].[#temp00000000A324].[ix_TempDate]),
- WHERE:(CONVERT([#temp].[SomeDate])='20080107'))
- |Index Seek(OBJECT:([tempdb].[dbo].[#temp00000000A324].[ix_TempDate]),
- SEEK:([#temp].[SomeDate] >= 'Jan 7 2008 12:00AM'
- AND [#temp].[SomeDate] < 'Jan 8 2008 12:00AM') ORDERED FORWARD)
make sure you turn it off again
- SET SHOWPLAN_TEXT OFF
So why does this happen? Well when you have a function on a column in the WHERE clause the optimizer can not use an index seek because it does not know how many rows will be affected. It has to do an expensive index scan and scan the whole index instead
Contributed by: --SQLDenis 15:24, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks - 100+ List
Section Query Optimization
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.