use variables in query instead of stored procedure parameters

Question

I have a stored procedure with some parameters (I've used generic type and name, just for the example). When I have it like the code bellow, it runs in two minutes:

    create procedure [dbo].[name]
        @param1 type,
        @param2 type, 
        @param3 type,
        @param4 type,
        @param5 type,
        @param6 type
    as
    begin
        SELECT 
            COLUMNS
        from 
            table1 t1
            inner join table2 t2 on t1.id = t2.j_id
            left join table3 t3 pn t2.column3 = t3.id
            ............
        where
            (@param1 is null or @param1 = some_column)
            and (@param1 is null or @param1 = some_column)
            and (@param1 is null or @param1 = some_column)
            and (@param1 is null or @param1 = some_column)
            and (@param1 is null or @param1 = some_column)
            and (@param1 is null or @param1 = some_column)
    end

But if I change the code and declare variables and use them in query, it runs in a second. Why?

Here is the "optimised" code:

create procedure [dbo].[name]
    @param1 type,
    @param2 type, 
    @param3 type,
    @param4 type,
    @param5 type,
    @param6 type
as
begin
    declare
        @var1 type = @param1,
        @var2 type = @param2,
        @var3 type = @param3,
        @var4 type = @param4,
        @var5 type = @param5,
        @var6 type = @param6
    SELECT 
        COLUMNS
    from 
        table1 t1
        inner join table2 t2 on t1.id = t2.j_id
        left join  table3 t3 on t2.column3 = t3.id
        ............
    where
        (@var1 is null or @var1 = some_column)
        and (@var2 is null or @var2 = some_column)
        and (@var3 is null or @var3 = some_column)
        and (@var4 is null or @var4 = some_column)
        and (@var5 is null or @var5 = some_column)
        and (@var6 is null or @var6 = some_column)
end

Why the second version of stored procedure runs much faster?

Parameter sniffing. When you rebind parameters to local variables the execution plan is evaluated every time — Lukasz Szozda
– Lukasz Szozda, Commented Dec 23, 2015 at 10:51
I've read about this but if at the first version I ann recompile option, it runs much faster (1 second), but I don't understand what's different in having parameters in query or variables in query — Buda Gavril
– Buda Gavril, Commented Dec 23, 2015 at 10:53
@BudaGavril When you first run the SP, the query optimizer optimizes it for the parameter you gave and cache it. Next time you run it the cached plan is not optimal for new set of parametrs. That's why you get performance penalty. You can add OPTION (RECOMPILE) or OPTION(OPTIMIZE FOR @par = UNKNOWN... — Lukasz Szozda
– Lukasz Szozda, Commented Dec 23, 2015 at 10:54

Buda Gavril · Accepted Answer · 2015-12-23 11:49:06Z

1

Based on @lad2025, here is why it works faster:

Workaround : Use local variable

This workaround is very similar with previous one (OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN)))
when you assign the paramaters to local ones SQL Server uses statistic densities instead of statistic histograms
So It estimates the same number of records for all paramaters
The disadvantage is that some queries will use suboptimal plans because densities are not precise enough as the statistic histogram.

http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx

answered Dec 23, 2015 at 11:49

Buda Gavril

21.7k41 gold badges133 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

use variables in query instead of stored procedure parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related