Stored Procedures Pros/Cons
1 posts in topic
Flat View  Flat View

Posted By:   Jason_Rosenblum
Posted On:   Thursday, March 14, 2002 09:16 AM

I've been working with MS-SQL Server and stored procedures for the last 6 months and I'm curious about the negative aspects of stored procedures? Are they expensive on the server-side? What would be the advantage of using a persistence layer like EJB or JDO if you are currently just using JDBC and stored procedures? Any guidance would be appreciated.

Re: Stored Procedures Pros/Cons

Posted By:   Christopher_Schultz  
Posted On:   Friday, March 15, 2002 06:24 AM

I would use EJB if any of the following conditions were met:

  • Complex transactions are necessary across multiple objects/databases or include non-database transactional issues.

  • The existing object-to-relational mapping or code components are slow, ugly, very old, or not meeting your need.

  • You have to be very distributable, and can have multiple clients contending for identical resources (like a single table record) from multiple servers.

I would say that if you are already using your own persistence or O-R layer, the switch to JDO is not worth your time under most circumstances.

In terms of stored procedures, they will always be faster than executing any kind of Statement (or PreparedStatement) from your Java process. This is because the stored procedures are compiled when defined, and can be optimized a single time by the RDBMS, instead of on-the-fly every time a statement is issued to the server.

Also, there are some portability and security benefits from using stored procedures: want to switch databases? Okay, just re-write the stored procedures (which is a lot better than re-writing Java code). Want to lock-down a specific set of SELECT queries? Just change the permissions on those particular stored procedures.

I'll give you an example of a SELECT stored procedure that can help you port your application from, say, Oracle, to another database.

Oracle has this crazy SQL extension called CONNECT BY, which allows you to pull-back multiple records that are all related (say, from a self-referential table). If you had to port this application to a database without this capability, you'd be up a proverbial creek, and have to re-write your Java code to issue multiple queries, etc. If you had done this with a stored procedure, the Oracle procedure could have taken advantage of this nice syntactic feature, and the newer database procedure could be written to track down those relationships 'manually'. Still, no change to the Java code, which is a huge benetit.

About | Sitemap | Contact