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.
| Author |
Topic |
|
TSQLMan
Posting Yak Master
160 Posts |
 Posted - 2003-09-05 : 10:16:07
|
| Look at the join on the following query. I have a table, that returns the date section where the time is defaulted to 12:00AM and teh table I am joining has the date and time. I need to join them where only the date datepart mmddyy are the same. Is there a more efficient way, than the following. Thanks, select a.loaded, g.loadstart,g.loadend, a.barge, g.bargedatestring, a.ash_ar as SGS_ASHAR, g.ashar as CTE_ASHAR from analysisimport.dbo.analysis_history a inner join _SP_gammsum g on g.bargedatestring = a.barge anddatepart(mm,g.loadstart)= datepart(mm, a.loaded) and datepart(dd,g.loadstart)= datepart(dd, a.loaded) anddatepart(yy,g.loadstart)= datepart(yy, a.loaded) |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-09-05 : 10:25:08
|
| Yes. Join on datediff( dd, date1, date2 ) = 0.Jonathan{0} |
 |
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2003-09-05 : 11:23:42
|
| Shaved 9 second off of a 100,000 row draw. Worked great. Thanks, |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-09-05 : 11:31:14
|
If you have an index on g.loadstart that the optimizer will use, try:...on g.loadstart >= dateadd(dd,datediff(dd,0,a.loaded),0) and g.loadstart < dateadd(dd,datediff(dd,0,a.loaded),0)+1 It should be even faster...Jay White{0} |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-09-05 : 11:33:22
|
| Or [this isn't in reply to Jay's]DATEDIFF(dd, 0, g.loadstart) = DATEDIFF(dd, 0, a.loaded) |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-09-05 : 11:43:12
|
Arnold, everything I know about dates in SQL I learned from your posts. Every post I make about dates in SQL is a tribute to you (assuming I answer the question correctly  ).Jay White{0} |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-09-05 : 11:52:10
|
| Actually, I'd missed that one of the dates was always a midnight... which is pretty fundamental!But I'm surprised that Jonathan's single DATEDIFF was performing better than the original -- I guess the other join condition must be reducing cutting down the possibilities to a size where the nested loop won't be too much of a burden. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-09-05 : 12:11:03
|
Me too  Although, the single DATEDIFF() call does do a third of the work that the three DATEPART() calls do, so I would expect some performance improvement due to the easier-to-compute predicate.OT: My new SQLTeam word for the day: "predicate" Jonathan{0} |
|
|
|
|
|
|
|
|
Joining Date parts of a datetime value