Hello everyone.
My Hibernate version is: 2.1.3
My database is: MySQL 4.1.2 alpha
The problem I have is this:
I have a Hibernate Query, which returns a list of items.
I expected the items to be loaded with a single SQL query.
However, an extra SQL query is run for every item in the list. My list can contain several hundreds of thousands of items, so this becomes quite expensive.
I realize that I am probably doing something wrong here, but I cannot find what it is.
Any help would be greatly appreciated.
The query is following:
Code:
// load the Item instance
String query = "FROM vyre.content.Item item "
+ "WHERE "
+ "item.itemInfo.collectionSchema.id = '" + schemaId + "' "
+ "AND item.version = item.itemInfo.activeVersion "
+ "AND item.itemInfo.isDeleted = '0' "
+ "ORDER BY item.creationDate DESC "
;
List l = null;
try {
l = session.find(query);
} catch ( HibernateException he ) {
throw new PersistenceException( "Unable to find Items", he );
}
return l;
ItemInfo.hbm.xml
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="vyre.content.ItemInfo" table="item_info">
<id name="id" length="30">
<generator class="assigned"/>
</id>
<property name="activeVersion" column="active_version" not-null="true"/>
<property name="isDeleted" column="is_deleted" not-null="true"/>
<property name="deleteDate" column="del_date" type="timestamp" not-null="false"/>
<property name="checkoutDate" column="checkout_date" type="timestamp" not-null="false"/>
<many-to-one name="collectionSchema" column="coll_schema_id" not-null="true" />
<many-to-one name="deletorProfile" column="deletor_profile_id" not-null="false" />
<many-to-one name="checkoutProfile" column="checkout_profile_id" not-null="false" />
<bag name="items" inverse="true" cascade="all" lazy="true">
<key column="item_id"/>
<one-to-many class="vyre.content.Item"/>
</bag>
</class>
</hibernate-mapping>
Item.hbm.xml
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="vyre.content.Item" table="items">
<composite-id>
<key-property name="itemId" column="item_id" length="20"/>
<key-property name="version"/>
</composite-id>
<property name="name" not-null="true"/>
<property name="description" not-null="true" length="1000"/>
<property name="keywords" not-null="false" length="1000"/>
<property name="locale" not-null="true"/>
<property name="deletable" not-null="true"/>
<property name="creationDate" column="creation_date" type="timestamp" not-null="true" update="false"/>
<property name="lastActivationDate" column="last_activation_date" type="timestamp" not-null="false" />
<property name="lastDeactivationDate" column="last_deactivation_date" type="timestamp" not-null="false" />
<component name="fileInfo" class="vyre.content.FileInfo">
<property name="name" column="file_name"/>
<property name="mimeType" column="file_mime_type"/>
<property name="size" column="file_size"/>
</component>
<many-to-one name="itemInfo" column="item_id" not-null="true" insert="false" update="false" />
<many-to-one name="creatorProfile" column="creator_profile_id" not-null="true" />
<bag name="categories" table="items_categories" inverse="false"
lazy="true">
<key>
<column name="item_id"/>
<column name="version"/>
</key>
<many-to-many class="vyre.content.Category" column="category_id"/>
</bag>
</class>
</hibernate-mapping>
CollectionSchema.hbm.xml
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="vyre.content.CollectionSchema" table="collection_schemas">
<id name="id" length="20">
<generator class="assigned"/>
</id>
<property name="name" not-null="true"/>
<property name="description" not-null="true" length="1000"/>
<property name="keywords" not-null="false" length="1000"/>
<property name="deletable" not-null="true"/>
<property name="creationDate" column="creation_date" type="timestamp" not-null="true" update="false"/>
<property name="lastModifiedDate" column="last_mod_date" type="timestamp" not-null="true"/>
<property name="creator" column="creator" update="false"/>
<property name="lastModifier" column="last_modifier"/>
<property name="locale" not-null="true"/>
<property name="fileFolderPath" column="file_folder_path" not-null="true"/>
<property name="storesFiles" not-null="true" column="stores_files" update="false"/>
<property name="xmlName" column="xml_name"/>
<many-to-one name="previewTemplate" column="preview_template_id"/>
<many-to-one name="xmlNamespace" column="xml_namespace_id"/>
<bag name="itemInfos" inverse="true" cascade="delete" lazy="true">
<key column="coll_schema_id"/>
<one-to-many class="vyre.content.ItemInfo"/>
</bag>
<bag name="attributeDefs" inverse="true" cascade="delete" lazy="true">
<key column="coll_schema_id"/>
<one-to-many class="vyre.content.CollectionAttributeDefinition"/>
</bag>
<bag name="sectionDefs" inverse="true" cascade="delete" lazy="true">
<key column="coll_schema_id"/>
<one-to-many class="vyre.content.SectionDefinition"/>
</bag>
<bag name="attributePresRuleRefs" cascade="all" lazy="true">
<key column="coll_schema_id"/>
<composite-element class="vyre.content.presentation.AttributePresentationRuleReference">
<many-to-one name="presentationRule" column="pres_rule_id" not-null="true"/>
<many-to-one name="attributeDefinition" column="att_def_id" class="vyre.content.CollectionAttributeDefinition" unique="true" cascade="all" />
</composite-element>
</bag>
<bag name="fileServiceConfigs" inverse="true" cascade="all" lazy="true">
<key column="coll_schema_id"/>
<one-to-many class="vyre.fileservices.FileServiceConfiguration"/>
</bag>
</class>
</hibernate-mapping>
Profile.hbm.xml
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="vyre.realms.Profile" table="user_profiles">
<id name="id" length="20">
<generator class="assigned"/>
</id>
<property name="userId" not-null="true" />
<property name="realmId" not-null="true" />
<property name="username" not-null="true" />
<property name="locale" not-null="true"/>
<property name="timeZone" not-null="true"/>
<property name="skin" not-null="true"/>
<property name="changePasswdOnNextLogin" not-null="true"/>
</class>
</hibernate-mapping>
I have turned hibernate.show_sql = true on.
This is what happens, there seem to be three queries, and then one for each item (four in this case):
Code:
Hibernate: select item0_.item_id as item_id, item0_.version as version, item0_.name as name, item0_.description as descript4_, item0_.keywords as keywords, item0_.locale as locale, item0_.deletable as deletable, item0_.creation_date as creation8_, item0_.last_activation_date as last_act9_, item0_.last_deactivation_date as last_de10_, item0_.file_name as file_name, item0_.file_mime_type as file_mi12_, item0_.file_size as file_size, item0_.item_id as item_id, item0_.creator_profile_id as creator14_ from items item0_, item_info iteminfo1_ where (iteminfo1_.coll_schema_id='6699' and item0_.item_id=iteminfo1_.id)AND(item0_.version=iteminfo1_.active_version and item0_.item_id=iteminfo1_.id)AND(iteminfo1_.is_deleted='0' and item0_.item_id=iteminfo1_.id) order by item0_.creation_date DESC
Hibernate: select iteminfo0_.id as id5_, iteminfo0_.active_version as active_v2_5_, iteminfo0_.is_deleted as is_deleted5_, iteminfo0_.del_date as del_date5_, iteminfo0_.checkout_date as checkout5_5_, iteminfo0_.coll_schema_id as coll_sch6_5_, iteminfo0_.deletor_profile_id as deletor_7_5_, iteminfo0_.checkout_profile_id as checkout8_5_, collection1_.id as id0_, collection1_.name as name0_, collection1_.description as descript3_0_, collection1_.keywords as keywords0_, collection1_.deletable as deletable0_, collection1_.creation_date as creation6_0_, collection1_.last_mod_date as last_mod7_0_, collection1_.creator as creator0_, collection1_.last_modifier as last_mod9_0_, collection1_.locale as locale0_, collection1_.file_folder_path as file_fo11_0_, collection1_.stores_files as stores_12_0_, collection1_.xml_name as xml_name0_, collection1_.preview_template_id as preview14_0_, collection1_.xml_namespace_id as xml_nam15_0_, previewtem2_.id as id1_, previewtem2_.name as name1_, previewtem2_.description as descript3_1_, previewtem2_.code as code1_, previewtem2_.for_filestores_only as for_file5_1_, xmlnamespa3_.id as id2_, xmlnamespa3_.uri as uri2_, xmlnamespa3_.prefix as prefix2_, profile4_.id as id3_, profile4_.userId as userId3_, profile4_.realmId as realmId3_, profile4_.username as username3_, profile4_.locale as locale3_, profile4_.timeZone as timeZone3_, profile4_.skin as skin3_, profile4_.changePasswdOnNextLogin as changePa8_3_, profile5_.id as id4_, profile5_.userId as userId4_, profile5_.realmId as realmId4_, profile5_.username as username4_, profile5_.locale as locale4_, profile5_.timeZone as timeZone4_, profile5_.skin as skin4_, profile5_.changePasswdOnNextLogin as changePa8_4_ from item_info iteminfo0_ left outer join collection_schemas collection1_ on iteminfo0_.coll_schema_id=collection1_.id left outer join coll_preview_templates previewtem2_ on collection1_.preview_template_id=previewtem2_.id left outer join xml_namespaces xmlnamespa3_ on collection1_.xml_namespace_id=xmlnamespa3_.id left outer join user_profiles profile4_ on iteminfo0_.deletor_profile_id=profile4_.id left outer join user_profiles profile5_ on iteminfo0_.checkout_profile_id=profile5_.id where iteminfo0_.id=?
Hibernate: select profile0_.id as id0_, profile0_.userId as userId0_, profile0_.realmId as realmId0_, profile0_.username as username0_, profile0_.locale as locale0_, profile0_.timeZone as timeZone0_, profile0_.skin as skin0_, profile0_.changePasswdOnNextLogin as changePa8_0_ from user_profiles profile0_ where profile0_.id=?
Hibernate: select iteminfo0_.id as id5_, iteminfo0_.active_version as active_v2_5_, iteminfo0_.is_deleted as is_deleted5_, iteminfo0_.del_date as del_date5_, iteminfo0_.checkout_date as checkout5_5_, iteminfo0_.coll_schema_id as coll_sch6_5_, iteminfo0_.deletor_profile_id as deletor_7_5_, iteminfo0_.checkout_profile_id as checkout8_5_, collection1_.id as id0_, collection1_.name as name0_, collection1_.description as descript3_0_, collection1_.keywords as keywords0_, collection1_.deletable as deletable0_, collection1_.creation_date as creation6_0_, collection1_.last_mod_date as last_mod7_0_, collection1_.creator as creator0_, collection1_.last_modifier as last_mod9_0_, collection1_.locale as locale0_, collection1_.file_folder_path as file_fo11_0_, collection1_.stores_files as stores_12_0_, collection1_.xml_name as xml_name0_, collection1_.preview_template_id as preview14_0_, collection1_.xml_namespace_id as xml_nam15_0_, previewtem2_.id as id1_, previewtem2_.name as name1_, previewtem2_.description as descript3_1_, previewtem2_.code as code1_, previewtem2_.for_filestores_only as for_file5_1_, xmlnamespa3_.id as id2_, xmlnamespa3_.uri as uri2_, xmlnamespa3_.prefix as prefix2_, profile4_.id as id3_, profile4_.userId as userId3_, profile4_.realmId as realmId3_, profile4_.username as username3_, profile4_.locale as locale3_, profile4_.timeZone as timeZone3_, profile4_.skin as skin3_, profile4_.changePasswdOnNextLogin as changePa8_3_, profile5_.id as id4_, profile5_.userId as userId4_, profile5_.realmId as realmId4_, profile5_.username as username4_, profile5_.locale as locale4_, profile5_.timeZone as timeZone4_, profile5_.skin as skin4_, profile5_.changePasswdOnNextLogin as changePa8_4_ from item_info iteminfo0_ left outer join collection_schemas collection1_ on iteminfo0_.coll_schema_id=collection1_.id left outer join coll_preview_templates previewtem2_ on collection1_.preview_template_id=previewtem2_.id left outer join xml_namespaces xmlnamespa3_ on collection1_.xml_namespace_id=xmlnamespa3_.id left outer join user_profiles profile4_ on iteminfo0_.deletor_profile_id=profile4_.id left outer join user_profiles profile5_ on iteminfo0_.checkout_profile_id=profile5_.id where iteminfo0_.id=?
Hibernate: select iteminfo0_.id as id5_, iteminfo0_.active_version as active_v2_5_, iteminfo0_.is_deleted as is_deleted5_, iteminfo0_.del_date as del_date5_, iteminfo0_.checkout_date as checkout5_5_, iteminfo0_.coll_schema_id as coll_sch6_5_, iteminfo0_.deletor_profile_id as deletor_7_5_, iteminfo0_.checkout_profile_id as checkout8_5_, collection1_.id as id0_, collection1_.name as name0_, collection1_.description as descript3_0_, collection1_.keywords as keywords0_, collection1_.deletable as deletable0_, collection1_.creation_date as creation6_0_, collection1_.last_mod_date as last_mod7_0_, collection1_.creator as creator0_, collection1_.last_modifier as last_mod9_0_, collection1_.locale as locale0_, collection1_.file_folder_path as file_fo11_0_, collection1_.stores_files as stores_12_0_, collection1_.xml_name as xml_name0_, collection1_.preview_template_id as preview14_0_, collection1_.xml_namespace_id as xml_nam15_0_, previewtem2_.id as id1_, previewtem2_.name as name1_, previewtem2_.description as descript3_1_, previewtem2_.code as code1_, previewtem2_.for_filestores_only as for_file5_1_, xmlnamespa3_.id as id2_, xmlnamespa3_.uri as uri2_, xmlnamespa3_.prefix as prefix2_, profile4_.id as id3_, profile4_.userId as userId3_, profile4_.realmId as realmId3_, profile4_.username as username3_, profile4_.locale as locale3_, profile4_.timeZone as timeZone3_, profile4_.skin as skin3_, profile4_.changePasswdOnNextLogin as changePa8_3_, profile5_.id as id4_, profile5_.userId as userId4_, profile5_.realmId as realmId4_, profile5_.username as username4_, profile5_.locale as locale4_, profile5_.timeZone as timeZone4_, profile5_.skin as skin4_, profile5_.changePasswdOnNextLogin as changePa8_4_ from item_info iteminfo0_ left outer join collection_schemas collection1_ on iteminfo0_.coll_schema_id=collection1_.id left outer join coll_preview_templates previewtem2_ on collection1_.preview_template_id=previewtem2_.id left outer join xml_namespaces xmlnamespa3_ on collection1_.xml_namespace_id=xmlnamespa3_.id left outer join user_profiles profile4_ on iteminfo0_.deletor_profile_id=profile4_.id left outer join user_profiles profile5_ on iteminfo0_.checkout_profile_id=profile5_.id where iteminfo0_.id=?
Hibernate: select iteminfo0_.id as id5_, iteminfo0_.active_version as active_v2_5_, iteminfo0_.is_deleted as is_deleted5_, iteminfo0_.del_date as del_date5_, iteminfo0_.checkout_date as checkout5_5_, iteminfo0_.coll_schema_id as coll_sch6_5_, iteminfo0_.deletor_profile_id as deletor_7_5_, iteminfo0_.checkout_profile_id as checkout8_5_, collection1_.id as id0_, collection1_.name as name0_, collection1_.description as descript3_0_, collection1_.keywords as keywords0_, collection1_.deletable as deletable0_, collection1_.creation_date as creation6_0_, collection1_.last_mod_date as last_mod7_0_, collection1_.creator as creator0_, collection1_.last_modifier as last_mod9_0_, collection1_.locale as locale0_, collection1_.file_folder_path as file_fo11_0_, collection1_.stores_files as stores_12_0_, collection1_.xml_name as xml_name0_, collection1_.preview_template_id as preview14_0_, collection1_.xml_namespace_id as xml_nam15_0_, previewtem2_.id as id1_, previewtem2_.name as name1_, previewtem2_.description as descript3_1_, previewtem2_.code as code1_, previewtem2_.for_filestores_only as for_file5_1_, xmlnamespa3_.id as id2_, xmlnamespa3_.uri as uri2_, xmlnamespa3_.prefix as prefix2_, profile4_.id as id3_, profile4_.userId as userId3_, profile4_.realmId as realmId3_, profile4_.username as username3_, profile4_.locale as locale3_, profile4_.timeZone as timeZone3_, profile4_.skin as skin3_, profile4_.changePasswdOnNextLogin as changePa8_3_, profile5_.id as id4_, profile5_.userId as userId4_, profile5_.realmId as realmId4_, profile5_.username as username4_, profile5_.locale as locale4_, profile5_.timeZone as timeZone4_, profile5_.skin as skin4_, profile5_.changePasswdOnNextLogin as changePa8_4_ from item_info iteminfo0_ left outer join collection_schemas collection1_ on iteminfo0_.coll_schema_id=collection1_.id left outer join coll_preview_templates previewtem2_ on collection1_.preview_template_id=previewtem2_.id left outer join xml_namespaces xmlnamespa3_ on collection1_.xml_namespace_id=xmlnamespa3_.id left outer join user_profiles profile4_ on iteminfo0_.deletor_profile_id=profile4_.id left outer join user_profiles profile5_ on iteminfo0_.checkout_profile_id=profile5_.id where iteminfo0_.id=?
Hibernate: select iteminfo0_.id as id5_, iteminfo0_.active_version as active_v2_5_, iteminfo0_.is_deleted as is_deleted5_, iteminfo0_.del_date as del_date5_, iteminfo0_.checkout_date as checkout5_5_, iteminfo0_.coll_schema_id as coll_sch6_5_, iteminfo0_.deletor_profile_id as deletor_7_5_, iteminfo0_.checkout_profile_id as checkout8_5_, collection1_.id as id0_, collection1_.name as name0_, collection1_.description as descript3_0_, collection1_.keywords as keywords0_, collection1_.deletable as deletable0_, collection1_.creation_date as creation6_0_, collection1_.last_mod_date as last_mod7_0_, collection1_.creator as creator0_, collection1_.last_modifier as last_mod9_0_, collection1_.locale as locale0_, collection1_.file_folder_path as file_fo11_0_, collection1_.stores_files as stores_12
Can anyone help??
Please let me know if you need more info.
Thanks!
Login
FAQ
Search
