Execution plan for Stored procedure

Question

Post reply

Execution plan for Stored procedure

Roman Vinnik

SSC Veteran

Points: 221
More actions
July 20, 2005 at 5:49 pm

#152481

Hi,
This is a bit of a tip, as well as an invitation for comments or suggestions.
I was trying to optimise a stored procedure, when something really odd happened:
The procedure is really simple:
PROCEDURE 1:
-------------
create procedure tmp_InsertTest (
@PeriodFrom int,
@PeriodTo int
) as
begin
create table #NetColl(
ClientCode int,
AccCode int,
Period int,
Amount money)
insert into #NetColl
select tc.ClientCode,
tc.ClientAccCode,
tc.Period,
sum(tc.Amount)
from Transactions_Current tc (nolock)
where tc.Period >= @PeriodFrom and tc.Period <= @PeriodTo
group by tc.ClientCode, tc.ClientAccCode, tc.Period
select * from #NetColl
end
---------------------------------------
This procedure took 80 seconds to run, the plan showed that it was using an index scan.
By just adding declarations and re-assigning the variables, the time dropped to 19 seconds!
The plan showed that in this version, it was using a clustered index scan.
PROCEDURE 2: (bits in CAPS are changed)
-------------
create procedure tmp_InsertTest (
@PeriodFrom int,
@PeriodTo int
) as
begin
DECLARE
@PERIODFROM2 int,
@PERIODTO2 int
SET @PERIODFROM2 = @PeriodFrom
SET @PERIODTO2 = @PeriodTo
create table #NetColl(
ClientCode int,
AccCode int,
Period int,
Amount money)
insert into #NetColl
select tc.ClientCode,
tc.ClientAccCode,
tc.Period,
sum(tc.Amount)
from Transactions_Current tc (nolock)
where tc.Period >= @PERIODFROM2 and tc.Period <= @PERIODTO2
group by tc.ClientCode, tc.ClientAccCode, tc.Period
select * from #NetColl
end
---------------------------------------
What boggles me is, Why would the optimiser create a different plan based on that small change?
PS: Using the clustered index resulted in the fastest plan.
Thanks
Roman

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

The effect is called Parameter sniffing

you may have had the same effect adding with recomplie at the procedure declaration

This happens a lot when the number of records to be returned can vary drastically from one set of parameters to another

Cheers!

* Noel

kudla SSC Enthusiast Points: 166 More actions · Answer 2

I understand if the parameter values are different what you said makes sense. Newbie are the parameter values the same.

I have read somewhere that using local variables to the procedure improves the performance. Is it true.If so why ???

Roman Vinnik SSC Veteran Points: 221 More actions · Answer 3

Thanks for the input Noel, Ill read up on that.

Kudla, the parameters are sometimes the same, and somethimes not the same. made no difference in this case.