Re: Prepared Statement vs. Stored Procedure

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Baisong Wei[MSFT] (v-baiwei_at_online.microsoft.com)
Date: 03/16/04 

Date: Tue, 16 Mar 2004 06:22:48 GMT

Hi, Christopher ,

Thanks for Gregory's good link and Kevin's help. Here I just want to add
some informtion of the performace advantage of stored procedure in SQL
Server side.

SQL Server creates compiled plans for ad-hoc SQL command batches, stored
procecures and triggers. A compiled plan for a stored procedure or trigger
is samved in cache. An ad-hoc SQL statement may or may not have its plan
saved in cache. In adddition, views and Foreign Key Constraints are
normalized and their normalized plans can be saved in the cache. The point
of cache is that the plan, either before or after optimization, is
available for later re-use, saving processor time in doing so. When a query
is ready to be processed ,the SQL Manager looks it up in chache,. If there
is no plan for the query in cache, it must be compiled.

A query plan for a n ad-hoc statement that is cheap to compile will not
typically be saved in chache. It is cheaper to recompile these plans than
to push more expensive plans or data pages out of the memory. You may see
that if two identical statment are executed within secondes of each other,
an ad-hoc plan is reused. However, typically ,plans that are cheap to
recompile will be recompiled even if an identical plan is in cache.

We suggest you use stored procedures whenever possible. Besides giving you
the benefits of precompilation, they minimize network traffic by reducing
the text that needs to be sent from your application to SQL Server, and
they provide a security mechanism to control who can access data and under
what conditions. As we know that when the stored procedure is first run, an
execution plan of it is kept in the cache, later there might be some reason
that it will be re-compiled, but this advantage of precompilation will
enhence the performance.

There are also some articles for you for reference:
Effects of Stored Procedures on Application Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/od
p_tun_1a_6x45.asp

ADO Performance Best Practices
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/
html/bestprac.asp

I hope these articles helpful for your developing. Thanks

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Relevant Pages

  • Re: Stored Procedures
    ... statements compiled into a single execution plan." ... AFAIK is there no difference in SQL by a stored sproc and a dynamic sproc. ... Or how would you define a extended stored procedure written in C++ ( ...
    (microsoft.public.dotnet.languages.vb)
  • Re: What exactly is a query plan (or a SQL plan for that matter)
    ... I read a little more in "Inside SQL Server" about how the query optimizer ... it is at a much lower level, more 'requests' will benefit from the cache. ... > us the plan cache and what causes a new plan to be created. ...
    (microsoft.public.sqlserver.programming)
  • Re: Store procedure vs Direct statement ???
    ... A stored procedure generates an actual execution plan, but dynamic sql doesn't (at least last time I checked it didn't --- checking from within query analyzer) or at least it is very basic. ...
    (microsoft.public.dotnet.framework.adonet)
  • stored procedure occassionally running 10x slower???
    ... But there are some "anomalies" happening when this stored procedure is ... Using SQL Profiler, ... anomalies and normal execution). ... I tried to "capture" the execution plan for that particular query, ...
    (microsoft.public.sqlserver.programming)
  • Stored Procedure occasionally running 10x slower???
    ... But there are some "anomalies" happening when this stored procedure is ... Using SQL Profiler, ... anomalies and normal execution). ... I tried to "capture" the execution plan for that particular query, ...
    (microsoft.public.sqlserver.programming)