Hi Hibernate Developers.
I've written a Hibernate Criteria, where I'm only interested in the first 100 results.
I notice that the generated SQL includes a unneccessary sub select.
Is there any way to avoid this and won't this cause a big performance hit? if my inner query returns thousands or millions of results?
I'm using Oracle 10. Oracle explain plan suggests that, I'm wrong and that this nested select makes no difference compared to a single select. This example is obviously somewhat trivial, what if the criteria is more complicated?
Code:
String searchText = "a text";
Criteria criteria = session.createCriteria(GsObjectEntryTextHibernate.class);
// criteria.createAlias("gsObjectEntryTextHibernate", "text");
criteria.add(Restrictions.like("objectEntryText",searchText));
criteria.setMaxResults(100);
Collection<GsObjectEntryTextHibernate> results = criteria.list();
return results;
The generated code with setMaxResults
Code:
select * from ( select this_.GS_OBJECT_ENTRY_ID as GS1_13_0_, this_.GS_OBJECT_TYPE_ID as GS2_13_0_, this_.GS_TYPE_ID as GS3_13_0_, this_.SAP_PRUEFZIFFER as SAP4_13_0_, this_.SPRACHE_ID as SPRACHE5_13_0_, this_.OBJECT_ENTRY_TEXT as OBJECT6_13_0_, this_.SEARCH_TEXT as SEARCH7_13_0_ from DBUSER.GS_OBJECT_ENTRY_TEXT this_ where this_.OBJECT_ENTRY_TEXT like ? ) where rownum <= ?
When I avoid setMaxResults it generates this code
Code:
select this_.GS_OBJECT_ENTRY_ID as GS1_13_0_, this_.GS_OBJECT_TYPE_ID as GS2_13_0_, this_.GS_TYPE_ID as GS3_13_0_, this_.SAP_PRUEFZIFFER as SAP4_13_0_, this_.SPRACHE_ID as SPRACHE5_13_0_, this_.OBJECT_ENTRY_TEXT as OBJECT6_13_0_, this_.SEARCH_TEXT as SEARCH7_13_0_ from BESTTERM.GS_OBJECT_ENTRY_TEXT this_ where this_.OBJECT_ENTRY_TEXT like ?
I notice it does exactly the same thing when I use setMaxResults for a HQL query instead of hibernate
Thanks
Martin
Login
FAQ
Search