>I'd say there's nothing inherently wrong with stored procs, unless the "reasons" for using them are: 1 - they are "precompiled" and 2 they prevent sql injection and 3 they are the best API for CRUD. None of these things are true.
I agree with you on #2, don't necessarily agree with 1 and 3. 1 and 3 "can" be true.
>That reminds me of a question I have been wanting to pose. Assuming I have 1000 cached execution plans and I compare 1000 sprocs that each run a query vs directly running the individual queries,
>
>Wouldn't SQL have to lookup the sproc, then look at the query, then determine which plan, then execute
>instead of
>look at the query, then determine which plan, then execute?
>
>in other words, wouldn't having to scan the 1000 sprocs take extra time? I'm pretty sure that time would be less than the time spent transmitting the raw SQL vs transmitting the sproc name, but it seems reasonable.
Two things. First, if someone has 1,000 procedures, there "could" be an opportunity for refactoring. 2 specific features in SQL Server 2008 (MERGE and Table-Type Parameters) create opportunities to reduce the # of procedures.
Second, it would need to be benchmarked, but I'm fairly confident it's the reverse, that resolving the name of the execution plan is small compared with transmitting and then re-evaluating the SQL syntax. That would actually be a good experiment. Actually, it wouldn't surprise me if you ran the experiment across multiple environments and got different results.
I seem to recall reading that Microsoft added some functionality "under the hood" in SQL Server 2008 to handle dynamic SQL (someone who worked with the product team told me it was to improve the overall performance of some of the queries generated by LINQ to SQL). If that is indeed true, that might potentially add some time, however small, to the dynamic SQL process.
But the bottom line is this - a number of DBAs "force-funnel" access through procs, and I personally have seen increases in that trend over the last 10 years. I've never said that stored procs should be used 100% of the time in all cases - but I do think they represent the strongest starting point.
