• Post Reply Bookmark Topic Watch Topic
  • New Topic

stored procedure for business logic, good or bad?  RSS feed

 
Jitto P.Jose
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello friends,
I wish to make a alumni site for my class. I am supposed to use JSF , MySql, Tomcat. I heard that use of stored procedures for implementing business login will boost the performance. Is it right?. Any problem if I use code itself and write queries in classes at the time of database access? Please help me
Thank you,
Jittos�..
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any possible performance benefit is dwarfed by the downsides of not properly separating database access code from business logic. It's generally considered bad design to keep application logic in stored procedures; don't do it.

Plus -and I may make incorrect assumptions here- an alumni site does not sound like something where utmost performance is needed, or where there's complex, long-running logic to begin with.
 
Jitto P.Jose
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you for your replay,
its a site for an entire college, so more than 2000 members will be there. and many of them will be online all the time. thats why i asked about performance issues. database will be accessed frequently. i thought it would create problems. but it may not contain much more complex logic.
will any sort of database mapping like hibernate give performance improvement other than coding easiness in such small applications. I don't have much knowledge about hibernate.
thank you
jittos....
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ORM frameworks like Hibernate can improve performance (they usually use internal caches, which you'd have to develop yourself if using raw JDBC), but there are also scenarios where they are slower than raw JDBC (e.g. batch inserts of many records).

I would resort neither to putting application logic in the DB, nor to ruling out ORM, purely on the basis of what you think may be a bottleneck. It's much too early to worry about that. Start optimizing once you have proven that you need to, but not earlier. Put a good layered design in place that insulates DB access from application and presentation logic, and you will be able to change to a different data access mechanism later if you really need to.
[ February 13, 2008: Message edited by: Ulf Dittmer ]
 
Tim Holloway
Bartender
Posts: 18554
61
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is one of those questions whose answers is "it depends".

Stored procedures are bad because it puts business logic into the database. On the other hand, if multiple apps are referencing these services, it ensures that they're all playing according to the same rules. The majority of database apps, however probably have one app to do the business, so that's a reason to avoid stored procedures.

Another thing stored procedures are good for is cases where a large amount of backend processing needs to be done. By keeping the processing inside the database itself and only returning the distilled results, the overall network traffic is minimized and a load is taken off the application server. However, you're then putting the load on the database server, and while database servers are typically pretty powerful machines, if you have 25 app server machines and one database machine with only 4 or 8 processors, you're much better off distributing the load onto the 25 appservers. First of all because in total, they will usually have more overall horsepower and secondly, if you put too much load on the DB server, it becomes a major bottleneck for the entire installation.
 
Nikos Pougounias
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A major European project has implemented business logic using store procedures. No doubt, PL/SQL is really powerful. As it has turned out, this choice performs well in some areas; but it causes a big head-ache when it's about maintenance and extensibility.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!