Increase Stored Procedure execution performance dramatically
Last month we spent a lot of time in data loading optimization due to performance problems. I started with optimization of the heaviest views and put them in parameterized stored procedures . I also used a simple method to check the performance by measuring the time needed for execution. I was using MS SQL Management studio. During the test I had the following source with some local variables declared:
declare
@Param1 int,
@param2 int,
@TraceStart datetime
Set @TraceStart = GetDate()
Select Column1, Column2, ….
From Table1
Join …
Where Column1 = @Param1 and …
…
Select DateDiff(ms, @TraceStart, GetDate())
The idea was simple – to test, and then to place the same code in a stored procedure, and the declared local variables to be introduced as parameters. I managed to optimize the view drastically – from 500ms down to 1. And I was pretty happy.
Then I made the stored procedure:
Create procedure LoadData(@Param1 int @param2 int)
Select Column1, Column2, ….
From Table1
Join …
Where Column1 = @Param1 and …
Then I executed it using the following approach:
declare
@Param1 int,
@param2 int,
@TraceStart datetime
Set @TraceStart = GetDate()
Exec LoadData @Param1 int @param2 int
Select DateDiff(ms, @TraceStart, GetDate())
The results bring me a hard attack: more than 10 minutes!!!
Thanks to a friend of mine – Petar Atanasov – we solved the problem. He advised me to make a local copy of the parameters and to use them in the procedure’s body and that's exactly what I did:
Create procedure LoadData(@Param1_ int @param2_ int)
declare
@Param1 int,
@param2 int,
Set @Param1 = @Param1_
Set @param2 = @Param2_
Select Column1, Column2, ….
From Table1
Join …
Where Column1 = @Param1 and …
And that was it! The stored procedure was executed for less than 1ms!!!
The problem is that the query optimizer cannot make a good execution plan if the parameters are directly used, but it can if local variables are used – especially for date time parameters.