All,
Been using hibernate for a few weeks now and everything looks great...until now.

I have a query (mapping below) that uses a join.

when I run it with hibernate debug on, it flies up until the point where it says:

"DEBUG: opening JDBC connection:"
"DEBUG: select blah blah blah"

it then hangs for about 5 seconds, returns a few results (like, 2), then goes on.

if i copy the sql from the dump into query analyzer, it flies. if i run the sql directly, it flies. if i use DBUtils to run the query and map it into a list of objects, it flies.

what I can't figure out is what is hibernate doing with this one particular query, that it's not telling me about, that's taking a 50ms query to take 5 seconds to run.

I get the sql statement, then in 5 seconds, I get
"DEBUG: "about to open ResultSet"

very strange.

All other stuff I'm doing with hibernate is quite fast. It's just this one particular query!

The table has about 90000 rows in it, for what it's worth.

here's the mapping. what you'll see is a join from the main table to a view. I imagine this isn't preferred, but it's how it's gotta be done in this case. It seems that the join is where my problems are, because if I comment out that part of the mapping things move along much faster. problem is, I don't know how to get all of this data into the Bean and still have the fields outside of the join updateable, which is what i need. everything in the join is derived, and everything outside of the join is the stuff i need to be able to update.

<class name="com.argus.doh.beans.RequestStatusBean" table="Requests" >
<id name="requestID">
<column name="RequestID" />
<generator class="native" />
</id>

<property name="statusID"/>
<property name="systemID" update="false"/>
<property name="createTS"/>
<property name="completeTS"/>
<property name="requestTypeID" update="false"/>
<property name="rushRequest" insert="false" update="false"/>
<property name="successAction" update="false"/>
<property name="failureAction" update="false"/>
<property name="jobOption" update="false"/>

<join table="v_RequestStatuses" inverse="true" fetch="join" optional="false">
<key column="requestID"/>
<property name="errorRecipients"/>
<property name="priorityID"/>
<property name="totalFileCount"/>
<property name="pendingFileCount"/>
<property name="pausedFileCount"/>
<property name="runningFileCount"/>
<property name="completedFileCount"/>
<property name="failedFileCount"/>
<property name="cancelledFileCount"/>
</join>
</class>

I forgot to add: when I run the same queries with the same mapping document in hibernate console in eclipse, I get no hang. everything pops up as it should.

one other thing: this is being used with Spring. i turned on debug for both spring and hibernate, and it's definitely hanging right after the "Open Connection" statement. nothing's going on inside of spring that's interfering, fromw aht I can tell.

i narrowed down the cause, although I don't know how to fix it and had to resort to hackery.

if i ran the query like so:

getHibernateTemplate().find("from RequestStatusBean where statusID = ?",id);

it takes about 5 seconds to run. this is to return 3 results.

I then tried adding it as a named query in the mapping and running it like so:

getHibernateTemplate().findByNamedQuery("myquery",id)

same result: about 5 seconds.

I then changed the named query so that it didn't take a parameter but just used a hard-coded ID, and changed the findByNamedQuery call to not pass in an ID.

Once I did that, the query returned in the time I'd expect.

This explains why it returned quickly in hibernate console but not in the app...because in the console i had the ID hardcoded.

I do not understand why this behavior would occur. anyone ever see problems with params/prepared statements with hibernate?

what's weird is that if I run the query using straight jdbc, or using dbutils and passing in a param, the thing doesn't hang like it does in hibernate.

So... where I am is that instead of using params, i'm just building the query on the fly and passing that into session.createSQLQuery.

I'd still like to know why this is happening though.