Hello Everyone,

This forum has been great and very informative regarding Hibernate.

Our team has been assigned the task of migrating the current system which extensively uses Oracle Stored procedures (~400+) and few Proc*C code. It's a legacy system designed 10+ years ago and has myraid of features.

We have to redesign this system on J2EE (JBoss app server). We are planing to use Hibernate in this project. We would like to design a system that's DATABASE indenpendent without compromising the performance. Currently we are planning to implement it using Oracle

As stored procedures are well known for providing high performance as it's running in the DB, we would like to know, if there will be any considerable performance problems, if we choose NOT TO USE stored procedures. Again, this is to make our design DB independent.

It would be great to know if anybody has done this type of redesign and if yes, if you could share your experience overall and specifically about the PERFORMANCE issues.

I do know that Hibernate 3 supports stored procedures but we would keep this as a second option if we have any major performance issues without stored procedures.

I greatly appreciate your time and looking forward to hear from this forum.

Thanks in advance,
ssa1

A quite blunt but good estimation here is the amount of data that is needed to execute your business routines. If you run them in Java in the middle tier, and you need half of your dataset in application server memory, it might be a better option to leave it where it is, in the database system on a (usually) separate machine.

Ask yourself if a particular module of your application functionality needs object-oriented domain models and the polymorphic features of Java (and, most likely, has higher chances for portability and reusability). You then have two options: write Java stored procedures (yes, that exists) or write a "normal" Java application using EJB session beans, interceptors and a plain Java object domain model.

The persistence aspect would be taken care off by Hibernate. Hibernate will try to optimize your data access patterns (it gives you the tools to do so), so that access to your database would still perform well, even if a good part of your business logic executes in the application tier. However, this design is much better suited for many small transactions, in a highly concurrent fashion. This is where the effects of lazy, eager, and other fetching optimizations comes into play, as well as any caching solution. In other words, Hibernate is the right tool if you couple highly polymorphic and collaborative domain object models with common online transaction processing operation.

If, on the other hand, you are working with functionality that could be better described as a batch operation or some kind of reporting, executing business logic close to the datastore might make more sense. This means you'll usually write stored procedures in PL/SQL. If you still remember SQLJ, you can also write stored procedures in Java with a custom JDK and wrappers, both provided by the DBMS vendor.

For reporting, Hibernate can make some optimizations and provides several convenience functions (projection in Hibernate's SQL dialect HQL, "read only" queries, dynamic instantiation of report objects).

Even with batch processing, Hibernate can take care of most situations if you really work in batches and don't overload your memory. However, it should be clear that in almost all cases you either _work_ in batches or you really better move your business logic closer to your database and live with PL/SQL (or whatever language your stored procedures are written in). Clearly, moving huge chunks of data (effectively "fetching too much" all the time) into your application tier has to be avoided for best performance. If really portability is your goal, ask yourself if your database management system (including the database and its data) or your application will live longer. In my experience, this can't be guessed by me or anyone else up-front.

P.S. Usually most > medium scale applications need both, OLTP and OLAP support. While it can keep up for a while, object/relational mapping is very likely never the right candidate for mass data operations (assuming we don't see some kind of "new" ORM, including real Java stored procedures and decent relational type systems in our database products). So, let's all wait for the next SQLJ.

Thanks very much for your prompt response.

Our primary goals are to move to the new technologies and at the same time improve the performance. In the current system, GUI/Biz logic/DB are tightly coupled. Any change in Biz Logic/DB will force us to modify the client. We want to eliminate this and move to the domain model and make the client mostly independent of our Biz Logic/DB.

In this system, every feature in the GUI has to access data from the DB. It either creates new data in the DB, find the data (query) or reporting. Just to give an idea of the usage, it has got 400 max concurrent users.

We don't like Entity Beans (CMP/BMP) for our persistence layer. We've two options.

a. Stick with stored procedures and define a transactional interface to access those stored procedures so that client can be independent of changes in the Biz logic. There is no Hibernate in this. Client interacts with session beans which uses DAOs to access the DB.

b. Use Hibernate with or without stored procedures.

If we choose to go with option b, and choose to use stored procedures, we will be limited to Oracle or other DBs that support stored procedures. We may migrate to MySQL in the future. For this reason, we want to design our system independent of DB.

Does anbody have data on how FAST/SLOW the java stored procedures are??? I've not used them before. Is it DB independent?

Thanks,
ssa1