Skip to main content

Whether & When to use Stored Procedures

It's a discussion that comes up time after time in training session... when and why to use stored procedures. There are many heated debates posted on the net about this, with people seeming to argue strongly either for or against. Rather than suggest that you should or shouldn't use stored procedures with SQL server, let's look at their merits and pit falls with a view to knowing when to use them.

The biggest misconception about stored procedures is that they offer significant performance advantages over submitted DML queries to the SQL engine. While it is true that the query plan for any stored procedures is precompiled, the SQL engine does a very good job of parameterizing DML queries and caching their plans. The real-world result is that stored procedures don't offer a significant improvement in performance – especially not in a world where disk I/O, no CPU, is the bottle neck. So then – if performance isn't a core criterion for choosing whether or not to use stored procedures, then what is?

Simple: Maintainability and security.

Stored procedures offer a simple means of keeping client applications at arm's-length from your relational design, allowing you to accommodate changes to the storage of data without re-writing and re-deploying client applications. Moreover, stored procedures allow administrators to implement logic to perform custom logic and validity checks on the operations being requested.

Most interestingly, by using SQL 2008's new table types, database administrators can offer stored procedures to developers which enable multiple records to be inserted in a single round-trip to the server. See the this post for an example. With the advent of LINQ and the Entity Framework, the use of stored procedures as a rigid doctrine is becoming less attractive... LINQ DataContext classes allow developers to manipulate table data without writing a single SQL statement, and the queries which LINQ for SQL generates are impressively efficient. The point here is that enforcing the use of stored procedures will significantly slow the progress of a development team using technologies like LINQ or the Entity Framework.

If security and maintainability are your prime concerns, consider offering updatable views (instead of stored procedures) to your development team. Updatable views offer all of the same advantages as stored procedures, and are generally more flexible in the various ways you can use them. Views can also offer better query optimization that stored procedures, because when a "retrieve-data" stored procedure executes, it always returns all columns selected from the underlying table – regardless of whether or not the client application needed all those columns, but with an updatable view the query optimizer can offer performance gains by expanding the view into the query it is being used in, and then determining how to solve the outer query based solely on the columns requested by the client – regardless of how many columns the actual view defines.

If you aren't comfortable with updatable views or giving LINQ direct table access, or have a corporate policy of implementing all data access via stored procedures, that's OK... but there are a couple of common scenarios worth reviewing, where stored procedures are used, but shouldn't be...

When NOT to use stored procedures

  1. Aggregating, moving or manipulating large volumes of table data.
    This is one of the top reasons administrators site for using stored procedures. The problem with using a stored procedure, or even straight DML queries, for manipulating large volumes of data is that it hits your transaction log, and as the volume of data increases, so the performance becomes exponentially worse. The result is a stored procedure which works in a couple of seconds on your test-set of data, but crawls for hours in a production environment.
    Instead: Consider using SSIS – it is very effective at extracting large volumes of data, transforming into new "shapes", and then re-loading it into a destination... hence the term ETL (extract, transform, load). If you're a database administrator who's responsible for these sorts of year-end or month-end jobs, resist the temptation to write stored procedures and instead try SSIS – it's a great tool!
  2. Running queries across linked servers.
    Generally speaking, if you're serious about performance and system stability then you'll avoid using linked servers. Writing queries that joins data from multiple servers usually suggests that your organization needs coherent reports that gather data from disparate line-of-business systems.
    Instead: Rally for the implementation of a simple datamart- a place where data from disparate systems can be viewed and reported on. At the very least, consider creating a new database, using SSIS to populate it with data from the source databases, then running the queries against this new database. With SQL 2008's new change data capture capabilities you can run incremental SSIS data loads frequently without significantly hurting the performance of your source databases.
  3. Implementing complex business rules.
    Stored procedures can implement useful logic to manipulate and marshal data that's going to or from database tables, but that doesn't mean that a stored procedure is the best technology for implementing complex business logic. Generally speaking, the stored procedure is the "last mile" in the persistence layer of any good 3-tier design. Complex business rules are best implemented in high-level tools that deal with information at a business entity level. Stored procedures see data at the persistence level, which makes them a less-than-ideal choice for implementing complex business rules.
    Instead: Consider implementing a good 3-tier design with the business-logic layer implemented in a rich language like VB.NET or C#. Also, ,consider learning to use the new Entity Framework, which helps transform persisted data to and from the business entities it represents.

To keep perspective here, we're not saying that stored procedures are bad – they certainly have their place. Newer technologies like LINQ, Entity Framework, SSIS and updatable views allow you to achieve all of the same benefits (and more) that stored procedures offer, with fewer pitfalls and in significantly shorter development times.


Post a Comment

Popular posts from this blog

Reading Zip files in PowerQuery / M

Being a fan of PowerBI, I recently looked for a way to read zip files directly into the Data Model, and found this blog which showed a usable technique. Inspired by the possibilities revealed in Ken's solution, but frustrated by slow performance, I set out to learn the M language and write a faster alternative.
UnzipContents The result of these efforts is an M function - UnzipContents - that you can paste into any PowerBI / PowerQuery report. It takes the contents of a ZIP file, and returns a list of files contained therein, along with their decompressed data:

If you're not sure how to make this function available in your document, simply:

Open up PowerQuery (either in Excel or in PowerBI)Create a new Blank Query.Open up the Advanced Editor  (found on the View tab in PowerBI).Copy-Paste the above code into the editor, then close the editor.In the properties window, rename the the function to UnzipContents Usage Using the function is fairly straight forward: Choose "New Quer…

Easily Move SQL Tables between Filegroups

Recently during a Data Warehouse project, I had the need to move many tables to a new file group. I didn't like any of the solutions that I found on Google, so decided to create on of my own. The result?

MoveTablesToFilegroupClick here for a nifty stored proc allows you to easily move tables, indexes, heaps and even LOB data to different filegroups without breaking a sweat. To get going, copy-paste the code below into Management Studio, and then run it to create the needed stored procedure.
Hopefully the arguments are self explanatory, but here are some examples:

1. Move all tables, indexes and heaps, from all schemas into the filegroup named SECONDARY:
EXEC dbo.sp_MoveTablesToFileGroup
@SchemaFilter = '%',-- chooses schemas using the LIKE operator
@TableFilter  = '%',-- chooses tables using the LIKE operator
@DataFileGroup = 'SECONDARY',-- The name of the filegroup to move index and in-row data to.
@ClusteredIndexes = 1,-- 1 means "Move all clustered inde…

SQL Server vs Azure Data Warehouse vs Netezza vs Redshift

The Great Database Shoot Out In Jan'17, I teamed up with Marc van der Zon (Data Scientist), to test and compare several database technologies for our organization's future analytics and BI platform. The technologies that made the shortlist were:
SQL Server, because it is the organization's existing BI platform.Azure Data Warehouse, because of its high similarity to SQL Server.Amazon's Redshift, because of its attractive cost, scalability and performance.Netezza, because it is anaffordable on-prem appliance that performs well. Considerations We were primarily looking for the best bang-for-buck option, so price and performance were viewed as more important than how feature complete any particular option was. That said, what we regarded as important included: A shallow learning curve for BI developers (i.e. no need for expert skills in order to get good performance)
Ability to re-use existing code  (i.e. ANSI-92 SQL compatibility)
Time-to-solution.   (i.e. does the platform …