I don’t choose easily when a large project is on the line. It took me months to choose JSF over Swing. And most recently, I’ve chosen JPA over Stored Procedures. I know it may be frustrating for my teammates to have to witness my arduous decision making process. With this decision in particular, I went back and forth many times before I was able to settle on something I was comfortable with. I have to look at things from many different angles, and come up with answers for many different questions.

Why did I feel I needed to choose between JPA and Stored Procedures? Are they really mutually exclusive?

Before we even decided to migrate our application from Powerbuilder to the web, we had already planned to move the data layer to Stored Procedures. There are really only 2 things a Powerbuilder programmer can do to separate presentation from business. 1.) Break out business logic into Non-Visual Objects (NVOs) 2.) Put all SQL into Stored Procedures. Otherwise, you get this classic pile of Client-Server spaghetti where all the business logic is hidden behind Button controls and such. So, it just made sense to go with Stored Procedures…initially. Once, we decided to migrate to Java, we just carried forward with the assumption that we would use Stored Procedures. That’s where the training dollars had gone, and most of us had already had experience with SPs. Well, it didn’t take long to realize that SPs were not exactly all the rage with n-tier, enterprise java applications. Instead, we found that POJO persistence was the standard for middle-tier. Oops!

At first, I thought maybe these two technologies might fit together. So I contacted Doug Clarke, Director of Product Management for Oracle’s Toplink. He wrote the following:

It is possible to use stored procedures with JPA as of our Oracle TopLink 11gR1 version (currently in preview) as well as in the new open source EclipseLink project. We have added @NamedStoredProcedureQuery annotations to facilitate this.

Here is the 11gR1 docs:
http://www.oracle.com/technology/products/ias/toplink/doc/11110/devguide/jpatlext.htm#BABEJGGI
Here is the similar docs for EclipseLink
http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Using_EclipseLink_JPA_Extensions_for_Stored_Procedure_Query

But he went on to write:

The challenge with combining stored procedures with JPA is that you may be sacrificing some of the inherent benefits of JPA. I am a big fan of using stored procedures where they offer performance optimizations of bottle-neck queries. Using them as a general data access mechanism will prevent the JPA provider from being able to leverage its cache for reading and some of the SQL generation optimizations when reading graphs of objects.

It became clear to me that it would be better to just choose one way or the other. So I began to search for arguments for and against. I soon discovered that this was not a simple choice. There are good arguments on both sides, and in the end, both paths are valid and can lead to a successful system.

Why even consider using Stored Procedures?

Some of the best arguments for using SPs come from Eric Bruno. In his article Use Stored Procedures for Java Persistence, he listed the following problems with using JPA that Stored Procedures solves:

Using stored procedures to encapsulate data manipulation helps to solve the following problems often found when this logic—the associated queries—exists within the Java layer:

  • Client/server data shuttling: When queries are made from Java code, potentially large amounts of data are shuttled back and forth between the application server and the database. This can greatly impact performance.
  • Transactions opened from outside the database: A transaction is often created for each query executed from the Java code within an application server. When a single logical operation requires multiple queries, many individual transactions may be created.
  • Tight coupling of database schema and Java code: The database structure is exposed to the Java layer. Hence, future changes to database structure may require changes to the Java code. As with Java class structure and member data, the database structure should be abstracted from other layers in the software.
  • Software release coordination: Because the Java code depends on the database structure, database releases that contain schema changes must be coordinated with releases of the dependent Java code. It’s often difficult to coordinate such releases. Furthermore, if one system must be rolled-back due to a problem, dependent systems must be rolled back also.

The article was written in September 2005 (kind of old), so I shot him an email to see if he still supported his assertions. He wrote:

I still support the use of stored procedures for data-related business logic. In short, any queries that involve joins, multi-table lookups, or any other reason that multiple calls to the database are required, are best done in the database. This helps to limit the IO usage from un-needed data shuttling, as well as extra 2PC transactions being created from the Java layer.

Also, my database administrator made the following strong argument:

Why not let the RDBMS do what it does best? What better to manage your Relational Database than a Relational Database Management System?

I also know that a few of our guys were around when our system was migrated from VS Wang to Powerbuilder. They might be thinking, “Ya know? If we’d used stored procedures from the beginning, it would be a hell of a lot easier to migrate from Powerbuilder to Java. What’s going to happen when Java goes out of style and we need to migrate this sucker again? We’re going to be stuck with the same situation we’re in now!”

Stored Procedures run fast. They can be used by just about any kind of application. They’re tried and proven.

The list is long.

Why I ultimately chose ORM

It’s not hard to find good reasons to use ORM technology such as JPA, Hibernate, and Toplink. The internet is buzzing with it. Here are some reasons I want to use it:

  • Boosts productivity - JPA frameworks handles the low-level work of writing lots of sql, mapping data to objects and objects to data, and managing database connections properly. It is also becoming increasingly well-supported by Java IDEs… a drag here, a drop here, a wizard there, a tweak there, and POOF!
  • All the cool kids are doing it - It’s no fun to be outside of the current trend. When you go with the flow, you get a lot of community support and involvement. Conferences, JUGS, forums are a lot more valuable when you’re doing what everyone else is doing. If 100,000 other guys are doing exactly what you’re doing, then someone’s bound to have solved the same problems you’re encountering.
  • It just makes sense -Think about it,
    • Objects are relational in nature
    • Relational Databases are relational in nature
    • JPA capitalizes on their shared relational nature

Ok, so here are my answers to some of the strongest arguments made for using Store Procedures instead of JPA:

Argument 1: Stored Procedures are faster and they minimize the number of trips to the database.

Answer: This would be true if there was no caching for JPA…but there is. With SPs, every interaction with the data will require a call to a stored procedure which resides in the database tier. Both Hibernate and Toplink employ very sophisticated 1st and 2nd level caching to prevent unnecessary trips to the database. Nothing is faster than no call at all!

Argument 2: Why not let the database do what it does best?

Answer: Persisting data is what Database Servers do best. Persisting business logic is what Application Servers do best. In addition, why would I want to use a procedural language to take something relational, flatten it out into unrelated result sets, and put them all back together again into something relational? And why would I want to relinguish Object-Oriented design for an entire tier of my application?

Argument 3: “Ya know? If we’d used stored procedures from the beginning, it would be a hell of a lot easier to migrate from Powerbuilder to Java. What’s going to happen when Java goes out of style and we need to migrate this sucker again? We’re going to be stuck with the same situation we’re in now!”

Answer: Next time we migrate, it may be the database technology that we’re migrating. Then we’ll be glad that we used a platform independent technology instead of tying everything to Oracle PL/SQL Stored Procedures. Now get off your lazy ass and learn something new for a change! (talking to myself here)

Possibly related posts: (automatically generated)