Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.x
Mapping documents:

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

Full stack trace of any exception that occurs:

Name and version of the database you are using:
Oracle

The generated SQL (show_sql=true):
Hibernate: select count(*) as y0_ from SOME_TABLE this_ where this_.SOME_ID>?
Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

Hello,

I'm experiencing poor performance when using the Projections.rowCount. I'm dealing with about 3 million records and the query takes about 1 1/2 minutes to complete.

But, if i run the query directly in the DB, it returns in a matter of seconds.

And, if I run the query directly through createSQLQuery , it returns in seconds.

Has anyone else experienced this problem?

-Lily Changstein

Ok, i have determined that the problem is not with Projections.rowCount.

The problems seems to be with sql with paramters.

This took over 4 mintues to complete!

String sql = "select count(*) as y0_ from SOME_TABLE this_ where this_.SOME_ID> :some_id";
List b = getCurrentSession().createSQLQuery(sql).addScalar("y0_", new BigDecimalType()).
setInteger("some_id", 100).list();


while this finished in 20 seconds.

String sql = "select count(*) as y0_ from SOME_TABLE this_ where this_.SOME_ID> 100";
List b = getCurrentSession().createSQLQuery(sql).addScalar("y0_", new BigDecimalType())
.list();

Doing a search i have found similar problems:
http://forum.hibernate.org/viewtopic.ph ... meter+slow

But I have not found any satisfactory answers. Anyone have any ideas?

-Lily Changstein

Ok again! I have tried the queries again but this time using JDBC and I get the same results! So, Hibernate is off the hook.


I will post again when I find out what the problem is.


-Lily Changstein

Ok, so the DBA explained that the query is slow because it using an execute plan from a previous query ... and some other db mumbo jumbo .....

Bottom line, he suggested to not use bind variables for this kind of query.



So, back to Hibernate. I'm using the Criteria class to create the search query and the rowcount query. If I had to use string concatenation to assemble the where clause for the rowcount instead, is there a way that I can the table field names from the entity's variable name?

-Lily Changstein

ImprovedNamingStrategy looks promising .......











-Lily Changstein