SQL Server Performance Forum – Threads Archive

different query plans for query and sp

Hi, (SQL Server 2K.)
I have a sp that I have thoroughly optimized. It used to work under one second for a given parameter set. All at once the query plan changed and now it takes 9 seconds!
If I take the body of the sp and execute it as a query (the parameters are variables now), it uses the old fast query plan. The code (in the qry and sp) is exactly the same.
How can I force the sp to use that fast query plan ? tia
Claudiu

---

Did you update statistics or/and rebuil index as part of maitenance plan?. Luis Martin
Moderator
SQL-Server-Performance.com

---

Sure thing<br />That was the first thing I thought of.<br />But to no avail <img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /><br /><br />Claudiu

---

what intrigues me is that the body of the sp executed as a query uses the efficient plan. [?]Claudiu

---

Did you try recompiling sp?
Luis Martin
Moderator
SQL-Server-Performance.com

---

yes I did
I even tried to mark it "with recompile"
But SQL Server is so stubborn in choosing the highly inefficient plan… [?]
Claudiu

---

Claudiu, I find this in olds post check it. http://groups.google.com/groups?hl=en&[email protected]&rnum=3 HTH Luis Martin
Moderator
SQL-Server-Performance.com

---

thanks Luis, Finally, after lots of time spent re-reading the documentation I remembered that I can specify a join hint.
Now my "left join" became "left loop join" instead of the SQL optimiser "left hash join". It’s interesting how solutions come faster when one shares the problem with another. thanks again
Claudiu

---

Hi Luis, That old post is very good. If I remember correctly, the web seminar in www.sqlmag.com Why Stored Procedures Fail – Writing and Debugging Great Stored Procedures also mentions this area.

---

"It’s interesting how solutions come faster when one shares the problem with another." That’s this Forum are for. Thanks both. Luis Martin
Moderator
SQL-Server-Performance.com

---

]]>