0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ahsan Mumtaz Abbasi
  • 53
  • 1
  • 11
  • I just wonder how many records your table has? – Eray Balkanli Feb 09 '18 at 20:01
  • 2
    And what is happening to running time when you do: code='22' with single quotes? – Eray Balkanli Feb 09 '18 at 20:02
  • 1
    Since `code=22` is running fast, it suggests that your datatype for `code` is numeric (an Integer probably?). When you declare your variable you declare it as a varchar(). So now your database has to CAST(code as VARCHAR()) which means no index is being used and plenty of CPU in order to compare in WHERE clause. Declare that variable as the proper datatype and you'll probably see faster response times. – JNevill Feb 09 '18 at 20:06
  • sir i'm really sorry due to little mistake it create lot of confusion. all data type is varchar – Ahsan Mumtaz Abbasi Feb 09 '18 at 20:38
  • There is already an answer: https://stackoverflow.com/a/4459760/8524164 . In short the reason in SQL optimizer which creates a plan to estimate results. Adding `OPTION(RECOMPILE)` to the end of the query will fix it. And much more information here: http://www.sommarskog.se/query-plan-mysteries.html – Andrey Ampilogov Feb 09 '18 at 21:15
  • thank you so much for solution by adding OPTION(RECOMPILE) and it works ... but please let me clear why we need to recompile my procedure ever time. – Ahsan Mumtaz Abbasi Feb 12 '18 at 23:26

2 Answers2

0

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.

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
0

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.

my779
  • 59
  • 6