April 26, 2006 at 1:22 pm
I am curious to know why the declaration of a variable within a storedproc increases execution time.
If i place a declare variable inside of the proc the execution time goes up to 33 secs versus 2-3 seconds if i remove the variable and use static values or move the declaration the list of values the proc expects and set a default value. Any insight would be appreciated.
Ex of production procedure design. in production this procedure references table with a million plus records
Create Proc uspTestProc (@var1 varchar(10), @var2 varchar(10)
as
declare @var3 varchar(10), var4 varchar(10)
Set @var3 = @var1 + 2
Set @var4 = @var2 + 2
Begin
Select columnA from tableA where columnB between @var3 and @var4
End
April 26, 2006 at 2:13 pm
Try looking at the execution plan for both scenarios, with and without the variables. Maybe with the variables SQL Server is having a hard time determining whether or not to do an index seek, index scan or a table scan. Maybe an index hint when you use the variables in the proc would help. Just an idea.
HTH,
Mark
April 26, 2006 at 6:42 pm
Seems it's data type mismatch.
What is the datatype of ColumnB?
The statement
Set @var3 = @var1 + 2
will implicitely convert @var1 from nvarchar to int and than convert int result to nvarchar for @var3.
Implicit conversion does not allow the query to use index.
_____________
Code for TallyGenerator
April 27, 2006 at 7:46 am
Here's a better example of what we are doing.
The only difference in the procedures is in the way @var3 and @var4 are loaded. If @var3 and @var4 are declared within the procedure, the proc runs over 30 seconds. If @var3 and @var4 are declared then passed to the procedure, the proc runs in 2-3 seconds.
EX:
SlowProc – pass in 2 variables then derive the last 2
Create Proc uspProc (@month varchar(10), @year varchar(10))
As
Begin...
Declare @var3 datetime, @var4 datetime
Set @var3 = @month + '/01/' + @year
Set @var4 = @month + '/31/ + @year
Fast Proc – Pass in all 4 variables When I pass in the @var3 and @var4 the proc run in 2-3 seconds versus 30-40 seconds
Create Proc uspProc(@month, @year, @var3, @var4)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply