SQL Server Performance Forum – Threads Archive
different query plans for query and spHi, (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
Did you update statistics or/and rebuil index as part of maitenance plan?. Luis Martin
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?
yes I did
I even tried to mark it "with recompile"
But SQL Server is so stubborn in choosing the highly inefficient plan… [?]
Claudiu, I find this in olds post check it. http://groups.google.com/groups?hl=en&[email protected]&rnum=3 HTH Luis Martin
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
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