Hi all,
I am currently experiencing some strange performance issues with (I think) Hibernate version 2.1.6 with JBoss 4.0 on Fedora Core Linux 2 on a machine with 2gb ram, mySQL 4.1. It is running as a HAR in JBoss.
The following query runs in mySQL in 0.01ms:
Code:
mysql> select * from workflow_project where DATE_SUB( CURDATE(),INTERVAL 5 DAY ) <= amended_on;
+----+--------+------------+---------------------+------------+---------------------+
| id | title | created_by | created_on | amended_by | amended_on |
+----+--------+------------+---------------------+------------+---------------------+
| 25 | test-1 | discover | 2004-12-15 12:57:40 | discover | 2004-12-15 12:57:40 |
+----+--------+------------+---------------------+------------+---------------------+
1 row in set (0.01 sec)
However, running it through Hibernate takes an enormous 2.5 seconds, even with indexing:
Code:
public List findEditedProjects(int maxDaysOld) {
try {
String sql =
"SELECT {project.*} \n" +
" FROM workflow_project {project} \n" +
" WHERE DATE_SUB( CURDATE(),INTERVAL "+maxDaysOld+" DAY ) <= {project}.amended_on \n";
Session session = HibernateSession.currentSession();
Query query = session.createSQLQuery( sql, "project", Project.class );
logger.info(sql);
long now = System.currentTimeMillis();
List projects = query.list();
logger.info("query took "+(System.currentTimeMillis() - now)+" ms");
return projects;
} catch (HibernateException e) {
throw new BookingaspException(e);
}
}
Spits this out:
Quote:
16:33:57,674 INFO [SchedulerBean] SELECT {project.*}
FROM workflow_project {project}
WHERE DATE_SUB( CURDATE(),INTERVAL 5 DAY ) <= {project}.amended_on
16:34:00,351 INFO [SchedulerBean] query took 2676 ms
Can anyone point me in the right direction to speed this up? I'm a bit baffled!
Birju