Hi,

We just went to Production with our system and find all our queries slow. More so when joining large tables (one has 400 000 rows the other has about 185 000 rows). I've been trying to pin point the bottleneck without success.

I do all my testing/troubleshooting with one particular SQL to make sure that if I do something that increases the speed, I'll notice it. I turned the hibernate.show_sql to true, copied the generated SQL into JBuilder DB tool and executed the query. That returned the 28 rows almost instantly. Unfortunately the same query in HQL in my application takes about 8 seconds.

I have 50 connections to the DB initialized when the Tomcat server starts.
I turned all the hibernate and JDBC debugger ON and I can see that the 8 seconds is mostly between the display of the SQL and the opening of the resulset.

Any idea what the bottleneck could be?
I'm pretty sure it's not Hibernate. All our HQL are slower then direct JDBC connection.

Hibernate version:
3.1RC2

Name and version of the database you are using:
Sybase ASE 12.0.0.8
With DBCP pooling on the Tomcat server
commons-pool-1.2.jar and commons-dbcp-1.2.1.jar

Servlet container:
Tomcat 5.5.15

The generated SQL (show_sql=true):
Doesn't really matter which SQL, they're pretty much all slower then sending direct to the DB using same JDBC driver.

Well, if I were you, I'd try to send the SQL through the same connection Hibernate uses to see if it's not the pool which is slow (though I don't see why).

It would help you identify where the snail is. If your SQL executed through the same type of connection hibernate retrieves from the pool is quick, then it would seem H3 is the bottleneck for your case.

HTH.

How are u managing ur session ... is the session already too big when this query takes place ??

Good idea. I'll try that tomorrow.

I'll let you know what happend.

I'm not sure what you mean by ... how do you manage you session?

I'm using a class I found on the web called hibernateUtil. It has methods for getting a session, commit, rollback, close session, etc.

I do not believe the session gets too big. We do not have large resultsets either. We always make sure we commit/rollback and close sessions. As I'm trying to increase performance, I always use the same JSP page. That way I can see if the changes I make have any effect.

The HQL I'm testing with has 4 inner joins and 1 outer join. I was able to increase the speed by removing one of the joins. The resulting SQL (just one by the way) is very fast when I send it directly using the same JDBC driver.

There's a bottleneck somewhere but I can't put my finger on it.

I did set all Hibernate log4j settings to DEBUG and I can see that the lost time is between the SQL and the opening of the result set. That makes me think it's between Hibernate and the JDBC driver. Which would be the Pool. But the pool I'm using is DBCP (on Tomcat 5.5.15). I find it hard to believe it would be the bottleneck.

Anyhow, I'm gonna try to use the pool directly and send the same SQL and see what happens. Let me know if you think of anything else.

Thx

I finally found the solutions.

I needed to add "DYNAMIC_PREPARE=true" to my URL connection string.

url="jdbc:sybase:Tds:1.2.3.4:1234/myDbName?DYNAMIC_PREPARE=true"/>


Can you believe it? If that make such an impact on the retrieval performance, you would think it would be a default.


Cheers