Stored Procedures and Execution Plans
Myron Schroner
(a) Is there now little or no speed advantage to using
stored procedures? (Seems to be true.)
(b) Do we still have to recompile stored procedures after
altering the base tables/views? (Sounds like maybe not.)
From BOL:
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%
20Server\80\Tools\Books\architec.chm::/8_ar_da_0nxv.htm
----------------------------
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.
Myron Schroner
(a) Is there now little or no speed advantage to using stored
procedures? (Seems to be true.)
(b) Do we still have to recompile stored procedures after altering the
base tables/views? (Sounds like maybe not.)
From BOL:
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books
Myron Schroner
problems.)
BP Margolin
SQL Server 2000 stored procedures, unlike SQL Server 6.5 stored procedures,
are not pre-compiled and stored in sysprocedures ... in fact sysprocedures
disappeared as of SQL Server 7.0.
However, once invoked, a query plan is created and cached. Subsequent
invocations of the stored procedures will use the cached query plan, so ...
Yes, there continue to be performance advantages to using stored procedures
in SQL Server 2000.
For significantly more detail, I strongly recommend "Inside Microsoft SQL
Server 2000" by Kalen Delaney.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Myron Schroner" <myrons...@yahoo.com> wrote in message
news:06f401c15830$0242a0c0$a5e62ecf@tkmsftngxa07...
Pat Phelan
You typically save a considerable amount of network traffic that would be used to transmit the body of the procedure. This can be substantial when you are using a slow link.
You keep 98% of the advantage of not having to sweat the permissions issues. As long as the user the compiled the sproc can execute the statements in it, anyone with permission to execute the sproc can do it too. This is a major advantage that people often overlook.
You no longer have the tokenized plan stored with the procedure. That means that SQL Server essentially reparses the procedure the first time it is executed, and builds a fresh plan. Since the code stays 100% constant, you only tokenize it once, and rarely rebuild the execution plan (at least compared to ad-hoc T-SQL).
So to answer your questions, A) the speed advantage is different than it used to be, but it can still be substantial in some environments, and B) No, the need to recompile is practically eliminated.
-PatP
========================================
Please post DDL at the very least, and DML when possible to make it easier for people to help you.
Please reply only via newsgroups, as I rarely reply to email.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!