Hibernate version:3.0.2

Mapping documents:
<hibernate-mapping>

<class name="com.securimine.beans.model.ModelGroupView" table="SECVIEWModelGroup" mutable="false" lazy="false">

<id name="id" type="java.lang.Integer" column="group_id">
<generator class="increment"/>
</id>

<property name="numberOfEvents" type="java.lang.Long" update="false" insert="false" formula="count(*)"/>

</class>

</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():

List result;
ModelGroupViewI row;
String queryStr = "from ModelGroupView ca group by ca.id order by ca.id asc";

HibernateUtil.beginTransaction();
Session session = HibernateUtil.getSession();
try {
result = session.createQuery(queryStr).list();
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
} finally {
HibernateUtil.closeSession();
}

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

When queryStr = "from ModelGroupView ca group by ca.id order by ca.id asc"
the generated SQL is
select modelgroup0_.group_id as group1_, count(*) as formula0_
from SECVIEWModelGroup modelgroup0_
group by modelgroup0_.group_id
order by modelgroup0_.group_id asc

The Statistics give me:
[main] INFO org.hibernate.stat.StatisticsImpl - max query time: 2656ms


Now if I remove the numberOfEvents property from the mapping
<hibernate-mapping>

<class name="com.securimine.beans.model.ModelGroupView" table="SECVIEWModelGroup" mutable="false" lazy="false">

<id name="id" type="java.lang.Integer" column="group_id">
<generator class="increment"/>
</id>

</class>

</hibernate-mapping>


Then queryStr = "from ModelGroupView ca order by ca.id asc"
the generated SQL is
select modelgroup0_.group_id as group1_
from SECVIEWModelGroup modelgroup0_
order by modelgroup0_.group_id asc

The Statistics give me:
[main] INFO org.hibernate.stat.StatisticsImpl - max query time: 63ms

If I run both generated query in SQL Script Analyser, I get a running time of 0.0s (which is not really accurate,
but basically they are both really fast and way under 1 second.

For some reason, having the aggregate function using hibernate increase the execution time to almost 3 second.

I looked into it a little but I still don't know what the reason is.
Is there anythin that hibernate is doing because of the different HQL that would take time?
Both generated SQL should execute in the same amount of time (roughly 60ms).
So is hibernate doing some extra processing when there is an aggregate function?

I'll keep investigating but if anyone has an idea why is that, I'll be glad to hear it.

thanks,

Richard

The slow part is in the AbstractBatcher class:

public ResultSet getResultSet(PreparedStatement ps) throws SQLException {
ResultSet rs = ps.executeQuery();
resultSetsToClose.add(rs);
logOpenResults();
return rs;
}

For some reason ps.executeQuery() is slow in that case.
What I can't explain is why it is slow using hibernate and it is fast using SQL Query analyzer...
Maybe something wrong with Jtds configuration?

The problem comes from Jtds driver. So I'll investigate in that way.
Sorry for the post in Hibernate forum.