September 23, 2009 at 1:44 pm
Hi experts, I have always wanted to know this,
What is the best way to write a tsql code for parametrized date ranges? For example
create procedure procname
(
@datestart datetime,
@dateend datetime
)
as
select column1
from myTable
where date between @datestart and @dateend
I have no ddl or anything, I have just always wanted to know an optimal way to achieve this to prevent problems like excessive reads or parameter sniffing when the date range increase. For the parameter sniffing problem, could declaring the @datestart and @dateend locally work?
September 23, 2009 at 3:46 pm
If you look carefullt in the query plan, you'll notice that the BETWEEN directive is converted into 'X>=100 AND X<=200' pattern, so that's for optimizations.
As for performance, I sometimes create a clustered index on date columns where such query is more common
September 23, 2009 at 4:08 pm
sqlislife (9/23/2009)
Hi experts, I have always wanted to know this,What is the best way to write a tsql code for parametrized date ranges? For example
create procedure procname
(
@datestart datetime,
@dateend datetime
)
as
select column1
from myTable
where date between @datestart and @dateend
I have no ddl or anything, I have just always wanted to know an optimal way to achieve this to prevent problems like excessive reads or parameter sniffing when the date range increase. For the parameter sniffing problem, could declaring the @datestart and @dateend locally work?
I'll start with the standard answer, It Depends.
I have come to prefer this:
declare @datestart datetime,
@dateend datetime;
set @datestart = '2009-09-23';
set @dateend = '2009-09-24';
select
column1
from
dbo.mytable
where
datecol >= @datestart and
datecol < @dateend;
Reason, the pesky time portion of the datetime data type. If you use between you can get data from midnight of the next day, and that may not be what you want.
September 23, 2009 at 5:08 pm
Thanks for the response as always Lynn, are you suggesting initializing the @date parameters with an actual dummy date as opposed to intializing them with the variables to prevent parameter sniffing to have something like this for example;
create procedure procname
(
@datestart datetime,
@dateend datetime
)
as
declare @datestart datetime,
@dateend datetime;
set @datestart = '2009-09-23';
set @dateend = '2009-09-24';
select
column1
from
dbo.mytable
where
datecol >= @datestart and
datecol = @datestart and
datecol < @dateend;
select column1
from myTable
where date between @datestart and @dateend
September 23, 2009 at 5:10 pm
Thanks for your input as well Benyos, it has been duly noted !!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply