This week's book giveaway is in the Performance forum.
We're giving away four copies of The Java Performance Companion and have Charlie Hunt, Monica Beckwith, Poonam Parhar, & Bengt Rutisson on-line!
See this thread for details.
Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Stored procedures in J2EE

 
raj joe
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When should I use stored procedures in a J2EE applications, what are advantages and disadvantages of using stored procedures in J2EE applications.
 
David Ulicny
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One advantage is that access path is cached.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by raj joe:
When should I use stored procedures in a J2EE applications, what are advantages and disadvantages of using stored procedures in J2EE applications.


Its usually performance vs. portability. All ORM solutions (such as Entity Beans) carry a performance overhead which is usually tolerable because of the benefits of portability. If the performance of the application is key then SPs will usually win over an ORM solution.
 
Sravan Kumar
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also, if the database and the server exist in two different machines (which they normally do), you wouldn't want to place multiple database calls to achieve one simple logic that involves multiple queries.

So, it's better to push the logic to database by writing stored procedures and just place one call using CallableStatement's, if it involves huge database operations.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Sravan Kumar:
Also, if the database and the server exist in two different machines (which they normally do), you wouldn't want to place multiple database calls to achieve one simple logic that involves multiple queries.

So, it's better to push the logic to database by writing stored procedures and just place one call using CallableStatement's, if it involves huge database operations.


This wouldn't bother me. Is a far lower cost to implement better app. server to database conectivity than to pay a developer to code Stored Procredures for every database type the application may have to support (and possibly recode them as database versions change).
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Some J2EE apps use big databases. One of the problems as I understand with SPs is one of scalability: they may work well with a small DB, but what happens when you need to filter on a DB that has 50 or more tables? In this case, I would be quite surprised if SPs with optional parameters could perform as well as dynamic queries.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic