Re: Newbie: Stored Procedures and Or Datasets ?

From: Marc Scheuner [MVP ADSI] (m.scheuner_at_inova.SPAMBEGONE.ch)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 10:04:28 +0200

>Stored procedures are not pre-compiled nor pre-parsed. Compiling is done at runtime when
>the proc is executed for the first time, the execution plan is then cached
>and thrown out after a while

Sorry, I have mis-interpreted some docs I've been reading - you're
right, if a stored proc is used very sparingly, it's execution plan
can be phased out of memory after a while, thus not really giving it a
whole lot of advantages (at first sight) over a standard ad-hoc T-SQL
query.

While researching this, I've come across a really good article, which
I'd like to recommend to all interested parties - it's very
instructive, and a very good read:

Greetings, Marc

------------------------------------------------------------------------------------------------------------------------
An Evaluation of Stored Procedures for the .NET Developer
Christa Carpentiere, Microsoft Corporation
March 2004

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/storedprocsnetdev2.asp

------------------------------------------------------------------------------------------------------------------------

Some key paragraphs from this article:

Performance

The cached execution plan used to give stored procedures a performance
advantage over queries. However, for the last couple of versions of
SQL Server, execution plans are cached for all T-SQL batches,
regardless of whether or not they are in a stored procedure.
Therefore, performance based on this feature is no longer a selling
point for stored procedures. Any T-SQL batch with static syntax that
is submitted frequently enough to prevent its execution plan from
aging out of memory will receive identical performance benefits. The
"static" part is key; any change, even something as insignificant as
the addition of a comment, will prevent matching with a cached plan
and thereby prevent plan re-use.

However, stored procedures can still provide performance benefits
where they can be used to reduce network traffic. You only have to
send the EXECUTE stored_proc_name statement over the wire instead of a
whole T-SQL routine, which can be pretty extensive for complex
operations. A well-designed stored procedure can reduce many round
trips between the client and the server to a single call.

Additionally, using stored procedures allows you to enhance execution
plan re-use, and thereby improve performance, by using remote
procedure calls (RPCs) to process the stored procedure on the server.
When you use a SqlCommand.CommandType of StoredProcedure, the stored
procedure is executed via RPC. The way RPC marshals parameters and
calls the procedure on the server side makes it easier for the engine
to find the matching execution plan and simply plug in the updated
parameter values.

Maintainability and Abstraction

The second potential benefit to consider is maintainability. In a
perfect world, your database schema would never change and your
business rules would never get modified, but in the real world these
things happen. That being the case, it may be easier for you if you
can modify a stored procedure to include data from the new X, Y, and Z
tables that have been added to support that new sales initiative,
instead of changing that information somewhere in your application
code. Changing it in the stored procedure makes the update transparent
to the application - you still return the same sales information, even
though the internal implementation of the stored procedure has
changed. Updating the stored procedure will usually take less time and
effort than changing, testing, and re-deploying your assembly.

Also, by abstracting the implementation and keeping this code in a
stored procedure, any application that needs access to the data can
get it in a uniform manner. You don't have to maintain the same code
in multiple places, and your users get consistent information.

Another maintainability benefit of storing your T-SQL in stored
procedures is better version control. You can version control the
scripts that create and modify your stored procedures, just as you can
any other source code module. By using Microsoft Visual SourceSafeŽ or
some other source control tool, you can easily revert to or reference
old versions of the stored procedures.

Security

A final reason you may want to consider using stored procedures is the
way they can be used to enhance security.

In terms of regulating user access to information, they can provide
access to specific data by allowing users permissions on the stored
procedure, but not the underlying tables. You can think of stored
procedures as similar to SQL Server views (if you are familiar with
those), except the stored procedure accepts input from the user to
dynamically change the data displayed.

Stored procedures can also help you out with code security. They can
protect against some kinds of SQL injection attacks - primarily those
that use an operator such as AND or OR to append commands onto a valid
input parameter value. Stored procedures also hide the implementation
of business rules in the chance that your application is compromised.
That can be important for businesses where this type of information is
considered intellectual property.

================================================================
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch

Relevant Pages

  • Re: Confused about proc vs. dynamic SQL vs LINQ
    ... stored procedures are going to be faster than ... performance from dynamic sql (sql that you generate on the client and ... as well as if the queries are the same and a number ... another execution plan would be faster because statistics changed. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Performance Tool Question
    ... tool has a performance-optimizer ... Open a new window in query analyzer and type execute myprocedurename (type ... The execution plan will show all scans to be performed and will give hints ... > I am looking for a tool that will help optimize TSQL and Stored Procedures ...
    (microsoft.public.sqlserver.server)
  • Re: Stored proc recompilation
    ... There are other benefits to using stored procedures than just reusing the ... you have no choice but to generate a new execution plan each ... Jacco Schalkwijk ... "Chuck Urwiler" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: sql query or stored procudre which one is giving best performance in asp.net
    ... Stored procedures are better for performance. ... When you create one, SQL ... When you execute a SQL query ... direct the database needs to create the execution plan each time. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Business Rules & Referential Integrity
    ... With regards to my stored procedures and the inherent "White Elephant", ... fully appreciate your comments and will use Declared Referential Integrity ... in SQL Server and capture any named constraint errors/exceptions this way. ... >> about application design and implementation using DotNet. ...
    (microsoft.public.dotnet.distributed_apps)