In a recent discussion a statement was made that stored procedures perform
better than dynamic SQL statements execution.  This assertion was challenged
and it there has been a remark that in SQL Server 2000 that stored procs are
not faster although it is not sure if ths holds for SQL Server 2005.

Can anyone please give me any clarifcation on this point

There is no way to say generically that sp's are faster than dynamic sql or
visa versa.  Both can be very fast if they are properly used and you can get
plan reuse out of them.  If you build the dynamic sql in such a way as it
does not reuse the previous plan it will never be as fast as identical code
in a sp that does reuse the plan.

Show quoteHide quote

JOhn,
I'll take a stab at this... There is a lot of misunderstanding and 
misinformation about this... Most of the differences come from
generalizations ... but here goes.

IN a VB prog, perhaps you have about 10 t_SQL statements  you must execute
to get your final result set... One statement does a lookup somewhere, which
gives you a value that is used to look something else up, which is eventually
used to make the final SQL statement you use to get your final result set..

As a generalization, putting all of this in an SP WILL run faster... Why?...
because executing 10 statements is 10 round trips across the network, and
putting it all in a SP is 1 round trip... This has nothing to do with the
speed of SQL, just network trips.  So as a generalization, putting things
which require multiple statements will run faster in an SP than not.

Each statement would not run faster on the SQL Server however.  It used to
be true that the execution plans for individual statements were not likely to
be re-used, but execution plans for an SP were... Another reason to use SPs. 
However, since SQL 7, the optimizer has gotten increasingly smarter about
caching/reusing individual statement plans, closing the performance
difference for SPs vs single statements.

Stored procedures could also run WORSE than the individual statements. If
the stored procedure takes parameters which are used in queries, AND the best
plan varies with the parameter, you could get stuck with a plan which is BEST
for someone elses parameters, but many times worse for YOUR parameter.

As a SP creator/user, you should determine whether your stored procedure
plan varies in this way, and seek ways to remediate the problem ( by forcing
the SP to recompile, by breaking the larger SP up in to smaller SPs, by
introducing dymaic SQL into the SP... there are many ways.)

It is still a good idea to encapsulate logical pieces of work into SPs, and
releive the programmers from duplicating work in multiple parts of several
different programs ( code re-use), and you may get better performance, but
it's no guarantee.

Hope this helps...

Show quoteHide quote

You may found very useful information in "Batch Compilation, Recompilation,
and Plan Caching Issues in SQL Server 2005" whitepaper on Microsoft TechNet.
Quick'n'dirty in two words:

Parametrized queries perform better than ad hoc queries in subsequent
executions because their execution plans are cached differently. For
example, this query

select * from SomeTable where SomeColumn between X and Y

may be cached 'as is' and will benefit from subsequent executions only if
newly submitted query will exactly match the cached one (including X and Y
values). In some cases (usually when query execution plan doesn't depend on
literal values) optimizer parametrizes query so it will be cached as

(@1 datatype, @2 datatype)select * from SomeTable where SomeColumn between
@1 and @2

and, when newly submitted query will have form

select * from SomeTable where SomeColumn between Q and Z

it will be executed faster because there will be no time for plan building
and compilation.
Parametrization may be forced by executing query as stored proc (they are
always cached and seldom get recompiled if not created with 'with
recompile') or by using sp_executesql to execute query (as ADO.NET does, for
example). There are also several improvements in parametrization in SQL
Server 2005 in compare with SQL Server 2000 - see BOL for details.

Batches are cached as well. But in some cases execution plan for entire
batch becomes invalid during execution, which leads to recompilation of
entire batch (SQL Server 2000) or just statement causing invalidation (SQL
Server 2005). Because stored proc is named batch, this is true for it too.


Show quoteHide quote

T-SQL: How to loop through the resultset from another procedure?
Count Occurances in a column
Who has the highest bid?
Need Help Please...
How to compare strings for equality in Transact SQL