SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Searching Dates		  Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Russell M
Starting Member

2 Posts
Posted - 06/26/2005 :  21:15:39  Show Profile
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

USA
3459 Posts
Posted - 06/26/2005 :  22:27:11  Show Profile
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...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 06/26/2005 :  22:48:02  Show Profile
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
Edited by - Michael Valentine Jones on 06/27/2005 07:30:34
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000