Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Query Optimizations With Dates

From Wiki

Jump to: navigation, search

When querying for dates do not use convert. The following code will cause a table scan

  1. WHERE CONVERT(VARCHAR(8),SomeDate,112) = '20080107'


Let's take a look what we should do. First create this table

  1. CREATE TABLE #temp (SomeDate DATETIME)
  2. INSERT #temp VALUES ('2008-01-08 10:19:40.703')
  3. INSERT #temp VALUES ('2008-01-07 13:19:40.703')
  4. INSERT #temp VALUES ('2008-01-07 12:19:40.703')
  5. INSERT #temp VALUES ('2008-01-07 11:19:40.703')
  6. INSERT #temp VALUES ('2008-01-08 11:19:40.703')
  7. INSERT #temp VALUES ('2008-01-06 15:19:40.703')
  8. INSERT #temp VALUES ('2008-01-06 16:19:40.703')
  9. INSERT #temp VALUES ('2008-01-06 17:19:40.703')
  10. INSERT #temp VALUES ('2008-01-06 18:19:40.703')
  11. INSERT #temp VALUES ('2008-01-06 19:19:40.703')
  12. INSERT #temp VALUES ('2008-01-06 20:19:40.703')
  13. INSERT #temp VALUES ('2008-01-08 21:19:40.703')
  14. INSERT #temp VALUES ('2008-01-07 22:19:40.703')
  15. INSERT #temp VALUES ('2008-01-08 23:19:40.703')


Add an index

  1. CREATE INDEX ix_TempDate ON #temp(SomeDate)


Hit CTRL + K and run these two queries

  1. SELECT *FROM #temp WHERE CONVERT(VARCHAR(8),SomeDate,112) = '20080107'  
  2. SELECT *FROM #temp WHERE SomeDate >= '20080107'AND SomeDate < '20080108'

Look at the plan, see the big difference?


Plan.JPG


If you prefer to see the plan in text you can run the SET SHOWPLAN_TEXT command


  1. SET SHOWPLAN_TEXT ON

An here is the output if you would run the same two queries

  1. |Index Scan(OBJECT:([tempdb].[dbo].[#temp00000000A324].[ix_TempDate]),
  2. WHERE:(Convert([#temp].[SomeDate])='20080107'))


  1. |Index Seek(OBJECT:([tempdb].[dbo].[#temp00000000A324].[ix_TempDate]),
  2. SEEK:([#temp].[SomeDate] >= 'Jan 7 2008 12:00AM'
  3. AND [#temp].[SomeDate] < 'Jan 8 2008 12:00AM') ORDERED FORWARD)

make sure you turn it off again

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

111 Rating: 2.7/5 (104 votes cast)