This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes General Computing and the fly likes App/Business Logic in Stored Procedures? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Engineering » General Computing
Bookmark "App/Business Logic in Stored Procedures?" Watch "App/Business Logic in Stored Procedures?" New topic
Author

App/Business Logic in Stored Procedures?

Sharad Agarwal
Ranch Hand

Joined: Sep 11, 2002
Posts: 167
We are working on a read-only application. What is wrong, if anything, with an MVC based application where the Actions are very thin stubs that simply call stored procedure to do all the heavy lifting?


Alco-Haul: We move spirits.
Demented Deliberations of a Dilettante
M Beck
Ranch Hand

Joined: Jan 14, 2005
Posts: 323
other than locking yourself even more closely to your database, you mean? possibly nothing, possibly everything. you might not care about being very closely tied to one given DBMS; then again, maybe you do.

rolling out new releases and testing development code might be trickier if you have to update stored procedures on a "testing" database. testing itself might be trickier or slower if your test cases / test harnesses have to connect to a DB to run. getting the most out of a revision control system might be tricky if the code has to be stored in a database as well; you might run into a "dueling versions" problem between your RCS and your testing DBMS. your coders might have to worry more about database administration than they might like to, and/or get into disputes with your DBA's, if you have any.

you can probably add to the list yourself, but any or all of the items might not be enough of a concern to tip the balance, depending on what your priorities are. you tell us.
Sharad Agarwal
Ranch Hand

Joined: Sep 11, 2002
Posts: 167
That is a lot of interesting points. Our application is proprietary and so tied to our business that I cannot envisage it ever being marketed outside. So, in that way, locking ourself with the DB is not such a big problem. One of our big concerns is response time. There also, I should think that stored procs would be faster than most anything we could do in the Java space.

As for testing, I am not sure how a stored proc heavy app would be any more difficult to test than one that does most of the logic in the app tier. What is the 'dueling versions' problem? The most interesting point is about squabbles with the DBAs, which we will have many
Jeroen Wenting
Ranch Hand

Joined: Oct 12, 2000
Posts: 5093
By all means use the tools available to you.
Stored procedures are almost always a lot better performant for executing database logic than is Java code or any code independent from the database for that matter (IF you have someone writing the stored procedures who knows what he's doing of course).
You need less database operations, less network traffic, possibly less CPU cycles on your busiest systems.

And if you're going to move to another database most likely code written outside the database to use the data in it would have to be rewritten anyway (especially if it's coded to have optimal performance rather than the buzzword approach of maximum decoupling).


42
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
We had a major debate on one project, migrating a smallish application from a company we acquired from SQL Server to DB2 for 100 to 1000 times the volume.

The original authors loved stored procs because they can be called by any language, any platform, with no requirement to go through any particular app server code. They could let any new client execute the procs via SQL and get them running in very short order. They had PowerBuilder, VB and Java clients. Good performance was gravy after all that goodness.

The new team preferred code as an abstraction between any application (including our own) and the database. We *never* give anyone (including our front-end guys) a connection to our database and let them write SQL. (And we never make anything easy when we could insert a layer of abstraction )

We won because, well, we bought them, not the other way around. My cufrrent project follows the same philosophy and avoids stored procs. The "no SQL from strangers" rule goes way back to mainframe DB2 in the 80s but I still like it. We can clobber the database with bad queries perfectly well all on our own, thank you!


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Sharad Agarwal
Ranch Hand

Joined: Sep 11, 2002
Posts: 167
Good discussion this. I over-simplified our situation a bit. The architecture we are considering is this:
Producer Application: DB > Stored Proc > thin app layer > web services
Consumer Application: MVC with thin actions > web service request to Producer

So, we will never be handing database handles. The question of whether to have app logic in the app layer or stored procs remained.
Jeroen Wenting
Ranch Hand

Joined: Oct 12, 2000
Posts: 5093
You can have a "no SQL from strangers" rule as well as having SPs. Just set up the database so the only thing it accepts from the accounts you make for your users are calls to the SPs (and maybe a few selected readonly table views).
Scott Ambler
author
Ranch Hand

Joined: Dec 12, 2003
Posts: 608
At Implementing Referential Integrity and Shared Business Logic I overview several implementation options, including stored procedures, and present their trade-offs. I suspect that it's one of the more balanced discussions on the web regarding this topic. Feedback would be appreciated.

- Scott


<a href="http://www-306.ibm.com/software/rational/bios/ambler.html" target="_blank" rel="nofollow">Scott W. Ambler</a><br />Practice Leader Agile Development, IBM Rational<br /> <br />Now available: <a href="http://www.ambysoft.com/books/refactoringDatabases.html" target="_blank" rel="nofollow">Refactoring Databases: Evolutionary Database Design</a>
Sharad Agarwal
Ranch Hand

Joined: Sep 11, 2002
Posts: 167
Originally posted by Scott Ambler:
At Implementing Referential Integrity and Shared Business Logic I overview several implementation options, including stored procedures, and present their trade-offs. I suspect that it's one of the more balanced discussions on the web regarding this topic. Feedback would be appreciated.


Scott - aside from a very few typos, it is indeed a well-written article. I understand that you have stayed away from specific technologies as it is meant to cover the concepts (that remain relevant across technologies).

I have a technology specific question. What is the consensus (if any) on Java Stored Procedures? Is there any standardization on them yet, or is each vendor providing their own flavor? JSPs seem to be a much better approach than using any one vendor's programming language extensions to SQL.
Scott Ambler
author
Ranch Hand

Joined: Dec 12, 2003
Posts: 608
Scott - aside from a very few typos, it is indeed a well-written article. I understand that you have stayed away from specific technologies as it is meant to cover the concepts (that remain relevant across technologies).


Thanks. In private, could you let me know what typos you found if you still remember? I'd like to fix them. Thanks.

I have a technology specific question. What is the consensus (if any) on Java Stored Procedures? Is there any standardization on them yet, or is each vendor providing their own flavor? JSPs seem to be a much better approach than using any one vendor's programming language extensions to SQL.


I'm not sure. I think that the vendors are each doing their own thing, and I don't really know where it's going. I suspect it's not taking off well -- the Oracle DBAs that I know are still writing PL/SQL for example.

My advice about using vendor-specific features is always the same -- If you do it, be aware that the performance features you're getting are locking you into the vendor, making it that more difficult to port away from them if you have to. However, bottom line is that few companies change DB vendors these days. Once a DB2 shop, always a DB2 shop.

- Scott
Sharad Agarwal
Ranch Hand

Joined: Sep 11, 2002
Posts: 167
Originally posted by Scott Ambler:

Thanks. In private, could you let me know what typos you found if you still remember? I'd like to fix them. Thanks.

I posted a note for you.


I suspect it's not taking off well -- the Oracle DBAs that I know are still writing PL/SQL for example.

That is to be expected. People tend to continue to use what they are comfortable with.


My advice about using vendor-specific features is always the same -- If you do it, be aware that the performance features you're getting are locking you into the vendor, making it that more difficult to port away from them if you have to. However, bottom line is that few companies change DB vendors these days. Once a DB2 shop, always a DB2 shop.

Thanks for the info, I quite agree. Being familiar with Java, I would prefer to be able to use that for my stored procs as well
 
wood burning stoves
 
subject: App/Business Logic in Stored Procedures?
 
Similar Threads
delete from select query
Select Statement Stored Procedures in MS sql server not returning anything...
call stored procedure from jdbc
heap question
weblogic Server Crashes