getdate() slows qry down 2

[grnzbra]

I have the following WHERE clause in a SELECT statement.

WHERE (Appointment_Dt BETWEEN DATEADD(d, 14, CONVERT(CHAR(10),GETDATE(),101)) AND DATEADD(d, 28, CONVERT(CHAR(10),GETDATE(), 101)))

The statement takes about 15 minutes to run. If I change it to

WHERE (Appointment_Dt BETWEEN DATEADD(d, 14, '3/24/2009') AND DATEADD(d, 28, '3/24/2009'))

it takes 2 seconds. I have tried moving the hard coded date to a declared variable, and replecing it in the WHERE clause with the declared variable, but it still takes about 15 minutes to run.

Any ideas of what could be happening and, in particular, how to get it to run faster with variables in the SQL statement?

 

[gmmastros]

I suggest you update your statistics.

[tt][blue]sp_updatestats[/blue][/tt]

On a large database (hundreds of megs), this could take several minutes, slowing down other operations.

I would also make sure there is an index on the Appointment_Dt column.

If you're not sure about the indexes, run this a post the output here.

[tt][blue]sp_helpindex 'YourTableNameHere'[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Not GetDate() is the problem, but the convert to CHAR() and convert back to datetime.
Try with this:

DECLARE @StartDate datetime
DECLARE @EndDate   datetime

SET @StartDate = DATEADD(dd,14,DATEDIFF(dd,0,GETDATE())) && 14 days from now w/o time portion
SET @EndDate = DATEADD(dd,28,DATEDIFF(dd,0,GETDATE())) && 28 days from now w/o time portion

---And then:
.....
WHERE     (Appointment_Dt BETWEEN @StartDate AND @EndDate

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[grnzbra]

Thank you. I will have to hold off on running the sp_updatestats because it is a large database.

Also, there is an index on Appointment date.

I'm not sure of what role the statistics play in this. Books On Line left me a little fuzzy about that.