SQL Server Stored Procedure Global Variable

Question

I'm facing a little problem in writing a stored procedure in SQL Server. When I pass global or local variable to the select query, it takes 3 minutes to execute; however, when directly passing the value instead of through a variable, it takes just 1 second.

For example:

--------this query takes 3 minutes----------------
Declare @code varchar (10)
begin
    select abc 
    from <table> 
    where code = @code
end

---------------this query take 2 seconds----------
Declare @code varchar (10)
begin
    select abc 
    from <table>
    where code = 'A22'
end 

Please guide/suggest me how handle this logic however I have to use first logic

Answer 1

As far as I see in your first query you are using a varchar(10), but using an integer for your second query (no quotes in the where clause). Filtering/Comparing by Int variables are faster than varchar (string) ones since int variables take up much less space than varchar variables (strings). That is why indexing provides fast search.

Edit -> Since post-owner stated that both filters are done by using varchar: Using hard-coded values while doing filter in a "where" clause for sql server, because the best query to run is already known via statistics. There is a good example here: Why SQL Server go slow when using variables?

The example basicly says, if I want you to get me 1KG of oranges, you will take a basket and bring them to me, because you know a basket would be enough already. But if I tell you that "bring me @value of oranges" then you need to find out what @value is and what kind of tool -basket? bag? maybe a truck?- would be enough for this process, and this takes time.

By the way, if you are doing it in a Stored Procedure, could you please try adding "WITH RECOMPILE" to your SP while creating? In some forums, it says this might help increasing the performance for such situations you are having.

Answer 2

When the values are hardcoded into the query, the query analyzer will look up at saved statistics to generate the best query plan, hence it runs much quicker.

Index on the code would probably help speed it up in general.