SQLnewbie (ga*******@hotmail.com) writes:
But aren't their situations where controlling the stored procedures
would cause query recompilation (Execution Plan recreation) which could
lead to performance issues.
Not sure what you mean here, but generally passing variables to stored
procedures does not cause recompilation.
On the other hand sometimes you may wish that it would. We had a case
in our application. There was a stored procedure that I frequenly caught
doing a complex parallel plan requiring a couple of seconds to run,
when there was a much more effcient plan for the task.
The problem was that the client that called this procedure first called
it with a 0 for its only parameter which means "bring me all from the
last 20 days". On successive calls, the client only wanted the rows
with an id higher than the highest id in the previous call. The problem
was that the plan for getting the rows for 20 days was different from
getting the delta, but it was this plan that ended up in the cache. My
solution in this case was to write to sub-procedures, one each for the
two cases. But to the client, it still looked the same. (Except that
performance was better!)
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp