Hobione's Weblog

Living & Breathing in Web 2.0 Era

StoreProcedures or Persistence APIs (Hibernate, Toplinks, JDO)?

I have chatted with Brett Schuchert and here his suggestions:

1. Choice between store procedures and persistence API. Performance wise, is it one better than other?

If you’re looking for raw performance, stored procedures are generally faster but that all depends on what you’re doing. Here are some differences:

1. Stored procedures can be faster for some operations, however for many lookups, queries are more than adequate

2. Hard to version in an RCS, not impossible

3. Harder to unit test, but not impossible

4. Often ends up implementing business rules in multiple places

5. Ties you to a vendor – might be OK – because each database vendor has their own way of doing it

So I’m not a big fan. However, sometimes they are what you need. It’s not an either-or choice… See below.


2. Is it possible to create an ORM to a store procedure?

It is possible to call a stored procedure, but creation? I do not know. I don’t think so.

3. Can an application use both, Store Procedures and Persistence Framework?

Yes. You can call stored procedures from JPA. You can look them up by name and invoke them.

4. Which is easier to maintain?

I believe code and the mappings in an ORM.

5. Which is better suited for a large-scale application especially on the web?

Both will work. I have a bias towards using my middle-tier language, I’ve seen places that use strictly stored procedures for everything – they also tend to be more fragile.

6. How well a web services work with JPA?

As for JPA and web services. I see them as orthogonal… that is, either can be used with or without the other.
Many web services will end up using a database. Using JPA for that is a natural thing to do. If your web service is simply a repackaged EJB 3-based session bean, then you’ll have access to JPA. If not, then you’ll want to make sure to define the interaction of the web service such that each individual method can start and then either commit or rollback.

Typically, web services are stateless, so trying to maintain extended conversations, while possible, is a bit against the grain.

Thanks Brett, appreciated



March 3, 2008 - Posted by | Java Persistence API


  1. Hey, Hobi. There is one issue we left out of this conversation: Sharing Business Logic. This project has a requirement of sharing some of the business components with a non-java application developed outside of our organization. Stored Procedures was the first proposal. It was received well by the outside organization, but I did not see it as a good solution given DB resource management issues. I counter-proposed Web Services to expose the business components. Web Services can expose Stored Procs and Java Middle-Tier in a non-platform-specific way. So either way, we have to use Web Services. So the issue comes down to State Management.

    JSF is designed to handle state. It shouldn’t matter if the CRUD is delegated to Stored Procs, JPA, or Web Services. They’re all in a different layer. And then there’s also BPEL. We can use BPEL to handle conversational transactions. We can also, if necessary, add state identifier information to the SOAP messages. Plenty of options. I’m not sure if I agree that it is “against the grain”.

    Comment by davidwburns | March 4, 2008 | Reply

  2. Dave & Hobi,

    I agree with David RE: using web services as a better option that stored procedures.

    As for “going against the grain”, he’s right, you can provide a state identifier within the message. Why I say it’s against the grain:
    * Construction of web services is generally done without parameters – it’s certainly possible to do otherwise
    * All instances of the web service will need to share some kind of resource to keep track of state – there are lots of options – you’ll need to roll your own
    * BPEL, sure, whose implementation? More technology, maybe overkill. Make sure you need it before you start using it, going to the BPEL darkside is seductive, and when it’s the right tool for the job, great. A lot of people see the need for BPEL without much justification (I’m speaking in general, I have no idea about Davie). So make sure it’s justified before you add yet complex technology.

    I am personally a proponent of stateful services. I do not deliberately add state and I will structure services so as to minimize the time required to keep state.

    You can certainly implement stateful web services. It’s not as well supported so you’ll have to roll your own in some respects (thus it goes against the grain).

    This particular point was something with which I totally disagreed with a previous company. In 1999 I was working at ObjectSpace. They had a web-services offering but it did not allow for calling constructors – default only. Getting state in required additional steps. It was possible, but it seemed forced. I though their implementation reflected a little too much group-think and not actually looking at what people needed.

    Comment by Brett Schuchert | March 4, 2008 | Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: