Searching Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Searching Dates

Author

Topic

Russell M
Starting Member

2 Posts

Posted - 2005-06-26 : 21:15:39

SQL Server 2000
Searching a DateTime field.

To search by "DAY" and "Hour" only...
Month, year, minute, second makes no difference.

There are 2 ways.
Using Query Analizer and looking at execution plan
This is what I get.

Using QA Estimated QueryPlan.
Query1
WHERE Day(jDate) = 23 AND DATEPART( hh, jDate ) = 18
In QA I get
Select = 22%
Index Scan = 78%

Query2
'Using 4 underscores ____ in place of year
ie:'%23_2004__6%' Without year '%23_______6%'

Where jDate LIKE '%23_______6%'
In QA I get
Select = 18%
index Scan = 82%

This is on 200,000 rows in table
and queries return about 2000

Seems like Query2 returns the rows to QA faster...

How does one time it?

OH
in QA hovering the tableName it shows
Index Scan
object = Select ... from .... where WHERE:( Day(jDate) = 23 AND DATEPART( hh, jDate ) = 18)

Now for Query2 the where changes to a convert...

index Scan
object = Select ... from .... where:(LIKE(Convert(jDate),'%23_______6%',NULL))

Can anyone tell or know for sure what would be faster if searching 1,000,000's of rows in table?

Or any way to accurately time it?

Thanks in advance.
Russell M

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-26 : 22:27:11

quote:
Originally posted by Russell M

How does one time it?

DECLARE T1 DATETIME
SET T1 = GETDATE()
-- exec your query here

PRINT DATEDIFF(ms, T1, GETDATE())

My impression is that converting a DATETIME (jDate) to character format so a LIKE wildcard comparison could be done would be a major loser.

Without further ado, I predict Query 1 will be the winner.

If you're really concerned about performance, add a couple of TinyINT columns to store the Day and Hour of the jDate DATETIME column and index on these columns accordingly... (You could write a trigger to set these columns whenever jDate is modified.)

Then, maybe you could do with a single column that holds day/hour together...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-26 : 22:48:02

Nothing you do is going to be very efficient, because the best you can hope for is an index scan on jDate, if there is an index on that column.

This should be faster than the LIKE, because the datediff and dateadd functions are much faster than conversions to strings. It works by finding the hour of the month for jDate and comparing it to the hour of the month for the month/day combination you are looking for:


where
	datediff(hh,0,dateadd(mm,0-datediff(mm,0,jDate),jDate)) = 
	datediff(hh,0,convert(datetime,'1900-01-23 18:00:00'))

You could also compute these numbers this way:


where
	((Day(jdate)-1)*24)+datepart(hh,jdate) =
	((23-1)*24)+18

As with anyting, you should test to see what actually produces the best results.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources