Stored Procedures or Java?
I found this thread on Artima forums discussing where logic should reside in database apps: stored procedures or java.
We've had this discussion many times at my place of work & I am currently on a project with zero stored procedures. But stored procedures are used heavily in our still strong legacy app & they helped us achieve acceptable performance at the time. One of the issues I remember from working on the legacy app was the problem that stored procedures caused with caching. With stored procedures updating rows w/o the knowledge of the application server, it makes cache consistency a real problem. We worked out a couple of ways to address this: 1) callbacks to the application server and 2) bringing cache into java vm in oracle database. We wrote some "stored procedures" in java as well.
I am in the camp that stored procedures have their place in batch processes that need the performance boost. But you need to plan for them up front & design your architecture accordingly.
We've had this discussion many times at my place of work & I am currently on a project with zero stored procedures. But stored procedures are used heavily in our still strong legacy app & they helped us achieve acceptable performance at the time. One of the issues I remember from working on the legacy app was the problem that stored procedures caused with caching. With stored procedures updating rows w/o the knowledge of the application server, it makes cache consistency a real problem. We worked out a couple of ways to address this: 1) callbacks to the application server and 2) bringing cache into java vm in oracle database. We wrote some "stored procedures" in java as well.
I am in the camp that stored procedures have their place in batch processes that need the performance boost. But you need to plan for them up front & design your architecture accordingly.
posted by Billy Bob Bain at 7:01 AM
3 Comments:
My religion forbids me to even speak about the dreaded st*r&d pr0c&d^r&
I don't think stored procedures are the way to go. Keep the logic all in one place, the (OO) code. A lot has been written about this and the arguments against stored procedures are very compelling.
An exception might be when the design is centered around the database rather than the domain model or the UI. However, I can't think of many situations where that would be a good way to go. Perhaps if it's a legacy database or the app is expected to have an extremely long lifespan, likely outliving the programming language or even OOD.
SQL belongs in the database not in the application.
SQL changes should not need redeployments of the application.
Data manipulation at the DB level should be transparent to the app.
The app need not be unnecessarily burdened with having to 'know' the data model.
We use databases as a service that responds to commands, it works beautifully.
In my opinion, 'transparent' persistence is good for only a strict subset of application that do mostly just insert/update/selects.
Any application with any serious data manipulation that is likely to be 'tweaked' by end user requirements need to externalize its SQL.
Post a Comment
<< Home