SQL Query much slower when using variables.. I dont get it?

Question

SQL Query much slower when using variables.. I dont get it?

Brian Corrigan

SSC Rookie

Points: 49
More actions
June 2, 2004 at 8:36 am

#87506

This is strange.. What am I missing?

The following code takes 12 seconds to run...

select sessionid as SessionID from transactiontable (NOLOCK)
where transactiontime between '2004-04-01 05:00:00.000' and '2004-04-02 05:00:00.000'
group by sessionid

Slightly modified (to make it dynamic for use within a stored procedure) takes over 3 minutes...

declare @startdate datetime
declare @enddate datetime
set @startdate='2004-04-01 05:00:00.000'
set @enddate ='2004-04-02 05:00:00.000'
select sessionid as SessionID from transactiontable (NOLOCK)
where transactiontime between @startdate and @enddate
group by sessionid

The clustered index is transactiontime.. The results are exactly the same.. What am I missing?!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ashcroal Old Hand Points: 329 More actions · Answer 1

I think you will find it is because values for the variables are not known at compile time, and therefore the execution plan is less than optimal ;-). The solution is to pass the variables in as params

create proc test( @startDate datetime, @endDate datetime) as begin

select sessionid as SessionID from transactiontable (NOLOCK)
where transactiontime between @startdate and @enddate
group by sessionid

end

go

then call the sproc passing in the params

exec test '2004-04-01 05:00:00.000', '2004-04-02 05:00:00.000'

One other problem could be that the columns are different data types as well - but I can't tell that without looking at your table design. ie. the columns could be smalldatetime rather than datetime.

Hope that helps.

Regards

Al

Eaton Valued Member Points: 74 More actions · Answer 2

Al makes a good point. Let's see your table defintion.

12 seconds to three minutes is a huge difference; maybe your "dynamic" code isn't using the clustered indexed as expected. Did you actually run this 3 minute version in Query Analyzer by itself and not within a stored proc? Also, check the output from the Display Estimated Execution Plan and let us know.

Eaton