Condividi tramite

Stored procedure vs. SQL Query

  • Articolo

One of the common questions that I face while reviewing the applications at customer site is if SQL Server caches and reuses the plan then where is the difference in stored procedure vs. SQL Query.

There is no single answer; recommend to read Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 as this article highlights and explains most of the items in detail.

Couple of more points to remember:

  • Main goal for stored procedures is to promote plan reuse and we save on time taken to compile the SP each time it is called. SQL Server converts the name of the stored procedure to an ID, and subsequent plan reuse happens based on the value of that ID.
  • Yes, you are right plan reuse is done for Adhoc Queries also. But few catches - ad-hoc queries can reuse the plan only if the texts for two ad-hoc queries are exactly the same. The text match is both case- and space-sensitive. So, even for a slight variation in the query, SQL will end up generating totally a new plan.

Comments

  • Anonymous
    May 20, 2008
    Do you have any other thoughts on the SQL vs Stored Procedure debate?  We are using LLBLGen in preparation for moving to Linq for Entities after it is released.  The SQL is all parameterized and machine generated, so the argument that the plan will not be pre-compiled gets weaker.  SQL injection is also not an issue.   Any input you have would be appreciated. gordon.glenn@healthnow.org