Should we drop stored procedures and run database calls from java programs.

Question

I am fighting to keep the use of stored procedures in our company. There are a few people who say they are bad and we should not use them. We are using DB2 on the i-series.

Please help in my argument to keep stored procedures alive in my company.

Answer 1

You're not going to like this, and I'm probably going to get downvoted into oblivion, but I'm with the rest of your comapny.

Stored Procedures used to offer many benefits (security, performance, etc.) but with parameterized queries and better query optimization, stored procedures really just add another layer of overhead to your application and give you another place you need to update/modify code.

I prefer to keep everything in a single spot so that when I need to edit code, I can go to one place and make my changes there.

If you want more details about the arguments for moving away from Stored Prcoedures, check out this CodingHorror article:

Coding Horror: Who Needs Stored Procedures Anyway?

...and I just noticed that the article is from 2004. I have to believe that databases have gotten better since then which means this would ring even more true today than it did then.

Answer 2

Doing everything over JDBC essentially means that you are inserting a network layer between you and the database. All in all it means that data is more "remote" and come to you slower. Stored procedures can work directly on the data inside the database, and the resulting difference in speed may astonish you.

Please note that you can write stored procedures in any IBM i language including Java, in case it is a matter of programmings skills. Also, you have access to the FULL machine, not just some database internals. Here the AS/400 is so vastly different from any other database product, that experiences from other databases simply - in my opinion - does not apply.

I would recommend the Midrange mailing lists as they have the largest concentration of AS/400 programming skills I know of.

Answer 3

They're useful when you have a layered set of apps. For example, a single core DB with web services offering the atomic operations (which happen to be stored procedures) and a ESB or a set of applications consuming those WSs. In a single-app/single-db case, the idea is to keep the code in one place as others suggested.

But well, that's just me.

Answer 4

I am a long-time Java developer who has recently come across several projects that made heavy use of stored procedures that have put the use of stored procedures in a really bad light for me.

Having said that, I am reluctant to make a blanket statement that stored procedures are bad as a system design option, because really it depends on the project in question and what the particular stored procedures are trying to accomplish.

My preference is to avoid any kind of stored procedure for simple CRUD operations (it may sound laughable to some to have stored procedures handle these operations, but I've encountered several systems that were doing this) -- This ends up resulting in a lot of code having to be written (and tested and maintained) on the Java side to manage these procedure calls from what I've observed. It's better to just use Hibernate (or some other ORM library) to handle these kinds of operations...if for no other reason than it tends to reduce the amount of code needing to be maintained. It also can cause problems when trying to refactor or make any significant changes to the system, as you're not just having to concern yourself with class/table changes, but stored procedures that handle CRUD ops as well. And this can be exacerbated further if you're in a situation where developers cannot make changes to the database themselves, or there is some formal process in place to coordinate changes between the two parts of the system.

On the other hand, having stored procedures that require limited interaction with the Java code (basically, you just fire off a call to one with a few arguments), and run in a semi-autonomous fashion is not a terrible thing either. I've encountered a few situations (particularly where we were migrating or importing data into a system) where using a stored procedure was a much better route than writing a bunch of Java code to handle the functionality.

I guess the real answer here would be that you should be examining what each store procedure in the system is doing currently and evaluate them on a case-by-case basis to determine if perhaps it's easier to handle the operation in Java or the database. Some may very well work better in Java (either by ORM library, or actual hand-written code), some may not. In either case, the goal should always be to make sure the system is understandable and easy to maintain for everyone, not just whether stored procedures are good or bad in and of themselves.

Answer 5

OK I'll come out in favor of stored procs.

First if you use them exclusively, they make refactoring the database much simpler as you can use the dependencies stored in the database to find out what would be affected by a change (well in SQL Server anyway, can't speak for other datbases).

Second, if you need to change just the query, they are far simpler to deploy.

They are also easier to performance tune as they can easily be called without firing up the application.

If you have complex logic then you save some performance by not having to send all that over the network to the database server. May not seem like a big gain, but if the complex query is run thousands of times a day, it can add up.

Security is also extremely important. If you do not use store procedures, you must set rights at the table or view level. This opens up the database to internal fraud. Yes, parameterized queries reduce the risk of sql injection, but that is not the only threat you need to guard against. If you have personal or financial data and you do not use stored procs (and ones with NO dynamic SQl) and limit your users to only being able to do things through the procs, then your system is in extreme danger from internal employees who can steal data or bypass internal controls to steal money. Read about internal controls in the accounting standards to see why this is a problem.

ORMs also tend to write just downright bad SQL code especially if the query is complex. Further as people start to use them instead of stored procs, I have found that the people who have never used stored procs have a poorer understanding of how to get data out of the database and frequently get the wrong data. Using an ORM is fine if you already understand SQL and can determine when to rewrite the autogenerated code into something that works better. But too many users don't have the skill to write complex code because they never learned the basics.

Finally since you already have stored procs for your application, getting rid of them altogether is a way to introduce new bugs becasue you had to generate new code.

Answer 6

This is one of those Marmite issues. if you are primarily a database programmer you will think that stored procedures should be used extensively. If you are an application programmer - say a Java or a .Net coder - the chances are you will say that they should be avoided completely.

Not that this meets application programmers want to write their own SQL statements. No, these days they tend to want to abstract everything behind convoluted ORM services. These are not easier to understand than stored procedures but are available within the same IDE, so they require less context switching.

There are two big things in favour of stored procedures. The first is that people who know PL/SQL are likely to be familiar with Oracle databases (T-SQL & SQL Server, etc), and so will tend to write better programs for that database (defined as programs which take advantage of the platform's features and are fitted to its functionality) than people who don't.

The second thing is that data persists. Application developers are fond of talking about "database independence" but what really matters is application independence. Front-ends come and go but the data model endures forever. In the last ten years Java applications have been written as Applets, Servlets, JSPs, Tiles and Faces, with add-ons in JavaScript, Groovy, AJAX and JSON, connecting to the database through hand-rolled JDBC, EJB (v1,2,3), TopLink, Hibernate and IBatis... forgive me if I've missed a few. Applications whose UI is a skin over a layer of stored procedures are easier to upgrade to the latest and greatest than applications where the business logic has to be re-written every time. And they will perform better too.

However, in the long run applications which interact directly with the database are probably going to die away. Everything is going to talk to the service bus, and that will decide from where to get the data. Of course, shops where the database is exposed through a well-designed API of stored procedures may find it easier to move to this brave new world than those places which are going to have to extract everything out of their ORM logic.