• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

App/Business Logic in Stored Procedures?

 
Ranch Hand
Posts: 167
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 323
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 167
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 5093
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
Sharad Agarwal
Ranch Hand
Posts: 167
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 5093
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
author
Posts: 608
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Sharad Agarwal
Ranch Hand
Posts: 167
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 608
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 167
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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

reply
    Bookmark Topic Watch Topic
  • New Topic