Hibernate version:
2.1.3

Mapping documents:
<class name="Content" table="CONTENT">

<id name="contentId" unsaved-value="0" column="content_id">
<generator class="native"/>
</id>

<property name="title">
<column name="title" index="idx_contenttitle" not-null="true"/>
</property>

<property name="notes"/>

<set name="hashInfo" cascade="all-delete-orphan" inverse="true" lazy="true">
<key column="content_id"/>
<one-to-many class="HashInfo"/>
</set>
....
some more properties
....

<many-to-one name="product" column="product_id" class="Product" unique="true" cascade="all"/>

<joined-subclass name="Song" table="SONG">

<key column="content_id"/>

<property name="track"/>

....more properties....

<property name="publishDate" type="date">
<column name="publish_date" index="idx_songpublishdate"/>
</property>

<set name="albums" inverse="true" table="ALBUM_SONG">
<key>
<column name="content_id" not-null="true"/>
</key>
<many-to-many class="Album">
<column name="album_id" not-null="true"/>
</many-to-many>
</set>

</joined-subclass>

</class>

Name and version of the database you are using:
MS SQL Server 2000

The generated SQL (show_sql=true):
select song0_.content_id as content_id, song0_.track as track12_,
song0_.duration as duration12_, song0_.publisher as publisher12_,
song0_.genre_id as genre_id12_, song0_.isrc as isrc12_, song0_.volume as volume12_,
song0_.version_title as version_8_12_, song0_.explicit_lyrics as explicit9_12_,
song0_.album_only as album_only12_, song0_.preview_url as preview11_12_,
song0_.publish_date as publish12_12_, song0__1_.title as title10_,
song0__1_.notes as notes10_, song0__1_.min_qos as min_qos10_,
song0__1_.max_qos as max_qos10_, song0__1_.product_id as product_id10_
from SONG song0_ inner join CONTENT song0__1_ on song0_.content_id=song0__1_.content_id
inner join PRODUCT product1_ on song0__1_.product_id=product1_.product_id
where (product1_.product_id= ###)

Running under JBoss 3.2.5

Here's the quick explanation....this query is taking 8-10 seconds to complete when it's run by hibernate. I grabbed the query, inserted the product_id that it's asking for and ran it through our SQL Server query interface. It takes less than a second to return. I really don't know what is causing the difference.

Longer explanation....
I am querying an instance of the Song class from the database, which is a joined subclass of the Content class. Content has a Product, and I'm querying based on the product_id. So I'm trying to get the Song (which extends Content) which has a Product with a certain ID.

I hope this is a good enough explanation....I'm just trying to figure out why there's such a large speed difference between executing the query in our application and executing the query through the SQL Server query GUI.

I really appreciate any help.

search the forum for this kind of question.

Can you post your code that is doing the query.

Trust me, I did. Don't know if I'm searching on the wrong key words, but nothing useful came up.

The main issue here is that I can run the same query that hibernate is spitting out in less than a second through the SQL Server GUI. When hibernate makes the call, it takes anywhere from 8 to 60 seconds to return one record.

I don't think this is specifically an issue with hibernate, as the query goes out, and my app just sits and waits for the data to come back. Once the information comes back, I'm off and running again.

I just figured I'd ask here because someone else might have seen the same thing.

mveitas -- the code is very simple and looks exactly like most of our other load methods:

public Content findSongByProductId(final long productId) throws DataAccessException {
return (Content) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Content c = (Content)session
.createQuery(HQL_FINDSONGBYPRODUCTID)
.setLong("productId", productId)
.uniqueResult();

return c;
}
}
);
}

And the HQL looks like this: "select content from Content content join content.product product where product.id = :productId"

try to change
select content from Content content join content.product product where product.id = :productId

with
select new content() from Content content join content.product product where product.id = :productId
(take a look at projection)

also try query.list().get(0)

and are you sure of the generated sql?

Thank you very much, I'm trying that right now.

Not sure of your question about the generated SQL....I'm copying it from the jboss server output window, we have the show SQL option on hibernate enabled right now.... So I'm as sure as I can be that this is the query that's being executed.

Ah, looks like it's a driver issue. Just switched over to an open source one (JTDS from sourceforge) and it seems to have gone away completely. A query that took anywhere from 12 seconds to more than a minute is now down to less than a second.

Thanks for the suggestions....