ifacethoughts

To Use Or Not To Use Stored Procedures?

One of the most ignored features of the modern RDBMSs today is the stored procedures. In fact most of the web applications go to extreme lengths to increase performance but avoid the stored procedures. They are also one of the favorites in theory, but hated in practice. Are they so unnecessary? Are they so useless? Many say so.

Stored procedures give pre-compiled code which can execute requests in batches. This itself can help boost the performance. Of course the improvement will be dependent on the context, but other advantages like more granular access control and more mechanisms for security only promote them. A layer of stored procedures can also act as a single interface for multiple applications or clients.

Then why are they treated as ghosts? I wonder if there are other factors that drive the choice, factors which are more dependent on the programmers than on the merit of stored procedures itself. I have talked to programmers, database people (programmers, administrators), managers, testers about stored procedures and there have been extreme responses regarding stored procedures. Maybe this extremity is a problem itself. But let us dive deeper.

As Greg Jorgensen says, programmers hate RDBMSs and they hate SQL. Most of the programmers are comfortable to imperative programming, and the declarative SQL syntax only creates more friction. While I have met programmers who like the challenge of SQL, I have also found that they are not particularly fond of it. So what do they do? They google for SQL code and more often than not find a ready-made piece of code to solve their problems. This is just too easy.

As Martin Fowler says, we are moving away from using the database to share data across applications, and that nullifies one of the benefits of the stored procedures. But it is more than just liking, the programmability of the database might be against best practices of software programming. Programmers do not want the database to do anything more than manage the data. Database is not part of programming, so using stored procedures does not help much.

I think the problem is that the stored procedures are not yet programmable enough to guarantee inclusion of the entire business logic. And placing only a part of it in there can cause nightmares in maintenance. That leaves the benefit of granular access and security, but does that tilt the cost-benefit balance against the stored procedures?

This affects other decisions like composition of the development or the maintenance team, which tend to be stronger on non-DB programming side. But I believe that stored procedures can be useful in some cases, like in cases when it is possible to clearly separate the data rules and the business rules. In stead of not considering them at all, thoughtfully accepting or rejecting them is a better approach.

I have been part of desktop and network applications that used them. I haven’t seen them in any of the Web applications yet, for various reasons mentioned above. But I find it difficult to accept that stored procedures are useless. Do you use stored procedures?

Discussion [Participate or Link]

  1. Innovations & interesting Ramblings said:

    Yes I do…and definitely they help :)

  2. JohnMcG said:

    The other barrier is human. In order to deploy a stored procedures, developers often need to involve a DBA, even on the development side, whereas developers have more immediate control over embedded SQL.

  3. Steve Campbell said:

    No, I have not used stored procedures directly for business logic in years. I use them for database maintenance tasks though.

    For me, adding further programmability to database servers is pointless. Separation of concerns is a primary linchpin of good software. Why would I want to embed the concerns of my business logic with the storing of data?

  4. Tombo said:

    Yes. I have been able to move some of my embedded SQL statements to stored procedures and have made some nice speed improvements.

  5. Vinod said:

    Stored Procedures are ‘good’ only to have a tight vendor dependency. Most of them are tough to port across databases. Apart from that, having a Object-Oriented view of your business Objects ( as we have in JDO/Hibernate/Entities) is not-so-practical with a generic stored-proc env.

  6. russ said:

    They don’t port from DB to DB, they are a pain to code against, development suddenly is in two places instead of one.

    There are many cons and not many pros. I haven’t profiled the relative performance of stored procedures so I can say how much benefit they give. I’ve used them in the past but when we switched to mySQL some years ago we dropped their use altogether.

  7. Curious HARD said:

    hi all,

    I also have done some work on stored procedures…
    i think there is not any restriction for business logic….

    the reason behind not using stored procedures can be that if you are using hibernate to interact with database… then you may prefer not to use procedures as in case of using procedures your application can be restricted to specific database like procedures created in MySQL will not be able to run in MSSQL Server.. So Your application is bound to use MySQL as database server….

  8. KpyM said:

    Definitely use them.
    Definitely use SP/DAL generator to produce the stored procedures TOGETHER with the DAL for you.
    Never touch the DAL by hand, only touch the stored procedures that need optimization.

    Having a good SP/DAL generator also solves most of the issue with the porting.

  9. Aaron Saray said:

    I’ve used stored procedures from time to time… but its important to know that every situation has its use.

    For example, with a set of high usage duplicate sql statements, possibly use a stored procedure. If its just a quick call, use your programming language.

    The argument that switching to a new database causes friction in these stored procedures… is just inaccurate. No matter what, you still need to change your programming code (probably) to support the new db too…

  10. Beau H said:

    I use stored procedures on occasion, but as others have said there are definitely reasons not to use them for everything. There are three reasons I tend to avoid them: 1) Generally the DBA has to be involved to get them created or modified, which also makes troubleshooting difficult. 2) Stored procedures don’t meld well with any ORM I have used (as others have mentioned). and 3) (and most important to me) I cannot easily stored the code in SVN, which makes migrating an application difficult, and tracking changes impossible.

  11. Jonathan Allen said:

    > Stored procedures give pre-compiled code which can execute requests in batches. This itself can help boost the performance.

    Actually that isn’t true on all databases. In SQL Server specifically, stored procedures are the same speed as parameterized queries.

  12. Opinion: To Use Or Not To Use Stored Procedures? « Rip’s Domain said:

    […] November 15th, 2007 I was browsing around on Reddit today and came across an article that particular struck my interest: To Use Or Not To Use Stored Procedures? […]

  13. Fergus said:

    Keep in mind that when scaling an app, if written well, you can scale by adding
    more app servers. However, depending on your use of stored proc, you might be
    blocked by number of cpus on your db server. Just my 2c.

  14. Glenn said:

    Oracle DBA here. First, not all DBs are equal and some are better then others at some things and vice-versa. Same is true for the procedural language supported by each database. Oracle has PL/SQL, SQL Server has T-SQL, PostgreSQL has Pg/SQL, Py/SQL, and a few others. A general statement asking if you use one or not is not fair unless each respondent identies the database they are refering too.

    Oracle’sPL/SQL (which I am most familair) is quite powerful. There is really not much you can’t do in it anymore INCLUDING building websites (Oracle’s Application Express). At my current company, our main DB is 2 terabytes and involves scrubbing data against other data, GBs againsts tens and hundreds of GBs. I doubt you’ll ever want to do that outside the DB.

    As mentioned above, database portablility is not a good reason. The SQL involved will/should be changed for each database as techniques used for each is different.

    Stored procedures due offer a nice advantage. The database keeps track of the dependencies. Once the database grows in the number of tables and procedures, that becomes very useful when changes are needed.

    As far as scalability (Fergus) – I don’t get it. Your scaling your app servers to hit the database? The database is still fetching all the data but you are now having a bunch of app servers massage the data. sounds like your throwing hardware to fix a design issue.

  15. Abhijit Nadgouda said:

    Thanks for your comments. One of the biggest reasons for skipping stored procedures is non-portability across DBs. I wonder if the one about not suitable to use with ORM is a problem with the ORM itself.

  16. Innovations & interesting Ramblings said:

    When it comes to portability , do you people feel that code written in OOPS languages are portable to other easily??!! We have the same problem there too…Ideally one chooses a database after considerable deliberation and sticks on it for the lifetime of an application..Why shy i keep changing my database frequently? do you do that with your top tier or middle tier layers? Guess not…If you do, then how easy was the porting for you…

    I would use stored procedures, based on the requirements…A business logic that needs more data manipulations might work better wih stored procedures and you avoid the multiple calls to your bootom tier – database…

  17. Gijs van de Nieuwegiessen said:

    Claiming ‘you should!’ or ‘you should not!’ use Stored Procedures is never a valid statement. SP’s are a tool that might help in some situations, ruling them completely out is therefore a bit silly, shouting you should always use them is even sillier.

    I believe that SP’s, triggers and other ‘Database logic’ have a lot of downsides and can be avoided most of the time (all depends on the context of the application you’re building of course).

    My preference goes out to O/R mappers, 99% of the time. Creating SP’s for CRUD operations is (IMHO) a complete waste of time and effort. Furthermore SP’s increase the complexity and decrease the maintainability of most applications.

    A lot of discussion takes place regarding performance when talking about the usage of SP’s. When I read a comment like ‘OR mappers decrease performance’, then ‘Premature optimization is the root of all evil’ crosses my mind.

    Performance never is an issue until it becomes one! Ruling out OR mappers because they ‘decrease’ the performance (Data access wise) isn’t a good argument.

    I’ve experienced O/R mappers are often even faster since you have more control and can do a lot of clever things (Query pattern, Identity map, Snapshots etc etc).

    Apart from that, if it does become a performance problem, you can tune/ use SP’s anyway (for those rare occasions).

  18. Tony Landis said:

    I think Stored Procedures have their place. For example, in the case where there is a huge performance increase for using a SP or there are multiple applications connecting to a database.

  19. Nitish Shukla said:

    As numerous others have said above, it all depends on where and how you intend to use the SPs. I think their respective comments would’ve been much more useful had they also mentioned what kind of program/application they were developing when they made the observations.

    For example, I work on data-intense applications where there is a lot of batch processing but no real time performace is required. In this setup I find that developing and maintaining SPs is much simpler than a complete 3-tier architecture. The developers know as much SQL as they do C and are always eager to get the job done faster, and, of course, we never go for early optimization, preferring to let sleeping dogs lie.

    On the other hand, if the application is a real time service, the ideal architure should be BL heavy. This is where the downside comes in. Now that the developers are very thorough with procedural SQL, they would rather work with less OO designs and get it over with, leaving a lot to be desired. I should mention, wishlists apart, even such application do not fail to come up to the mark.

  20. DeveloperKS said:

    I use stored procedures to provide a better degree of encapsulation in my applications. Example. Say I have an inline SQL statement that retrives my total sales using the SUM(decSaleAmount) if this column name is changed for some reason in the database I have to change this column name in code and recompile the application. Unlike if I have stored proc with the SELECT SUM(decSaleAmount) even if the column name is changed I used the new column name in the stored proc and I dont have to touch my source code.

    This is why I prefer stored procs

  21. On Misusing ORMs | iface thoughts said:

    […] I feel that any programmer who is dealing with an ORM has to understand the relational model being developed. I do not think ORMs are there to shield the programmer from DBs, they are there to help you extend your object modelling in to the relational domain. Though SQL syntax is not a must to work, understanding the underlying concepts of transactions and referential integrity cannot be skipped. Unfortunately, this is not popular with programmers. One side-effect of this also that sometimes ORMs get pitched against some DB capabilities like stored procedures. […]

  22. Stein said:

    Yea I have programmed now for 14 years professionally and used stored procedures in both desktop apps, services/web-services and web-apps. To make a long story short; those so-called “programmers” that does not know how much procedures can help if used properly… Sounds much like .NET vs Java discussion. More feelings and lack of understanding than proper, objective estiment.
    Many religions that works that way :)

  23. Syed Tayyab Ali said:

    I strongly support CRUD stored procedures.
    You can read my opnion on stored procedures.
    http://programming360.blogspot.com/2009/06/some-thoughts-on-stored-procedure.html

  24. Right Tool said:

    There are a lot of tools out there. I don’t clean my dishes with a hammer or build a house with a sponge.

  25. Kapil Dev said:

    Ok, i am also little bit confuse where to use stored procedure or it reliable for future or not,Accoding to me we have to sue SP only when when we need to handle complex data other wise we don’t need to used SP and You know that in future you need to mantain your application again and again so its better to use SP.

  26. fdepijper said:

    There are reasone to use and not to use sprocs.
    The main reason probably is: when building .net or java web-app its easy to generate data entities which represent the tables. These entities can then be used as data-classes in the web-app and with linq or hibernate you can do CRUD’s. The disadvantage is you load way to much data with each database call than you might need.
    Another reason as already mentioned is spreading the logic. For maintenance properties keeping the logic in one place makes life easier (hence in your web-app).
    However this said. Stored procedure do have their use.
    F.i. triggers which perform actions with data you don’t need in your web-app, avoiding sending data back and forth.
    Or when you are able to keep the logic within the sprocs and don’t need the logic in your web-app.
    I believe stored procedures are the future once we are able to get the same logic in them as we now use in the DAO’s of the applications

  27. nick said:

    The purpose of stored procedures is to be used in multiple environments. A webapp is a prime example because you might have a long running task that consumes a stored procedure but is actually running a DTSX task in SSIS. Or you may have to get the same data for a report that might have to do a little more work so you can mimic the sql query to create the report.

    Having all of your data access code in the application does just that, leaves it in the application. To comply with the ‘reusability of code’, one would be up the creek without a paddle in this situation. If you would have created a stored procedure, you could have done more.

Say your thought!

If you want to use HTML you can use these tags: <a>, <em>, <strong>, <abbr>, <code>, <blockquote>. Closing the tags will be appreciated as this site uses valid XHTML.

freshthoughts

contactme

Abhijit Nadgouda
iface Consulting
India
+91 9819820312
My bookmarks

badgesand...

This is the weblog of Abhijit Nadgouda where he writes down his thoughts on software development and related topics. You are invited to subscribe to the feed to stay updated or check out more subscription options. Or you can choose to browse by one of the topics.