Do I need Stored Procedures? Maybe or Maybe not.

31 07 2008

I have written a couple of posts about 7-8 months back (here and here) on how to call stored procedures from a rails application. After using stored procedures in my app for a while I started to think about whether we really need them. So over a couple of months I discussed it (mainly short discussions about stored procedures not whether to use them or not) with my colleagues, friends, other people  and gave some thought to it. If you are new to stored procedures then here is a sort of textbookish definition.

Stored procedures are available to applications accessing an RDBMS, they are similar to constructs seen in other programming languages and stored in database data dictionary. Stored procedures remove the compilation overhead required for inline queries as they can be pre-compiled, hence they run faster in some cases. However, now most RDBMS, as they evolved, implement statement caches to avoid repetitive compilation of SQL statements.

Why people think we need them?

People, especially with database background or DBAs, are of the view that we should use stored procedures for most of the tasks involving database because they offer following advantages:

  1. Stored procedures separate server side function from the client side, i.e, put all your business logic in stored procedures.
  2. Stored procedures are modular and offer advantages compared to embedded queries buried within the GUI code.
  3. They are faster, secure and maintainable
  4. Reduced network usage between clients and servers

Why people or I think we don’t need them?

  1. If the application in question even has a remote chance of running on more than one RDBMS then I would just kick stored procedures out of the door because the exact implementation of stored procedure varies from one database system to another and I certainly won’t like to tie my app to a particular database.
  2. Having business logic in stored procedures is not only breaking every rule of good programming but also not following the tried and trusted way of architecting n-Tier applications, i.e. separating presentation, business logic and storage/data access. Do not mix storage/data access with logic because stored procedures are just an extension of data access tier. As they say, use database as a very efficient file cabinet. The only concern of a database should be to store data or persistent objects and the business logic should reside in the application code. Database in itself is not an application you are developing, it’s part of the layer/tier that stores data. We use SQL in RDBMS and SQL is simply a data manipulation language hence very limited in its scope. A programmer can do everything in the application code that can be done in a stored procedure but the opposite of this is definitely not possible. Hence, databases are not programmable enough to include all the business logic. If you somehow manage doing that using temp tables, inserting redundant rows into the tables, by spending ‘n’ number of extra hours or other hacks then you are gonna face performance/scalability issues and surely maintenance problems later in the development life cycle, i.e. a nightmarish, incomprehensible & complex POS (piece of shit). These issues will come back persistently to haunt you and your team. Hence, the advantage 1 and part of advantage 3 mentioned above is not valid anymore.
  3. Storing business logic in the stored procedures also leads to more work for testing compared to having them in (Object Relational Mapping) OR / domain model class and stored procedures/triggers make test driven development less productive. Also, if I am using ORM then I do not need to use any SQL in my application code because the ORM will generate optimised queries for me on the fly and will also bring simplification and consistency in the coding of whole team. Hence, advantage 2 and part of advantage 3 mentioned above is not valid any more.
  4. Stored procedures can very easily get out of synch with the application code. For instance, if I have different versions of my app in the source code repository and in case something goes wrong with my latest version then I can very easily switch back to an older version by simply checking out / exporting stuff from the repository. But these facilities do not exist for stored procedures / triggers. It’s troublesome to have business logic residing in stored procedures because they are in the database and once you alter an existing procedure the old one is gone, hence no going back. Now some of you may argue to keep each stored procedure/trigger in some text file and commit it to the repository with the app but that’s again creating additional work for your team which means you are also adding chances of error/mistakes. However, if you do this then you still have to run your stored procedures/triggers with every update that’s again prone to mistake/errors.
  5. When something goes wrong then stored procedures don’t provide meaningful feedback unless you code all the exception handling within the procedure itself.
  6. You can’t pass objects to stored procedures. If you are using an ORM, and you have a class that is mapping to a table then you can simply pass the object of that class and you are good but with stored procedures you have to pass each field or object attribute as parameters and you may end up passing lots of them, may be 20, 30,40 or even more. Hence, you end up writing more code which essentially is more error prone and if you get a ‘bad call’ error then go figure what just happened. That’s just creating one object what if you need to add a record to 4 tables simultaneously using transaction and let’s say each of them have 10 fields then you need to pass 40 parameters. Change is the only constant in life, especially in the business environment.  Lets say, due to some business requirement your database schema changes and you have to add/remove columns to/from those tables. If you are using stored procedures then you atleast need to take these steps to make sure that your application is still working fine:
    • Add new data vaildation rules for CRUD operation.
    • Change the way data presented to users and your form fields where you allow users to create/update records.
    • Add or remove new parameters to the stored procedure calls from your front end
    • Add or remove new parameters to your stored procedure definition
    • Add or remove new parameters to all other stored procedures or triggers from where you are calling your affected stored procedure(s).

    If you are using ORM instead of stored procedures then you need to take these steps to make sure that your application is OK:

    • Add new data vaildation rules for CRUD operation.
    • Change the way data presented to users and your form fields where you allow users to create/update records.

    That’s it and you will also have your whole app properly version controlled so that you can switch back and forth seamlessly. So why do you want to do something that involves more work leading to delays, increased complexity, less productivity and has more chances of error? Hence, stored procedures/triggers are actually a constraint in your system and according to Theory of Constraints; generally, in any system or process there is one constraint at a time. To improve overall system or process performance first find the constraint then improve it or eliminate the constraint entirely. Do I need to say more?

If you think you can use stored procedures to hide business logic,  then you are definitely using stored procedure what it is not good or designed for. I neither like putting SQL queries in my code nor have any problem using stored procedures only if they can offer me/my team/employer some measurable benefits but in my opinion an OR model is much better in terms of developer time, code, productivity and maintenance. Using stored procedures indicates adding another layer, another language, complexity and losing database portability. If I have an application running on ‘n’ number of different clients then it will be much easier for me to keep my application code synchronized and maintainable rather than the stored procedures and triggers. Triggers pose another problem area because application does not know whether they exist hence do not even have a clue whether they have run or not. What if you have some logic in one of your trigger and that itself may be calling another stored procedure(s) (again some logic on important data) and due to one or the other problem trigger didn’t fire, you are screwed because neither you nor your application will know whether the trigger was fired or not unless your application users see some discrepancy in their data or reports, report them back to you and then you run multiple sql queries and check everything manually. How kewl is that? Not for me atleast.

Having said all that, I don’t mean the stored procedures were never useful or the advantages of stored procedures cited by DBAs were never true, yes they were true until 6/7 years ago. But with time, new technologies come into existence, some lose their advantages and some become obsolete. There is no point in sticking to something for which you have other better options available.


Actions

Information

29 responses

1 08 2008
Christian Zachariasen

A very good article. Thank you.

1 08 2008
Subhash

I too, was once using SPs, and now with PHP I’m using only PHP classes.

One thing I find difficult is to explain budding developers about MVC, hiding biz logic, easy backup & recovery, etc.

But still I’m sticking with MVC. Honestly, I no more care about patterns now. Biz and code changes – wherever it is – and in the end whatever we develop today is going to get scrapped in next 1-2 years.

1 08 2008
Jacques Chester

The only concern of a database should be to store data or persistent objects and the business logic should reside in the application code.

The structure of a schema is business logic. The constraints you ask the RDBMS to enforce are business rules. The triggers you might add are business logic.

Database in itself is not an application you are developing, it’s part of the layer/tier that stores data. We use SQL in RDBMS and SQL is simply a data manipulation language hence very limited in its scope.

Have a look at the SQL specs some time and tell me again about limited scope.

A programmer can do everything in the application code that can be done in a stored procedure but the opposite of this is definitely not possible. Hence, databases are not programmable enough to include all the business logic.

This is actually a feature. SQL needs to be able to predictably terminate.

Your POV is quite common. A lot of folk see the RDBMS almost as an inconvenience, a senile great uncle that everyone humours. This drastically misunderstands the power and purpose of using relational technology to represent and store data.

I think the reverse can be true: RDBMSes have been described as golden hammers, in that they get applied to all sorts of things that don’t make sense. If, for example, you only want the features of bare schema, you shouldn’t be using a database.

Databases matter when you want:

* ACID properties,
* Single points of enforcement of business logic, and
* Fast ad-hoc queries.

1 08 2008
Jesse McNelis

Stored procedures are good because they can provide a means of protecting your data from your application.
If you have multiple programs or multiple version of a program accessing data in a database then stored procedures will make sure that the database remains in a consistent state.
The logic that is generally written in stored procedures is usually unlikely to change much and are generally much simpler than your main program.

1 08 2008
João Marcus

I can give one *very* important reason for not using Stored Procedures: whenever I need to do *anything*, I have to ask the DBA to change the SP’s. That can be a major PITA in environments where the DBA has lots of work to do, and then takes several hours (even days) to change a single line in a single procedure.

1 08 2008
Andrew Gilfrin

Joao – Thats a DBA problem not a Store Procedure problem.

As for the article, if you don’t want to use them then thats fine by me. However your wrong on most points, Jacques cover some of it.

Point 4 in particular is totally wrong as with Joao’s point your blaming the technology when it’s the method your using thats the problem. To be honest if you were talking about versioning of tables in DB’s you might have a point but when it comes to stored procedures, not a problem.

Point 5, depends on what your using, you might not understand what 90% of the error messages mean but to me they provide all the information I need.

Point 6, again your confusing a problem with the way you implement stored procedures, with a problem with stored procedures in general.

Hence, databases are not programmable enough to include all the business logic.

Never seen anything in all my years in programming that when it comes to business logic a decent stored procedure language couldn’t do that any other programming could.

1 08 2008
Chris

full disclosure: I’m a self taught developer with very little formal CS education. My development career started by inheriting a DBA/database developer position.

I think each developer/team needs to use the tools that help them get the job done most efficiently. I typically start out a little heavy with the stored procedures and migrate into code when the proper logic/architecture becomes more transparent.

Some folks will disagree, but I think you should first solve the problem at hand with awareness of your weaknesses. Then proactively work to educate yourself while improving the project.

Now, one point was highlighted above which consistently annoys the crap out of me. There’s not an easy to use, easy to deploy, synchronized source control method for database development. I there is please let me know.

1 08 2008
Erik Jones

My points match yours…

1. How many application frameworks are you using? By trying to make your application truly database agnostic (which I hold largely to be a myth) you’re actually limiting what you can do. Conversely, if you choose one database intelligently, based on your applications requirements, then you gain the huge advantage of being able to exploit *all* of it’s features.

2. “use the database as a very efficient file cabinet”, “hash in the sky”, etc.. are all total rubbish. If that’s all you want use BerkelyDB or grep. In addition, the idea that placing (some) business logic in the database breaks separation of concerns is weak, your controller and views are both run by Rails, are they not separated? And, that last point about what databases can and can’t do, take a look at PostgreSQL, you can use any of Ruby, Python, Perl, Tcl, C, Java, and/or plpgsql in your stored procedures giving you control flow to your hearts content.

3. There is nothing stopping anyone from writing a nice testing framework for database level code (I’m working on one in Python now) and there are already some out there, you just have to actually look. I won’t even touch that the comment about the ORM writing optimized SQL for you as I deal with AR generated SQL every day.

4. Bah, all of your code should be in version control and all changes should have rollback procedures tested before moving forward regardless of whether or not is for the “app” or database layer. The whole “more work” argument is not valid as the code has to be maintained no matter where it is.

5. While that definitely can be true, it depends on the error and is equally true for code written anywhere.

6. Writing out those function calls with all of those parameters definitely can be tedious, but you can make that transparent by mapping your object methods to those function calls.

Now on that last point many would say, “What’s the point of any of it then? The ORM I have will already generate direct SQL?”, and in many cases that is an extremely valid point. While most of what I’ve said may have come across as a staunch advocation of using stored procedures over application level SQL generation (be it via an ORM or otherwise), I’ve really just been playing the devil’s advocate here. Your arguments against stored procedures seem to be biased by your limited experience with both them and the use cases for them. If you don’t have a good use case requiring them and instead are asking some kind of Platonic “Are they better?” then the answer is most often no.

I’d say that one very good argument against would be that if you don’t have any actual database developers (we prefer Database Architect for grins and giggles) then you probably shouldn’t be implementing much logic or actual functionality in the database layer. There are also some very good arguments for using stored procedures. One is the financial and medical industries, they’re often required to implement much of the data access & manipulation logic in the database for security/regulatory reasons. Another good example is what Skype did, they realized that their data set would balloon in size very quickly and that they’d need much redundancy at the database level as well as the ability to transparently add more databases to their setup. So they implemented all of the data access logic in stored procedures and wrote plProxy which will allows them to shard their dataset in a way transparent to the application, it just connects to a database and calls a stored procedure which is then automatically routed to the correct database server. If they need new data or data returned in a different format they just write a new stored procedure, how is that any different from writing a new method in your application layer? I think, though, that more than for most web apps more than a few one-off stored procedures are not needed and do add extra complexity, but the same kind of added complexity that comes from using any extra technologies.

2 08 2008
nasir

Jacques Chester:

The structure of a schema is business logic. The constraints you ask the RDBMS to enforce are business rules. The triggers you might add are business logic.

I would only go as far as schema structure anything further than that should be handled by application code. Why? see point 2,3,4,5,6 of the post.

Have a look at the SQL specs some time and tell me again about limited scope.

Yeah, please tell me how would you create a multidimensional array or an array in your stored procedure.

* ACID properties,
* Single points of enforcement of business logic, and
* Fast ad-hoc queries.

Almost all ORMs provide you transaction methods that you can use to ensure that your data is in consistent state, multiple tasks were run as a single transaction and at any point of failure the whole thing is rolled back.
You can also attach behaviour in the form of callback methods to your objects i,e.(table records) at different points along their life cycle such as after initialization, before inserting, updating, removing database records and so on.

Jesse McNelis:

Stored procedures are good because they can provide a means of protecting your data from your application.

The concern should not be to protect your data from your application but only to protect your data. It can be done very well by separating your business logic from your data access tier/database that way you can also provide appropriate feedback to your application users. See point 5

If you have multiple programs or multiple version of a program accessing data in a database then stored procedures will make sure that the database remains in a consistent state.

Please have a look at point 4. However, you can also provide a RESTful interface without using stored procedures.

The logic that is generally written in stored procedures is usually unlikely to change much and are generally much simpler than your main program.

The assumption you are making here is not flawless because business environment change, rules change and hence the logics. You can never guarantee anything to be permanent.

Andrew Gilfrin:

Point 4 in particular is totally wrong as with Joao’s point your blaming the technology when it’s the method your using thats the problem. To be honest if you were talking about versioning of tables in DB’s you might have a point but when it comes to stored procedures, not a problem.

Could you please elaborate on why it’s not a problem?

Point 5, depends on what your using, you might not understand what 90% of the error messages mean but to me they provide all the information I need.

Hmm… so do you want to throw those messages to the app users since it makes sense to you or do want to sit with your application developer(s) and explain each of those messages or the other exceptions that might be raised due to unforeseen errors so that your developer can translate them all into a user friendly message. If you think that’s a right way to do things then yeah ‘Good luck’.

Never seen anything in all my years in programming that when it comes to business logic a decent stored procedure language couldn’t do that any other programming could.

Please read point 2 fully.

2 08 2008
VB

Both points of view are correct, but for entirely different types of application. I spend a lot of my working hours in the care and feeding of a database that started in 1993 as a bunch of dBase III tables and passed through Access and now resides in PostgreSQL. Along the way we dabbled in Borland Paradox and Lotus Approach as well.

This dataset is accessed by more than a dozen different applications written by different teams at different points of time. One of the apps is in fact a very Web 2.0-ish website. I expect this database’s descendant to one day live in a form that is as far beyond PostgreSQL as PostgreSQL is beyond dBase III.

Such databases are very common in businesses. In most cases, the database is an embodiment of the business. The state that the dataset is in is the business and the continuous transition from one consistent dataset to another is what the business does.

There’s no way such a database can go through this kind of a life cycle without being a an active, living thing that can defend itself against generations of application programmers intent upon solving their immediate problems. This world cannot exist without SPs and triggers and the rest of that stuff.

On the other hand, there is now a much larger universe of single-purpose applications where the database should be, quite appropriately, just a simple dumb and reliable data store.

What amuses me is that the big database I’m talking about started off in 1993 as a simple dumb and reliable data store attached to a single-purpose application.

So you see Nasir, when your apps are a decade or two old, you should come back and post an update. ;-)

2 08 2008
Amir Laher

Hi Nasir<Jamal!

I have worked for years in both styles of development. I’m afraid* I prefer to use stored procedures for all database access. For any data which you want to grow and change within your business, it’s just better to lock down applications to sproc-only db access.

* note when I say ‘i’m afraid’ it’s mainly because ORM tools are enjoyable to use. I wonder whether there is a gap in the market for a good sproc-to-pojo ORM tool. Or do things like hibernate already support this?

3 reasons for preferring sproc-only access:
1. Security. It’s much easier to lock down access to individual apps, and much much easier to enforce rules & triggers with sprocs. Also, it’s usually much easier to lock out SQL injection attacks with sproc-only access.
2. Database maintianability. With exclusive use of sprocs, I actually think it’s much *easier* to plan and deploy db changes, as requirements change over time. You can treat your sprocs as an API, with each sproc being modified along with db structure, or deprecated as necessary. Far, far easier than co-ordinating code changes on multiple codebases (some of which may otherwise be not-under-development).
3. Performance. Sorry but an ORM does not make judgements on things such as table size, or with knowledge of which cacheing mechanisms are being used in the db. A DBA will know, and optimize their queries with this knowledge.

The main point I concede to is your number 1: sprocs make database-neutrality nigh-on impossible. This is certianly true.

Your perspective very much assumes that you have only one application. What happens when you have several? Sure, n-tier architectures can limit this issue, but surely not everyone advocates n-tier any more? (SOA, anyone?)

What’s wrong with using normal version control for stored procedures?
SP’s can be stored in version control, just like code. Any scripting whiz can write a script to deploy the latest sp from source control, in the same way that you might deploy your code from source control. I, and dba’s I’ve worked with, store all db structure alterations into source control. This ensures that they can roll back sprocs in particular.

ok i’ve talked enough. Thanks for the article, some well made points in there despite my overall disagreeing!

3 08 2008
Jorge Diaz Tambley

Good article, I’ve had this discussion with my customers only to end up doing what they want (SP), but all in all I prefer not doing that, specially because I don’t think it’s a good idea to have business logic in two different languages at the same time

regards

3 08 2008
Jacques Chester

Yeah, please tell me how would you create a multidimensional array or an array in your stored procedure.

You don’t: if you’re storing arrays or matrices in a relational database, you haven’t even got first normal form. You’re missing the point of decomposition.

RDBMSes are a curious part of the field in that they are the one place left where you absolutely must be familiar with the theory to understand the practice. Almost everywhere else you can bodge along with lore and duct tape. See also: PHP.

This might be an example of the blub paradox.

Almost all ORMs provide you transaction methods that you can use to ensure that your data is in consistent state, multiple tasks were run as a single transaction and at any point of failure the whole thing is rolled back.

Either it’s being done by the database for the ORM, in which case, what difference does it make to move it closer to the DBMS? Alternatively it’s being done by the ORM, in which case, why are you using the DBMS at all, and not an OODB layer?

You can also attach behaviour in the form of callback methods to your objects i,e.(table records) at different points along their life cycle such as after initialization, before inserting, updating, removing database records and so on.

Same again. Either the DBMS can (and should) handle the data layer, or you should go the other way and embrace a proper OODBMS.

3 08 2008
Pjer

As an App and DBA developer I tryed almost everything and I’m using both from both “worlds”… the only truly valid statement/point in this article is:
Why people or I **think** we don’t need them? …of course “think” is the keyword.

4 08 2008
nasir

Erik Jones:
Now my points match yours..

1. Whenever you make a decision about using any tool, forget just about database, then you don’t make that decision without thinking. It goes without saying that, you always make that decision as intelligently as possible for instance, you look into TCO, features, your application requirements, etc. But, now take into consideration that you are developing an app that you are going to sell to different customers then do you think that you can force them to use a particular database only because you have business logic in stored procedures. Can you? Or would that be better to leave it open for them to use any database they want or are already using.
2. I won’t even comment on your point “your controller and views are both run by Rails, are they not separated?”.
Yes I am aware of using other languages in stored procedures but what I said was about SQL not about using any other language in stored procedure. But if I am going to code the stored procedure using Java, ruby or any other language then why not keep it within the application code which is actually going to use it and is more maintainable.
3. Do you want to hit the database for automated tests instead of running them inside your application. FYKI.. that is slow and provides slow feedback. Goodluck.
4. Yes you can keep them in version control but with each application update you need to run the new modified stored procedures against the databases and that’s when they can get out of synch and will break your app. It happens very often if you are selling your product to multiple customers.
5. I don’t think that’s true because your application code provides much better feedback compared to stored procedure/triggers when something goes wrong.

I won’t comment on how financial and medical industries work because sometimes you just can’t change what you are following for the last 10-20yrs and you have legacy systems to maintain.

If they need new data or data returned in a different format they just write a new stored procedure, how is that any different from writing a new method in your application layer?

Then wouldn’t it be better to just modify the existing method or create a new one on the application layer. It even makes more sense if you are using a language that can increase your productivity and if your database stored procedures don’t support that language (again check point 4 of the post)

4 08 2008
nasir

Jacques Chester:

You don’t: if you’re storing arrays or matrices in a relational database, you haven’t even got first normal form. You’re missing the point of decomposition.

I don’t mean to store arrays in the database, what I meant was; what if you are coding a complex logic where at some point you have to create an array to move ahead in your algorithm. You can do that easily in your application but when it comes to stored procedures then what will you do? create temp tables? create and insert data into those temp tables at each instance? and then delete data from temp tables when that object instance is destroyed?

Either it’s being done by the database for the ORM, in which case, what difference does it make to move it closer to the DBMS?

It does, because I don’t want to keep my business logic at two places. It makes more sense to keep it at one place (i.e. the application code), in one language, run my tests efficiently and have the whole app version controlled properly.

4 08 2008
Terry Blater

I couldn’t agree more with this article. With the uptake of service oriented design over the next few years, hopefully we will see the use of stored procs relegated to legacy systems.

4 08 2008
Sergio

Chances are that if you come to ask yourself “Do I need Stored Procedures?” or “Are Stored Procedures good?”, the answer for you is “NO”.
Seriously. You don’t. If you are a Ruby/PHP/etc programmer, you don’t need stored procedures.
If you are a database programmer, then you probably already know whether the particular application you are developing needs SPs or not.

5 08 2008
nasir

VB:

dBase III tables and passed through Access and now resides in PostgreSQL. Along the way we dabbled in Borland Paradox and Lotus Approach as well…..In most cases, the database is an embodiment of the business. The state that the dataset is in is the business and the continuous transition from one consistent dataset to another is what the business does.

Yes, you are right and it also shows the fact that we change the way or tools used to store, collect and manipulate data with time except the data.

This dataset is accessed by more than a dozen different applications written by different teams at different points of time.

Now you can do the same by exposing your data as services with minimal effort compared to writing stored procedures/calling remote procedures. When you have time then check this site http://www.sphred.com You can call it a site or an API, when you are on home page then do this http://www.sphred.com/websites.xml and see what you get. You can also get the data for almost all pages by just adding .xml in front of the URL. I didn’t spend much time in exposing this data as XML though it can be used by any other application without using stored procedures. So why do I need stored procedures?
Yahoo has a plethora of applications and they follow a service based architecture so their apps can seamlessly talk to each other. Amazon’s S3 is service based, more specifically RESTful (Representational state transfer) and so is delicious and ebay doesn’t use stored procedures. As I said in my post ‘…with time, new technologies come into existence, some lose their advantages and some become obsolete. There is no point in sticking to something for which you have other better options available.’ Please also see the Terry Blaker’s comment.

….that can defend itself against generations of application programmers intent upon solving their immediate problems.

I don’t agree with this. From your point of view, could you please describe an application programmer’s concerns/responsibilities about his job?

This world cannot exist without SPs and triggers and the rest of that stuff.

First, this is an overstatement however please look at my reply to Jacques Chester above. World is changing, applications are evolving and the way we develop applications, consume data and expose data to and from our applications is also changing.

What amuses me is that the big database I’m talking about started off in 1993 as a simple dumb and reliable data store attached to a single-purpose application.

That is the evolution of your business requirement and moreso the way the data was stored. Things change, evolve and become better to exist in time, hence this is not just the case of database but any application.

Had it been pre 2000 then I would have accepted your arguments readily but not in 2008. As you said, I should post an update in a decade or two but I dont think we have to wait that long ;)

5 08 2008
nasir

Hi Amir

People have preferences due to one reason or the other so if you prefer to use SPs like many other people then thats fine.

…much easier to enforce rules & triggers with sprocs

I would like you to read my point 2 again, please. At some point of time, try giving the same business rule/logic (not just a name can have 10 chars, if ‘a’ exists then ‘b’ can, object ‘c’ can exist only if object ‘a’ and ‘b’ is unique, BUT A LITTLE MORE THAN THAT) to two equally competent guys in database and java/C#/php/python/ruby,etc and see who implements it faster, which solution is more maintainable, easy to understand and also to write tests.

..it’s usually much easier to lock out SQL injection attacks with sproc-only access

Preventing SQL injection attacks is a coding issue, and every decent developer takes that into consideration. How about parametrised SQL?

Point 2

Please read my reply to VB

Point 3

I think every ORM is written by people who are, if not best, very good, smart and have number of experience in the industry. Moreover, I never said that a developer doesn’t need the knowledge of database, infact I can give you an analogy here, 10 or more yrs back almost every developer had to think about memory management, garbage collection and other similar task to even write a small program and to make it work efficiently but now if you are using a high level language then it pretty much takes care of all those issues and coders just focus on finding solutions through their code (that doesn’t mean poorly designed program can’t have memory leaks or other issues). Hence, ORM is a tool that makes a developers life easier but that doesn’t mean a developer can use database without having any understanding of how things work.

Your perspective very much assumes that you have only one application. What happens when you have several? Sure, n-tier architectures can limit this issue, but surely not everyone advocates n-tier any more? (SOA, anyone?)

Again, please read my reply to VB

What’s wrong with using normal version control for stored procedures?

Please look into my other replies.

Thanks for taking out time to write comments

5 08 2008
Mark

Nasir,
Totally agree.

15 08 2008
Abraham Alaka

Hi Nasir,
We haven’t actually run into a situation yet where we’ve had to use stored procedures. All our dev is for the web and I dont think they are needed. I think if the app is designed well enough, then you wont need stores procedures.

20 08 2008
design

>Having said all that, I don’t mean the stored procedures were never useful or the advantages of stored procedures cited by DBAs were never true, yes they were true until 6/7 years ago. Oh really? Do they somehow no longer reduce network traffic? It’s also worth noting that the big databases have better SQL optimisation tools than any programming IDE I’ve seen, and that something that’s called a lot is better precompiled.

2 09 2008
nasir

I am glad that you asked though I left this untouched in my post because I thought the answer to this is very obvious, any way here goes the answer for people like you ;)

Oh really? Do they somehow no longer reduce network traffic?

Yes, but the reduction in network traffic they offer doesn’t affect the application much nowadays because
1. the amount of data come back as a result of those queries is much more than the query sent to the database
2. and every organisation is running gigabit network or better
Hence, as I said before, it mattered 6/7 years back but there is no point in using stored procedures only to reduce the network traffic caused by sending a query instead of a stored procedure.

It’s also worth noting that the big databases have better SQL optimisation tools than any programming IDE I’ve seen, and that something that’s called a lot is better precompiled

I don’t agree because the moment you add logic to your procedure then your so called precompilation is dead because it won’t know which path to optimise.
I will prefer to pass actual queries with valid data because modern databases cache query plans and in most cases they do it in the same manner as a precompiled stored procedure. So stored procedures do not offer the advantages anymore that they used to before.

6 11 2008
Andrew Gilfrin

Found your article on Reddit a while ago and never came back, found it by another route so just wanted to respond.

“4. Yes you can keep them in version control but with each application update you need to run the new modified stored procedures against the databases and that’s when they can get out of synch and will break your app. It happens very often if you are selling your product to multiple customers.”

Never had that problem, a stored procedure is no different to any other part of your code from what I can tell. You obviously disagree so thats fair enough. I just don’t understand why you would get out of sync, thats the whole point of version control.

“Yeah, please tell me how would you create a multidimensional array or an array in your stored procedure.”

TYPE strings IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;

“Please read point 2 fully.”

I did thanks… didn’t have a disagrement in general that Business logic should or shouldn’t be in the DB but responded to this exact comment by you.

“A programmer can do everything in the application code that can be done in a stored procedure but the opposite of this is definitely not possible. Hence, databases are not programmable enough to include all the business logic. “

6 11 2008
LeRoux

I’ve been working on corporate databases for years now and I pretty much share your point of view. I’ve found that people that defend stored procedures do it for one or more of the following reasons:

1) they don’t actually know the alternative. you may *tell* them there are ways to do it in VB or .NET or Java or ORM and they’ll say they believe you but in reality they don’t and haven’t really looked into them. these guys usually bring up stuff like security through obscurity, or tell you that you can’t prevent injection without using stored procedures.

2) they are fully professionally invested in SPs and such and such. they spent many years studying databases and PLSQL and to tell them they don’t need it anymore is a sort of heresy.

3) the DB engine they use provides them, so telling them to stop using SPs somehow is like telling them that the money spent on them was a waste. that’s when you get comments such as “then you’re better off using MySQL or plain text files! or better yet, store your company data in clay tablets!”

4) they are used to it. they work in a corporate environment and a legacy or not so legacy database that’s full of them, with humongous programs done in Powerbuilder or some other 4GL and 90% of the database is done in stored procedures, so they figure it’s probably the best way to do it. The people around them, specially those that have been working in that company for longer, and probably MADE these SPs in the first place, will defend them a lot and reinforce this preconception constantly.

I just finished programming the passport/airport migration control software about a year ago and I was a big opponent of using stored procedures in our apps. In the end we managed to make the entire thing using webservices and WinForms and remoting, so all the business logic was in a separate layer (in a separete computer) and there was absolutely no SQL queries in the interface layer and only a couple SPs that are used for batch and jobs and such stuff. Programming it was a blast, we used version control easily and could revert to any version at any moment (as long as we reverted both the server and the client app together). Also many different applications used the same webservice/remoting server for different purposes.

7 01 2009
conatural

I believe the most compelling reason for using stored procedures today is the extra security layer it provides by limiting the access to tables and views, all the other reasons are debatable. I personally use a custom DAL that takes care of deploying my SQL commands automatically as stored procedures when I deploy my apps. You can find more at CoNatural Blogging.

18 05 2010
Ashis

After reading the post I can say that this is very misleading for a new/budding developer. Database Stored procs are very powerful tools without which it is not possible to build complex applications.

30 10 2010
rajatraju

hello, I just wanted to say that I really enjoyed your blog and this post.
You make some very informative points. Keep up the great work!
Thank You

http://my.opera.com/larryrhodes/blog/show.dml/18615602

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s




Follow

Get every new post delivered to your Inbox.

%d bloggers like this: