Stored Procedures Pros & Cons

Using a stored procedures (SP) data access layer is a totally viable approach (see [1]). While I do not devalue SP’s as the “assembly language of databases”, I personally dislike the language break and generally use stored procedure in special cases only:

  • Optimize performance by saving roundtrips.
  • Needing specific RDBMS features not exposed by the ADO.NET provider.
    The ODP ADO.NET provider even supports Oracle Change Nofitfications and Advanced Queuing.
  • Coding very complex SQL.
  • Creating data centric fat-server applications.

I  generally prefer developers working on complete use cases, implementing them vertically through all layers and have layer specialists for UI and database define standards and perform reviews for quality. For end user centric apps this enforces focusing on user experience and delivering applications with a benefit to users. For background services developers get a feeling for the complete service from the start. It is easier to follow this approach when using one language (family) throughout all layers.
I have no experience with

Using them might be an alternative to SP’s written in T-SQL or PL/SQL.

Some aspects about using stored procedures:

  • SP’s are not generally substantially faster than using dynamic SQL from ADO.NET.(*) The execution plan of parametrized(!) SQLCommands is cached.
  • Some apps may benefit  using Oracle’s result caching in SP’s..
    Oracle offers a “Client Result Cache” feature for caching in the application layer too, supported by ODP ADO.NET (see [ProODP]).
    I am generally not a fan of caching though.
  • SP’s are faster if saving a considerable number of roundtrips.
    • You need to save about four roundtrips to compensate for the XML (de)serializing overhead when using SQL Server OpenXML.
    • Oracle offers PL/SQL associative arrays and bind arrays to pass parameters in bulk.
  • SP’s can reduce network traffic.
  • Minimizing roundtrips and reducing network traffic could become more important again with the limited bandwidth and latency of cloud databases.
  • SP’s are easier to change on-the-fly.
    Don’t forget to source control the changes though.
  • SP’s make it easy to share data access code across (heterogeneous) applications.
  • SP languages like T-SQL or PL/SQL are better suited for coding complex SQL operations.
  • Depending on the development and application requirements, having a DBA completely create, tune, maintain and control a SP layer may or may not be a good approach.

*)  BTW: One can write dynamic SQL in SP’s too.

Books & Articles

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions,
Thomas Kyte

Microsoft SQL Server 2008 Internals (Pro – Developer),
Kalen Delaney

Pro ODP.NET for Oracle Database 11g

About Peter Meinl

Perpetual Traveller, IT Consultant
This entry was posted in Computers and Internet and tagged , , , , . Bookmark the permalink.

2 Responses to Stored Procedures Pros & Cons

  1. Pingback: DB Concurrency Control with .NET – Overview « DuongTienLam's Blog

  2. Pingback: DB Concurrency Control with .NET – Overview – Know your Database System « DuongTienLam's Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s