July 20, 2009 at 7:56 am
Hi all,
I have two scripts created by the development team essentially doing the same thing. We asked the Dev team to rewrite the first script as it was using Dynamic SQL within a curser and this does not conform to our Development Standards. the annoying thing is that now the 'Non-Dynamic' SQL takes 6 minutes to run and the original script takes only 30 secs!
I have attached the two scripts and Execution Plans for both and would appreciate some guidance in order to investigate why the rewritten script is taking so long.
Many Thanks
S
July 20, 2009 at 8:33 am
Dynamic SQL has EXPLICIT values for stuff in the where clause, and thus the optimizer has the exact information it needs to get accurate statistics and put together the optimum query plan. Anything else (sproc parameters, internal variables) are susceptible to 'guesses' or 'imprecise' statistics gathering and also succeptible to parameter sniffing (search the web or this site - kajillions of resources out there for that).
First thing I would try is adding OPTION RECOMIPLE to the offending statement(s) to see if that helps.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply