Hi.

It has been a week since we first noticed problems with our application (java+hibernate+oracle+weblogic).
The query and the session are optimized with the FIRST_ROWS mode.

I've written one class that run the sql query with jdbc14. (it took 15s to perform)
I've written tool servlet that measures its time with the weblogic driver (noXA) - took also approximtelly 15s.

From the Front-End it takes above 4mins and with some parameters 10mins while in SQL+, jdbc14, weblogic it takes let say 18s.

The hql query takes models from database, it is basing on customerTable, it scans many datatables and if u put in FE the customer number which doesn't give any results - it takes the same time as if u would put there a customer that has some data linked. (I thought that that's maybe a fault of lazy collections but it seems not.)

There are respect indexes set on strategic colums...

I have hardcoded the most important parameters so that I don't pass them to the params array before getHibernateTemplate().find. For example in the query string i would have select * from customers where customerNumber=5
and not: select * from customers where customerNumber=?

What should I do...

Hardcoding the various parameters won't affect speed much: if it does affect speed at all, it will only slow down successive calls. Unless it's a pure one-off query, using ?s or named parameters is faster than building a complete HQL string.

If Hibernate is taking much longer, but doesn't issue any/many more SQL statements, then the problem is probably that your classes are too heavyweight. Maybe each one creates a Calendar instance (very expesive) or does a separate lookup from some other datasource. Also, if you end up with thousands or even millions of objects on your heap, it could well be a paging issue, as your JVM swaps objects out to disc in order to free up enough memory to create more objects.

To avoid creating those objects, use raw SQL, or at least use HQL to return Object[]s of scalars. That is, instead of HQL like "from YourClass", use "select a.prop1, a.prop2, a.prop3 ... from YourClass a".

Big reports are often good candidates for bypassing hibernate and going straight to JDBC, though.

That's exactly what I'am doing today. I think i'll leave the hql but with Object[]s of scalars. Will see if it helps :)

And it is a report query :)