I have a more generic question for the Hibernate user community:

Is Hibernate a suitable solution in the area of small- to medium-sized data warehouse applications?

I am working on an application that accesses a well-designed, 20-or-so table database in a read-only manner (in Mysql 5). There are about 9 million rows in all the tables combined, and each request to the application may load anywhere from 1,000 to 100,000 rows from the database.

What would be the preferred architecture for such an application. Should I be using ORM at all? It feels like I should - the database has a very accessible design, and I have natural relationships between entities that are suitable to object mapping. I also like the idea of caching lots of the data in memory, as none of that data changes.

If ORM is an ok way to go, is Hibernate the right tool to use for the job? Are there other ORM framewoerks that are better suited to this kind of application?

Thanks for the input!

jeff

I wouldn't go with hibernate in such a case.

ORM has its power if you manipulate objects as you do in OLTP systems. With datawarehouses you do two things:

huge, big, complex queries. Hibernate won't help you with those, and it would only cache results, which would be of not much use.

loading your database in batches. Hibernate isn't very good at that either.

Choose a database that supports datawarehouses well. Learn it in depth and use it.

Also get some kind of reporting tool in order to provide access to the datawarehouse for the user.

There really shouldn't be much java coding involved for most of it.

I probably am using the term 'data warehouse' incorrectly, or at least in a very loose way. What I mean is that the database I have is a read-only resource. It is also very amenable to mapping to Java objects. Java happens to be the technology that I and my compatriots in crime are most proficient with (not an argument for an appropriate technology, no, but certainly a consideration) - and a true data warehousing platform is not in the plan (certainly not the budget). And I have used Hibernate on a number of different projects, all dealing with read-write databases, to good effect.

If the answer is still "don't use hibernate", then are there other ORM technologies that are better at dealing with read-only databases?

The question is what do you want to do? What does Hibernate give you?


When you planning a read only application where you navigate some rich objectstructure. Hibernate can do that very well.

If you have lots of not so big reports ... you can do those in HQL as well as in SQL, you might end up coding classes just for hibernate to store query results in. Do what every you like most.


if you have BIG reports with many results or complex queries which require database specific sql tuning. Go with plain jdbc, because you don't gain anything from hibernate, but HQL will get in your tuning ways and the creation of objects might become a relevant cost.

If you need to create stuff dynamically, I found Hibernate (and its Metadata and Configuration structure) highly usefull.


As always with these questions: there is no simple answer.

Of course you still get SQL resultset to object graph or report bean mapping from Hibernate. There are very very few good reasons for falling back to the JDBC API if you use Hibernate, in any situation.

Which ones would you consider good reasons?

Thanks for the feedback. Unfortunately, the problems I seem to be running into (I have already post earlier about this, no responses so far, but it is early daze!) is that Hibernate's first-level (session) caching and transactional support seem to be creating a performance bottleneck. In loading nearly 70k objects during the course of a single Session, I am seeing very poor performance. Again, I hesitate to "blame this on Hibernate" because maybe Hibernate is not the right tool.

If I am using Hibernate to load individual objects and lazy-load collections from them, such that up to 100k objects might have been pulled into a Session cache, is there evidence that suggests this will be a performance prohibitive application of Hibernate?

jeff

IMHO (and I bet Christian will see things very different) it seems to be obvious that Hibernate is at least part of the problem in these cases.

Hibernate gives you a couple of guarantees and services, e.g.
- you have proper Objects instead of ugly RecordSets
- you have one Instance per id, class and Session.

These things cost resources. Instances need to be created, checked if they already exist and eventually garbage collected.

A simple test should be really simple:
Prepare an object graph of realistic size.

create two little applications.

One sucks it in as Hibernate POJOs

One as Recordsets.

Both create a report in the format you need.

let us know what comes out of that experiment.