Datetime Variable Slowing Query?

[TimJL]

I'm trying to write a report from pulling data from several sources and have managed to write a query that gets me towards what I need.

However, when I tried to convert this into a stored procedure
the execution time rocketed from 0-2 secs to 3-4 mins!

I've narrowed this down to the effect of replacing literal strings with variables.

(i.e WAS "between '06/06/03 00:00' and '06/10/03 23:59'"
is now "between @StartDate and @Removedate"

Any ideas how i can work around this?

 

[Bygbobbo]

I have run into this problem, could you be more specific about where your variables are coming from.

I have found that if you dynamically create your variables within your sql (i.e. between @present_date to dateadd(m,-1,@present_date) ) it slows the query down.

As compared to:
declare @present_date datetime, @post_date datetime
set @present_date = getdate()
set @post_date = dateadd(m,-1,@present_date)

select *
from tablename
where 0 = 0
and date between @present_date to
@post_date

by setting variables before the query the time is cut many fold.

To help you better next time post your whole query.

Also, a stored proc should run a bit quicker than an uncompiled query.

hope this helps,
Bygs