Slow SP with date parameters solved.

,

When i use datetime fields in the where clause like timestampfield > {ts '2010-01-01 00:00;00'} and timestampfield < {ts '2010-01-31 23:59:59.998'} or timestampfield between {ts '2010-01-01 00:00:00'} and {ts '2010-01-31 23:59:59'} the query runs fine, but if i need to use it on stored procedeures like parameters like this:

CREATE PROCEDURE withDates

@d1 as datetime,

@d2 as datetime

AS

BEGIN

select * from tablewithdates where datetimefield between @d1 and @d2

END

 

The Procedure becomes slow, if try the same query with the direct values the query runs very fast. I don't know if there is an answer to this or if it is a bug, but, in many forums the people has the same problem: very slow stored procedures that uses date parameters.

If you use the code that i propose. The SP becomes fast as if you put the direct values. Enjoy.

CREATE PROCEDURE withDates
   @d1 as datetime,
   @d2 as datetime
AS
BEGIN
 DECLARE @dateparams TABLE (d1 datetime, d2 datetime)
 insert into @dateparams (d1, d2) values (@d1, @d2)

 --this is more fast than the normal way
 select * from tablewithdates where datetimefield > (select d1 from @dateparams) and datetimefield < (select d2 from @dateparams)

 --normal way, becomes very slow for SQL
 select * from tablewithdates where datetimefield > @d1 and datetimefield < @d2

END

Rate

2.05 (20)

Share

Share

Rate

2.05 (20)