Why I Avoid Stored Procedures (And You Should Too)

Subtitle:  Object Relational Mappers are the New Standard Practice for Application Development

Okay – DBA’s are already upset after reading the title.  I understand.  Stored procedures have been the standard practice of most professional software developers for more than a decade.  But like my recommendations on Flash, there are newer and better options than some old tried-and-true technologies.

I’ll have you know that I’m not an extremist on this subject.  I have personally worked on many applications that use stored procedures, and I have written plenty of them myself to follow a client’s “established best practices.”  Stored procedures still have a place in some scenarios.  However, given a choice, I avoid stored procedures as often as possible.

Why Do Developers Use Stored Procedures?

Traditionally, there have been a lot of reasons given for the use of stored procedures.  The most popular arguments include:

  • Performance – The query plan for stored procedures is compiled in SQL Server so that subsequent requests can run faster.  Also, a single stored procedure can perform multiple SQL commands, reducing traffic between an application and the database server.
  • Security – Stored procedures are well defined database objects that can be locked down with security measures.  Use of typed parameters can help prevent SQL injection attacks.
  • Code Re-Use – Database queries can be written once and re-used multiple times without writing the same SQL commands over and over again.
  • Business Logic Encapsulation – The database can house a lot of business logic so that the brain of your application is kept “neatly in one place.”

Ten years ago, I was completely on-board with this train of thought.  But around 2005, I started working with Object Relational Mappers (ORMs) and it changed my whole thinking about stored procedures.

What is an Object Relational Mapper?

An ORM is a code-based tool for application developers to work with databases.  The purpose of an ORM is to create a code representation of the data model.  Once you have this code in place you can access your database without writing a line of SQL code.  No stored procedures (or ad-hoc SQL) needed.  A lengthy study is beyond the scope of this article, but you can read about ORMs on Wikipedia.

Oh CRUD

ORMs are very efficient at Create [INSERT], Read [SELECT], Update and Delete (CRUD) operations.  Usually at least 90% of SQL commands used in an application are simple CRUD operations.  ORMs automatically write parameterized queries for you so that you never have to spend time writing CRUD from scratch.

Writing stored procedures for simple operations is a waste of time and it muddies up the database.  I cringe when I see a list of 500 stored procedures in a single database.  I cringe further when I discover that most of the stored procedures contain mind-numbingly simple queries like “select * from [tablename] where [col1]=@param” and “update [tablename] set [col 1]=@param where [id]=@userId”.

It drives me batty when I know that someone took the time to write all of those stored procedures, then an application developer manually wrote code to call the stored procedure, convert and pass in parameters, check the result and pass back a DataSet.  I commonly see ~20 lines of C# or VB application code to call a simple one-line stored procedure.

With LINQ, I can accomplish more functionality with less code.  By writing “var result = context.TableName.Where(tn => tn.UserId == userId);” the data context writes a parameterized SQL query, the query is executed, the result is returned and translated into a strongly-typed set of objects (much better than a DataSet).  I get more benefit out of one line of LINQ code than a stored procedure coupled with 20 lines of code to call it.

Good N-Tier Application Design

N-Tier Application DiagramIf you are familiar with n-tier (or multi-tier) application design, you know that the business layer belongs in the middle of your application stack.  In other words, business logic should be compiled application code that is testable via automated unit tests.

Your data access layer should be comprised of an ORM and basic repository methodology.  Some people would throw stored procedures into the diagram as a second data access layer, but this is unnecessary and (IMHO) incorrect.

A database should be limited to the role of a “persistence layer” – a technology agnostic storage mechanism.  When business logic is scattered through stored procedures, database triggers and application code, the n-tier model is broken.

Since T-SQL is technology-specific, your stored procedures would need to be re-written in order to migrate to MySQL, Oracle or another database.  If you avoid stored procedures and use an ORM like NHibernate, moving to a new database architecture is basically as simple as changing a connection string (okay, maybe not quite that simple in the real world).

Benefits of Object Relational Mappers

Every ORM is different, some with more benefits than others.  My personal favorite is still PLINQ for LINQ-to-SQL, and there are versions for Entity Framework and NHibernate now.

These are the most common benefits to look for in a good ORM:

  • Less Hand-Written Code – There is substantial time and cost savings when using a good ORM versus hand-writing stored procedures and stored procedure calls.
  • Rapid Application Development – Developers are all under pressure to build more features with less time.  Tools like PLINQO offer code generation with intelligent re-generation.  This is extremely important because PLINQO can update your data layer to match the database with two clicks of a mouse.  If you’ve been using stored procedures and your data model changes, you’re in for a lot of fun tracking down and fixing all of the references.
  • Maintainability & Refactoring – The foundational principle of agile development is the expectation that applications will need to be changed in the future.  The data model and business rules will change.  If you use a good ORM, these changes can be handled quickly and easily with a refactoring tool (like ReSharper).  Compiling your application is often enough to verify that your changes were done properly.  But if you used stored procedures as the basis of your data access and/or business logic layer, you will be frightened to make any changes for fear of breaking something.  This “stored procedure paralysis” leads to hacks and workarounds instead of properly addressing new requirements.  I just saw this happen a few weeks ago on a client’s project.
  • Performance – A LINQ data context uses deferred execution for selects, and it batches record updates and deletes in a single database round-trip on Submit().  Good ORMs offer additional performance enhancements like caching, futures and batch operations (select, update and delete).  These can have a dramatic impact on application speed.
  • Proper N-Tier Design – Your database should merely be a “persistence layer.”  It should not contain the business logic of your application.  Using an ORM helps you architect a solution where your business logic resides in strongly typed managed code (instead of being scattered through stored procedures and triggers).  This keeps the separation of concerns more clear, avoiding a broken or inverted n-tier structure.
  • Business Logic Encapsulation – The data entities that are generated by your ORM are effectively business objects.  Instead of working with “dumb DataSets,” these entities can enforce business rules that can interact with your presentation layer and data layer.

Common Objections to Object Relational Mappers

  • “Stored procedures are faster.” – In speed tests that I’ve seen, parameterized queries perform just as well as stored procedures (sometimes better).  From what I’ve read, SQL Server does cache the execution plan of parameterized queries.
  • “LINQ syntax is not as flexible as T-SQL.” – This is true but misleading.  95% of T-SQL queries I’ve seen can be accomplished easily in LINQ syntax (even aggregate functions).  If you look at some sample LINQ commands you may be surprised what is possible.  Also, you can execute custom SQL or call a stored procedure from LINQ and return the results in a strongly-typed object.
  • “ORMs add too much overhead.” – Compared to what?  If you’re comparing with straight ADO.NET and a DataReader, there is a slight performance hit.  If you’re comparing with explicit hydration of custom business objects, there is likely a performance gain.  Plus, good use of caching, futures and batch operations more than make up for any overhead.  Remember that data access speed is not the only measure of good application design.
  • “ORMs pull unnecessary data.” – By default most ORMs will return every column in the table(s) that you requested.  However, most ORMs support projection queries where you can limit the columns returned.  The results of your projection are returned into a strongly typed collection.
  • “Stored procedures still provide better security.” – If you need to give data access to someone other than your development team, you should use a web service.  As for SQL injection attacks, parameterized queries prevent this problem too.
  • “ORMs should only be used to call stored procedures.” – Most ORMs can call stored procedures, so these two technologies are not mutually exclusive.  However, calling stored procedures from an ORM negates many of the benefits of ORM technology and design patterns.  It adds work and complexity while reducing benefits.

When Should I Use Stored Procedures?

I see several cases where stored procedures are still acceptable:

  • You’re doing data warehousing / ETL / data aggregation.  An ORM is usually not the right tool for this kind of bulk data management.  You should consider a tool like SQL Server Integration Services (SSIS), BizTalk, or another dedicated ETL tool.
  • You’re working on a legacy application where everything is already built using stored procedures.  Sometimes you have to go with the flow.  Sometimes adding a second tool is worse than supporting the wrong tool.
  • You need to feed data to SQL Server Reporting Services (SSRS).  Strangely, the CLR integration for SQL Server does not have LINQ support, so T-SQL is your only option with SSRS.  But frankly, SSRS isn’t always the best choice either.  There are a lot of good third party tools for report generation to HTML, PDF and Excel that can interface with your ORM.  Since SSRS is not available in the Express Edition of SQL Server (and definitely not available in MySQL or Oracle) you are limiting your deployment options again.
  • Your query absolutely cannot be performed with LINQ syntax.  This is rare, but does happen occasionally.  Thankfully, stored procedures or custom SQL can be called from a LINQ data context.
  • You need to perform a lot of complex SQL statements from a small set of input.  Although there are lots of ways to handle batch operations with PLINQO, sometimes “you gotta do what you gotta do” to address a performance issue.
  • You need to push thousands of records to SQL in a single statement.  This is most efficient through table-valued stored procedure parameters in SQL Server.

I hope you find this information useful.  Happy coding!

Kick it on DotNetKicks.com [Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]
About these ads

16 thoughts on “Why I Avoid Stored Procedures (And You Should Too)

  1. SalukiJim says:

    LOL on the “you should do data access via a web service”. Web services do NOT perform fast enough for our application, which has a short-response-time SLA.

    • kevinlawry says:

      Okay – Allow me to clarify my perspective in this article. I typically work on web applications, desktop applications and/or web services. The approach I described is not right for every development scenario, but it is generally the right approach for the use cases that I encounter the most. Web services (or even ORMs) would not be the best recommendation for data warehousing / ETL / data aggregation scenarios.

      My example about security was in providing data access to third parties. Generally, this should be done with some form of web service, or at least an export process. I’d never give my customers direct access to my company’s SQL server. I assume you wouldn’t either, but I don’t have enough information about your use case to comment on your approach.

    • If that’s the only thing you have to say, why bring it in? There isn’t a one way solution for anything, there are always multiple solutions and everything has trade-offs. Webservices can be extremely fast, maybe you’re just doing it wrong.

      What I didn’t get from the security part is that people like to set authorization on sprocs. If that’s the case, use views. Done.

    • kevinlawry says:

      True – I actually mentioned this in the article. ORMs and stored procedures aren’t necessarily mutually exclusive (it’s not always “one or the other” but sometimes “both”). However, you lose the greatest benefits of ORMs when your ORM is calling stored procedures for every database call.

      1. (1) The CRUD isn’t handled for you because you’re still writing all of the SQL queries as stored procedures.
      2. (2) You spend time hand editing SQL code instead of letting a code generator write 80% of your data access layer automatically.
      3. (3) If your data model changes, you’re still chasing down text references in SQL instead of using a refactoring tool that can find and fix strongly-typed references quickly.
      4. (4) You don’t get to take advantage of LINQ syntax or query chaining.
      5. (5) You also miss out on nice tools like the LinqDataSource that handles pagination and filtering without writing a line of code.

      Point in case – for most applications, ORMs should be the foundation of the data access layer. Stored procedures are an add-on for the rare cases when an ORM can’t do the job, or can’t do it fast enough.

  2. Yeah, try doing a Full Text Search using an ORM…
    Try to adjust a simple error of the data access already deployed in production. You will need to recompile your … ORM client code instead of just editing a sproc.

    Furthermore try doing any update or delete on several rows at once. (no, I don’t see an overlap with the ETL bulletpoint above).

    • kevinlawry says:

      Hi Andrei – thanks for your reply. Please allow me to address some common misconceptions:

      • Full Text Search – Actually with PLINQO I can execute a full text search two ways. (1) I can pass any text SQL command to the ORM. The result will be transformed into a strongly typed result set. (2) I can call a stored procedure to handle the full text search. Again, the result will be a strongly typed result set, plus PLINQO will perform code generation to write the calling method and the strongly typed result data object [no hand coding needed]. Remember, I said that stored procedures should be avoided as the rule, but they can still be used as the exception to the rule when it makes sense.
      • Deployment – Compiling an application and deploying to a web server is no more difficult than updating a stored procedure. This is especially true if you are following proper deployment procedures and not just tinkering with your production database. Furthermore, using an ORM makes it less likely that you will deploy an error in the first place, especially if you use good development practices like unit testing.
      • Batch Update and Delete – A number of ORM systems support batch update and delete operations. Some good ones also support multiple record sets and query futures.
  3. About the LINQ vs Stored Proc. SQL Server caches the execution plan for both, there’s no difference in speed there. However, every time you add an “IF” statement in a stored procedure and the outcome of that IF statement differs, SQL throws away the execution plan and regenerates it. And I already didn’t like IF statements in sprocs! :)

  4. We use stored procs for all data modifications, but sql for queries. The stored procs are more secure because the account used to connect to the database only requires execute permissions on the stored procs and this eliminates the chance a developer or hacker could write their own custom data modification SQL. Another advantage of stored procs is that they provide a layer of abstraction between the code and the database. Finally, we generate all of our stored procs, so no one is bothered by the tedium of their use.

    • kevinlawry says:

      Hi Rob – Thanks for your reply. I understand all of the arguments for the use of stored procedures. I expect that most of the responses I receive supporting stored procedures will come from DBAs and/or developers who have been writing stored procs for 10+ years as the “standard industry convention.” I used to be on your side of the fence. The point of my article is that this convention is changing, and developers should “get with the times.” Back in the day, I programmed with Classic ASP, then I moved on to ASP.NET WebForms, then MVC, then MVVM design practices (example: Knockout JS). As newer better technologies and design practices emerge, I try to keep moving forward and growing instead of stagnating in the old familiar way of doing things.

      For web, desktop, web service and mobile applications, ORMs are usually the best technology choice. ORMs can replace 90%+ of the stored procedures written resulting in an application that is (1) more easily maintainable (2) less fragile in refactoring and (3) helps enforce clear separation of concerns of application layers. Business logic belongs in strongly typed managed code, not in brittle plain-text untyped T-SQL.

      In response to your concerns:

      • Developer Access – This is a whole other topic that I should write about. You don’t need to give everyone on your development team direct access to your live production database (or any shared database for that matter). A good development practice is for each developer to have their own local database instead of everyone connecting to one central database server. The local database would be cleansed of any sensitive data (SSNs, credit card #s) so it could be distributed to the development team. Developers should submit SQL change scripts via a source code repository. SQL change scripts should only be applied to a production (or staging) database server by a designated “build master” (generally the technical lead). Following this practice completely eliminates access by “untrusted developers.”
      • Hacker Access – Most ORMs use parameterized queries which are essentially “dynamic stored procedures.” These have the same exact SQL injection protection that a stored procedure does.
      • Abstraction – Abstraction really doesn’t belong in the Persisence Layer of an application. Your main abstractions (domain model) should be in the Business Layer.
      • Code Generation – I know there are code generators that will develop stored procedures to handle CRUD for all of your tables. However, (1) this leaves an unorganized mess of stored procedures in the database [sure, there is a naming convention, but no way to organize into folders/groups] and (2) 100% of these CRUD stored procedures would be unnecessary with a good ORM. Stored procedure code generation leaves a mess of extra brittle code to maintain. The real tedium comes when your data model changes and you have to change all of those stored procedures. With managed code and a refactoring tool (ReSharper) this is fast and easy. With stored procedures, this is a big effort with higher chance of mistakes.
      • Hi Kevin, thanks for taking the time to reply, but I continue to disagree in my case. I think each development environment has a different definition of what is best, and I would even agree that at the moment, perhaps it would be best to use SQL in 70% of environments, and stored procs in 30%. I would certainly never claim that any approach is best 100% of the time.
        I agree with your point that a “web” hacker, going through your application, is equally limited by stored procs as by a strong ORM. But if the hacker can interact with your desktop code (look at the Gray Wolf hacking tool), then they can rewrite your source code on the fly in a compiled .Net application. Also, if the hacker can connect to your database without going through your application, perhaps with SQL Enterprise Manager or an Access front end, then they have more power if not forced to use stored procedures.
        I think abstractions offer benefits in every layer. I would not rewrite an app to add an abstraction layer at this level, but I also would not begin dismantling such an abstraction layer if it exists. I think it is difficult to identify when an abstraction layer provides value until the need arises.
        Our stored procs are probably less brittle than the ones you are used to working on. If we change table structures the stored procs will automatically be re-genned to reflect that, as will the code that uses them. We don’t even need to take the time to use a refactoring tool, so, in OUR case, we don’t have any code generation penalties. In fact, we find it valuable if we can modify only the stored proc at a client to solve a problem, as that update is easier to deploy at the client that recompiling, versioning, and redistributing code.
        I’m not trying to produce strong arguments against using SQL instead of stored procedures, I’m just saying that there is not a single solution that is always best in every solution. Also, as you alluded to, everything keeps evolving, so it is possible that the wheel turns again and we find some great benefits of using stored procs over SQL.

      • kevinlawry says:

        Hey Rob – Thanks for writing again. It’s always interesting to hear from other professional software developers. About 90% of the apps I develop are web-based (ASP.NET), so that experience guides most of my software architecture biases. If I were developing a desktop (or mobile) app [which I have certainly done], I would use web services or WCF for all communication (side note: PLINQO can generate Data Services and WCF in seconds). I would further use SSL to encrypt all of that communication. In really sensitive cases (if the app is for internal use only), I would put the web service under a VPN.

        I personally avoid allowing any application to communicate directly with SQL Server unless it is a web application. Otherwise an employee can try to get direct access to your SQL Server by figuring out the connection string and/or doing packet sniffing. With the web services approach, the database server can be firewalled with no direct access from workstations. Yes, a web service adds a little bit of overhead, but it also adds (1) stronger security (2) a single place to fix query or business logic bugs [you can fix the web service as opposed to re-deploying the desktop app] (3) a reusable data service layer that can be consumed by multiple client types [desktop, mobile, web app server-side, JavaScript/AJAX, oData] (4) the ability to implement caching and performance enhancements at the web service level and (5) the option to implement redundancy via web farms.

        For other readers’ sake, I did want to clarify that my article is about stored procs versus object relational mappers, not stored procs versus ad-hoc SQL strings. Plain-text (ad-hoc) SQL commands have the highest risk of SQL injection attacks. If I did not have a good ORM available to me, I would always choose stored procs over plain-text SQL commands. However, ORMs use parameterized SQL commands which are essentially “dynamic stored procedures” which offer the same SQL injection protection as stored procedures.

  5. Cade Roux says:

    ORMs used with tables or views require you to have SELECT/UPDATE permissions on all those, and interferes with being able to refactor the database design without affecting the interface to the application. Like other parts of software development, a layer of stored procedures allows you to control security, data-hiding and abstraction and is a responsible way to build decoupled and properly designed systems. I’m not opposed to ORMs but am opposed to abandoning all your layered software principles as soon as you see a database and simply treating the database as a dumb table repository. For an app tightly coupled to a database schema, it’s fine (but I would question why you choose a RDBMS if you aren’t going to use any actual R, B, M, or S features). But for a system, perhaps with multiple apps, with a full lifecycle, it’s irresponsible.

    Why would you abandon all your system design principles as soon as you hit some arbitrary layer boundary? It’s like saying, “we’ll hit this web service and build a great system around it, but for the web service component we are going to just internally abandon those design principles we hold so dear and write it in procedural COBOL because we just feel like that’s easier”

    • kevinlawry says:

      Cade – Thanks for your reply. If you read my article closely you will find that I am advocating better system design principles by enforcing clear separation of concerns. In my experience, it is best to keep business logic in a strongly-typed managed-code Business Logic Layer. The BLL belongs in the middle of the application.

      I know stored procedures can be written as “dumb queries” (CRUD) without any business logic, but such CRUD-based stored procedures are unnecessary when you use a good ORM. In other words, business logic does not belong in the database, and if your stored procedures don’t have any business logic, then they are just doing simple CRUD that can be handled with an ORM, therefore those stored procedures reflect wasted effort and extra future maintenance. As for code re-use, this is done by separating your Business Logic Layer into a separate library that can be consumed by multiple projects (web app, desktop app, web service, etc).

      By the way, good ORMs understand and take advantage of database relationships (foreign keys), primary keys, constraints and other data model attributes. They can enforce size limits on varchar/nvarchar data types so that users can be prompted about the issue instead of sending oversized input to the stored proc and throwing an error. Using an ORM does not throw away the benefits of an RDBMS; rather it supports and enforces the rules set forth by your RDBMS.

  6. Joe says:

    stored procs also avoid you having to give direct table access to users. LINQ/ORM/or whatever that doesn’t use procs will require direct table level permissions, I have yet to see any anti-proc blogs address this. Table level access is where SQL injection comes in and it allows users to bypass the app and directly access tables (assuming they know how, some are very savvy).

    • kevinlawry says:

      Hi Joe – Thanks for your response. Most ORMs use parameterized queries for all data access. These are essentially “dynamic stored procedures” where all of the parameters obey data type rules. If the parameter expects an integer and the person attempted a SQL injection string, it would be rejected by SQL server. In reality, it wouldn’t even get that far in the pipeline – the ORM wouldn’t allow such a value to be passed to an integer parameter. Even in the case of a string parameter, SQL would only use the parameter for string comparison. The ORM will properly escape the string parameter, so it is not possible to break the SQL syntax to start a malicious command.

      Honestly, a stored procedure can be more vulnerable to SQL injection than an ORM. Some stored procedures use dynamic SQL commands (exec @someSqlCommand), and this is very susceptible to SQL injection attacks. With an ORM, no plain text SQL commands are executed (string input is only passed via parameters, not appended to a SQL command). In short, there are lots of ways to prevent SQL injection, and an ORM is actually your ally in this effort, not a hindrance. Please read my response to Rob Kraft regarding database security.

Comments are closed.