Parameters in stored procedures
-
So - I have a good one for you guys.... I have stored proc that takes in 2 date parameters - @startDate and @endDate - both varchar(50).... the query inside is a simple query that is a select based on the date criteria. I execute my stored proc -
exec StoredProc '7/1/2005', '7/31/2005'
It takes close to 8 mins....
In the top part of the stored proc I have:
CREATE PROCEDURE StoredProc (@startDate varchar(50), @endDate varchar(50)) AS
SET NOCOUNT ON
select * from orders
where orderDate between @startDate and @endDate
Now.... 8 mins being too long to return this data, I was playing with the stored proc and I changed the name of the parameters - and use local variables to run the query - So now I have...
CREATE PROCEDURE StoredProc (@startingDate varchar(50), @endingDate varchar(50)) AS
declare @startDate varchar(50)
declare @endDate varchar(50)
SET NOCOUNT ON
set @startDate = @startingDate
set @endDate = @endingDate
select * from orders
where orderDate between @startDate and @endDate
And it runs in very few milliseconds....!!
Can anyone tell me why this is case ? I'm afraid it may be affecting performance in other places too....
Thanks !
-
When you company orderDate (a datetime datatype) to @startdate which is also a datetime, it runs much faster than when it is compared to a varchar datatype. the conversion takes more time.
-
yes, but it is being passed in as varchar - so the local variable and the input paramters are both varchar...:-/
-
You could try this, should be faster too..
CREATE PROCEDURE StoredProc (@startDate datetime, @endDate datetime) AS
SET NOCOUNT ON
select * from orders
where convert(varchar(10),orderDate,101) between
convert(varchar(10),@startDate,101) and convert(varchar(10),@endDate,101)
-
yes, it is faster - but I still don't get why changing the names would make it so much faster.... they are both varchar - the scenario is both the same... AND - the stored proc ran fine when it was first created - and there were just as many records.....:-/
-
Hi MR,
The input parameters, which I have supplied aren't the VARCHARs,they are DATETIMEs. By using the convert function, and DATETIME style, you can supply any datetime format to the procedure and it converts in to the given style .It makes it faster when comparing the other datefields.
-
ahh - I see - but isn't the convert taking it back to varchar ?
-
Yes it is but when u supply the input parameters like @startdate as '2005-07-27', '2005/07/27' are in any other formats, the convert style 101 change it as '07/27/2005'. So it works pretty fast as it converts all the datetime fields in to the same format. You can also see the CAST and CONVERTstyles in BOL .
-
Thanks for all the helpfull info - but I'm not sure that was what was causing my slowdowns in the first place - when I changed it to a local variables, they are still varchars and all I'm doing is just setting a local variable to the input parameter and using that instead of using the input parameter directly in my query.....
-
Changing the name of the variables can't be the reason. but you could always reduce the exec. time by using the Convert
-
"Changing the name of the variables can't be the reason." - I figured that
- I guess it has something to do with using local variables as opposed to input variables ? .... thats why it is perplexing ... although using the local variables has reduced my time to fractions of a second.... -
This is known as a phenomena called parameter sniffing.
But related more to a cached query plan. the optimizer is guessing at the values your going to use, based off previous executions and index/Statistical information available to the optimizer. and executes your parametes against an invalid query plan.
Changing the parameter names as you did hides this from the optimizer, and cannot guess the plan as it tried to before.
Article about parameter sniffing/Procedure recompiling
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply