Better way to write this? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Better way to write this?

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-13 : 08:15:53

I have a query that has to be run evry day at 5:30pm. It should show tomorows data and the day after that.

This is what I have


where p.pickupTime BETWEEN DATEADD(day, 1, getdate()) AND DATEADD(day, 2, getdate())

It seems to run very slowly. Is there a better way to write this? More economical?

Thanks in advance

Laura

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-13 : 08:18:35

do you have an index on pickupTime? Is pickupTime a date comparison, or a date and time comparison?

You could set 2 variables

Declare @Begin datetime,
@end datetime

Set @begin = DATEADD(day, 1, getdate())
Set @end = DATEADD(day, 2, getdate())

... between @begin and @end

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-13 : 10:13:34

yup theres an index on Pickup time. Its a datetime field.

Thanks for the help.

L.

Kristen
Test

22859 Posts

Posted - 2006-01-13 : 10:42:48

Are you storing Date and Time in p.pickupTime - because if not then the selectivity might not be enough for the optimiser to choose the index - you'd need a clustered index for that, and that may well conflict with whatever you current use as the clustered index - probably the primary key.

What does the Query Plan look like?

How many rows a) in the table and b) will an average run select?

Kristen

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-13 : 11:03:53

Yes storing date and time. You're right it already has a clustered index.

This is a view from many tables, keep in mind I didn't write it it came from the company that makes the software.

There are anywhere between 80-150 rows returned.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 11:16:54

Sometimes I've seen seperate comparisons perform better than "between". Just for grins try changing

from:

where p.pickupTime BETWEEN DATEADD(day, 1, getdate()) AND DATEADD(day, 2, getdate())

to:


where p.pickupTime >= dateAdd(day, 1, getdate())
and   p.pickupTime <= dateAdd(day,2, getdate())

Be One with the Optimizer
TG

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-13 : 11:29:14

Good idea but I had to kill it. Was still looking after 30 seconds.

Hmmmm.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 12:03:49

>>This is a view from many tables
The problem could be unrelated to the date comparison. The view may not be optimzied to perform this query or perhaps there are other criteria that's causing the slowness. Or maybe there are subqueries or UDFs in your select list?

Can you post your entire statement?

Be One with the Optimizer
TG

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-13 : 12:07:16

I think I may have fixed it. I told it


where p.pickupTime BETWEEN getdate()  AND DATEADD(day, 2, getdate())

and it seemed to like that.

Thanks for all the help.

Laura

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-13 : 12:53:51

Actually this may be even easier to answer: Is it possile to say

"show me everything between 1159 tonight and midnight tomorow"?

Thanks

Laura

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-13 : 13:08:59

You realize, I hope, that the DATEADD(day) function retains the time portion of the datetime value. So if you run this at 5:30 pm on Friday, January 13:

where p.pickupTime BETWEEN DATEADD(day, 1, getdate()) AND DATEADD(day, 2, getdate())

...you are actually running this:

where p.pickupTime BETWEEN '2006-01-14 05:30 pm' AND '2006-01-15 05:30 pm'

...so you are going to miss a big chunk of tomorrow's data.
Explain EXACTLY what you want this to do...

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-13 : 13:41:40

I need it to show me all load to be picked up between tonight at midnight and tomorow at 5:50pm.


where p.pickupTime BETWEEN midnight AND 5:30-pm

thanks

Laura

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-13 : 13:52:30

Laura,

Follow the suggestions in the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-01-16 : 05:46:32

If:

where p.pickupTime BETWEEN DATEADD(day, 1, getdate()) AND DATEADD(day, 2, getdate())

performs significantly worse than

where p.pickupTime BETWEEN getdate() AND DATEADD(day, 2, getdate())

its probably parameter sniffing, or whatever its called. Try replacing the Start / End dates with @Variables as SeventhNight suggested. That might give the optimiser a better shot.

"show me everything between 1159 tonight and midnight tomorow"

So you want everything "tomorrow"?


DECLARE @dtBegin datetime,
        @dtEnd datetime

SELECT @dtBegin = DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 1),
       @dtEnd   = DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 2)
..
WHERE     p.pickupTime >= @dtBegin
      AND p.pickupTime <  @dtEnd

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources