All

Can anyone advise me the performance advantages of using Stored Procedures
vs. a normal SQL statement.

Thanks

D> Can anyone advise me the performance advantages of using Stored
D> Procedures vs. a normal SQL statement.

None, unless the version of sql server is less than 7.


Vadim Rapp

do you mean inside SQL (like in query analyzer)
or from a client (like VB)
?


David wrote:
Show quoteHide quote

Hi

Check out the answer to the question "Are Stored Procedures Being Used
Whenever Possible?" in

http://www.sql-server-performance.com/sql_server_performance_audit8.asp

John

Show quoteHide quote

On Thu, 22 Dec 2005 17:04:02 -0800, "David"
<Da***@discussions.microsoft.com> wrote:
As a coding thing, it separates the SQL from the caller's source,
let's a DBA make changes if some part of the schema changes without
affecting the source, provides a single point of change, etc.  Sure,
you can also do that in the source C# or Java or whatnot, but it's
still a handy feature sometimes.  It allows the SQL to be developed,
tested, and encapsulated separately.

Since compilation plans are cached whether you us dynamic SQL or an
SP, the difference between the two is not black and white, but again,
as the code gets large, there are increasing benefits to invoking it
via SP.  Even the parse, much less the compilation/optimization, is
very slow (does it have to be, not necessarily, but it is).

In fact, there are sometimes advantages to using dynamic SQL, in that
there is always an issue with SPs about what plan to use depending on
the parameters for a specific call.  These are better analyzed by a
complete parse with constants, I think (does anyone know?) than with
the "parameter sniffing" that SQLServer does with an SP.  But since
SQLServer will try to use a cached plan, well, the SQL may not be all
that dynamic after all, may be internally equivalent to an SP.

And then there are prepared statements, an old-style middle ground.

I recommend using SPs pretty much wherever possible, in general it
avoids trouble, it seems to me.

HTH,

J.

GetDate() in UDF
Why query optimizer does not select the index?
Popuate field with value from last row
Finding the right record
CHARINDEX in CASE Within SELECT Statement