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
Hobi