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
- 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! - 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. - 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.
Comments