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 |
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
 Posted - 2010-01-04 : 09:59:55
|
| I have a query, nothing complicated, just a join between 3 tables. It runs fast if run in query analyzer, but if I make a SP and run exactly the same query with same parameters, its running forever.Any suggestions? |
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-04 : 10:01:39
|
| You will most likely have to provide some examples and code snippets, I would start by giving the original SQL and also the SP. |
 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-01-04 : 11:02:06
|
| It could be parameter sniffing. Search the site for it, you will get ideas. |
|
|
|
dhilditch
Starting Member
2 Posts |
Posted - 2010-01-04 : 11:25:19
|
| If you look at the execution plans of the two queries you will see the 'estimated number of rows' varying between the two executions. Probably you have the variables hard coded in the transact SQL version and not in the other?You'll find you can optimise the SP version to run as fast as the vanilla version using a variety of different methods - best one is probably:- option (optimize for (@p1 = 'x')) and just stick 'typical' parameter values in there.Possibly you are manipulating the parameters in some way inside the SP and forcing SQL Server to guess at the values of the parameters at compile time? Any chance you could post the code?www.skyscanner.net |
|
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2010-01-04 : 17:49:38
|
| read about parameter sniffing and this was exactly the reason.Thank you Vijay and David. |
|
|
|
|
|
|
SP takes a long time and query does not