Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kankanala
Starting Member
3 Posts |
 Posted - 2010-04-20 : 13:52:02
|
| Hello,i have a query on my SQL server which is taking 3 min to execute when i compare using an integer value in the where clause but it takes only 2 seconds when using a variable. let me give you an example of the query for better understandingExample:Query 1(takes about 3 min)select * from user_table where user_id = 1Query 2 ( takes about 2 seconds)DECLARE @USER_ID AS INTset @USER_ID = 1select * from user_table where user_id = @USER_ID both the queries are exactly same except comparing the value using a variable and directing comparing a value. please can you let me know if i need to change any setting in the database or anything. i t looks weird that both queries are exactly the same but there is a mjor difference in the execution time.I would greatly appreciate any help resolving the issue. thank you for your time.Vamshi |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kankanala
Starting Member
3 Posts |
Posted - 2010-04-20 : 14:30:01
|
| Thanks for the Quick response tkizer. i have read about parameter sniffing. the issue is i am passing the query directly to the SQL server (not as a stored procedure). is there anyway that i can reset the execution plan without changing the query. (like recompile option for stored proc)ThanksVamshi |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kankanala
Starting Member
3 Posts |
Posted - 2010-04-20 : 15:01:08
|
| Thanks for the info Tara Kizer. i did the DBCC FREEPROCCACHE of the database and UPDATE STATISTICS of the tables in the query. it fixed the issue. the query gives me reponse in 2 seconds. i have learned a new thing today "SQL Parameter Sniffing". i greatly appreciate your help with this issue.ThanksVamshi |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
where clause using data type vs variable