Hibernate Criteria query is running very slow

Hibernate ORM
1

I have a criteria query and it is retrieving the results very slow and taking 10+ secs which is causing my api to timeout.

final List<NotificationMessageInfoDB> batchIdDBList = new ArrayList<NotificationMessageInfoDB>();
HibernateCallback<NotificationMessageInfoDB> callback =
new HibernateCallback<NotificationMessageInfoDB>() {

	@Override
	public NotificationMessageInfoDB doInHibernate(Session session)
			throws HibernateException, SQLException {

		Criteria criteria = session.createCriteria(NotificationMessageInfoDB.class);
		criteria.add(Restrictions.ge("dateCreated", fromDate));
		criteria.add(Restrictions.lt("dateCreated", toDate));
		if (type != null) {
			criteria.add(Restrictions.eq("type", type));
		}
		criteria.add(Restrictions.isNotNull("batchUuid"));
		criteria.setProjection(Projections.projectionList().add(Projections.groupProperty("batchUuid")));

		// Add the pagination
		addPagination(criteria, Order.asc("batchUuid"), firstResult, maxResults);

		Iterator<String> results;
		results = criteria.list().iterator();
		while (results.hasNext()) {
			NotificationMessageInfoDB messageInfoDB = new NotificationMessageInfoDB();
			messageInfoDB.setBatchUuid(results.next());
			batchIdDBList.add(messageInfoDB);
		}
		return null;
	}
};
getHibernateTemplate().execute(callback);
return batchIdDBList;

It is generating the query like the following:

select * from 
	( select row_.*, rownum rownum_ from 
		( select distinct this_.BATCH_UUID as y0_ from NOTIFICATION_MESSAGE_INFO this_ where this_.DATE_CREATED>=? and this_.DATE_CREATED<? and this_.BATCH_UUID is not null and this_.TYPE=? order by this_.BATCH_UUID asc 
		) 
	row_ where rownum <= ?
	) 
where rownum_ > ?

when the query is executed directly on the database server it is getting exected in milli seconds. But, from my java application it is taking long time.

  1. oracle database table is partitioned on date
  2. I have index with all the columns used in the where clause and group by clause.
  3. table contains many around 50m records
  4. around 100,000 records will match with my criteria but i am using pagination of 100records.

Could some one help me what is the issue? I am running out of ideas and time.

2

Maybe the plan is cached when you execute it in SQL Developer and that’s why is fast.

The only way to know is to find the actual execution plan that was executed for this query and inspect that.

Use these tips to gather the actual execution plan.

3

Hi @satluri,

Not very familiar with Oracle so it’s more a general remark: what could happen is that in the case of the Hibernate Query, the query is passed without the parameters values so the plan chosen might be too generic and totally ineffective.

When you execute your query from the CLI, you pass the parameter directly so it’s more effective.

I know PostgreSQL used to have this problem at some point but it was solved at some point.

The issue is not really with Hibernate here, you need to understand why Oracle is choosing a suboptimal plan in the prepared statement case.