DateTime variable problem

Hi,
The followng snippet runs OK in SQL Query analyser when a literal date
'1/11/2006' is used for the first date comparision
When local variable @.StartDate is used instead it runs on forever (I think,
certainly an order of magnitude longer)
(I added the set dateformat dmy and switched to the numeric date format in
an effort to solve this.
Ideally I want to run with '1-Nov-2006' which for some reason runs faster
than the numeric version.)
Any ideas what I am doing wrong?
thanks
Bob
declare @.StartDate datetime
declare @.EndDate datetime
set dateformat dmy
set @.StartDate='1/11/2006'
set @.EndDate = '2/11/2006'
create Table #Temp (icp_id int)
insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory rh
inner join metershistory mh
ON rh.id = mh.routehistory_id
inner join icps i on mh.icp_id=i.id
WHERE rh.type = 1 and i.company_id =1
and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
mh.cant_read_code is null
It will be because the optimiser is allowing for different values in the
variable. With the literal it knows to use the date index - with the variable
it is allowing for a large date range.
Look at the query plan and change the query (mayne a subquery for the date)
or give a hint or maybe include the other columns in the date index to make
it covering.
Maybe something like
FROM (select * from routehistory where read_date between @.StartDate and
@.EndDate) rh
.....
"Bob" wrote:

> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
>
|||Bob
I have a couple of questions
1) Do you have an index (probably CI would be good choice) on read_date
column?
2) What happened if you change date format to YYYYMMDD and don't use SET
DATEFORMAT
"Bob" <bob@.nowhere.com> wrote in message
news:uRoiCglIHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I
> think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
> rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
|||Bob,
I think that you are being encountering what has become know as 'parameter
sniffing'.
You may wish to review these articles:
Stored Procedure -Parameter Sniffing
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx
http://tinyurl.com/f9r2
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Bob" <bob@.nowhere.com> wrote in message
news:uRoiCglIHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I
> think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
> rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
|||Hi All,
Thank you for your replies.
I won't pretend I understand Parameter sniffing.
What I have done is put the query into a sproc which was my end goal anyway
and I am tuning that up.
regards
Bob