4

Possible Duplicate:
What are the pros and cons to keeping SQL in Stored Procs versus Code

I was listening to Hanselminutes podcast "Rise of The Micro ORM," where one of the guests (Sam Saffron and Rob Conery) outlined the classic reasons that DBA's insist on stored procedures:

  1. They are pre-compiled, which gives them an execution speed advantage
  2. They hide the underlying database scheme, which allows a separation of interface and implementation that prevents brittleness.

A guest then said these aren't good arguments, and suggested the real reason the DBA's will insist on stored procs is because they simply want to protect themselves from the ignorance of the middle-tier developers.

I found that statement to be a bit on the extreme side. Certainly I can agree that argument #2 is flawed, but I thought it was well known that sending arbitrary (uncompiled) SQL to the database was a performance hit. Is there something I'm missing that would explain why argument #1 is not really true?

My own answer, as just a guess, is that there is a performance hit - but it rarely matters. It is perhaps analogous to a developer who attempts to optimize every loop he writes, even though only 1% of the loops written ever benefit from the tuning. Am I capturing the thought correctly?

Community
  • 1
  • 1
Brent Arias
  • 29,277
  • 40
  • 133
  • 234

3 Answers3

8

"but I thought it was well known that sending arbitrary (uncompiled) SQL to the database was a performance hit."

The distinction you're making between stored procs and other sql statements regarding precompilation hasn't existed since SQL 6.5.

Stored Procedures and Execution Plans

In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure. Also, the fully compiled execution plan for the stored procedure was retained in the SQL Server procedure cache, meaning that subsequent executions of the stored procedure could use the precompiled execution plan.

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements.

http://msdn.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx

heisenberg
  • 9,665
  • 1
  • 30
  • 38
  • 1
    Yep, this. It used to be true that ad-hoc queries weren't pre-compiled or otherwise optimized. That argument went away a long time ago. My cynical view is that DBAs just don't want to relinquish any control or job security to application developers. – Yuck Jun 10 '11 at 16:25
1

In my experience, most DBAs could no more write a stored proc then they could fly the space shuttle. Everywhere I've worked stored procs have been written by the application developers, who also designed and implemented the databases.

Having said that, stored procs are not innately faster than using, say views, and may indeed be slower if written by inexperienced developers using stuff like cursors.

0

as for performance: Either use Stored procedures or Precompiled statements.

as for abstraction: Either use a DAL/ORM or Stored procedures.

Sure, stored procedures can do things that you can't do from the outside and with this performance. So as usual, it depends..

codymanix
  • 28,510
  • 21
  • 92
  • 151