Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

unnecceary join with other entities

 
pavan bukka
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
In our application we have used hibernate as persistence framework. There is an entity called document in our application that has relationship with other entities called
Category, SubCategory, Assembly, SubAssembly, MasterSubAssembly, DocumentType, OperationNumber, OperationName, User in the application .

We wrote a HQL query to fetch the Documents of MasterSubAssembly, For that only we have joined Document, DocumentType, MasterSubAssembly, User using HQL, this is fetching the correct result,
but the problem is it is taking lot of time to fetch the result though there are only around 60 rows in the document table(Document entities) .

HQL which we have written is

query=session.createQuery("select d from Document d join d.documentType dt join d.masterSubAssembly m join m.subAssemblies s " +
"where d.documentName=? and s.subAssemblyId=? and d.deleteStatus=0 and " +
"d.status!='Rejected' and dt.documentTypeId=? ")
.setParameter(0, document.getDocumentName())
.setParameter(1, document.getParentId())
.setParameter(2, document.getDocumentType().getDocumentTypeId());

When i saw the sql generated by HIberate it is actually joing all the Related entitites of document entity, that's why it is leading to around 20 table's join, which is why it is taking so much of time , follwing query is what the sql generated by hiberate.

SELECT document0_.document_id AS document1_30_, document0_.checkout_status AS checkout2_30_,
document0_.checkedout_date AS checkedout3_30_, document0_.checked_out_by AS checked4_30_,
document0_.comment AS comment30_, document0_.comment2 AS comment6_30_, document0_.content_type AS content7_30_,
document0_.temp_ecn_no AS temp8_30_, document0_.delete_status AS delete9_30_, document0_.deleted_foldername AS deleted10_30_,
document0_.deleted_user_id AS deleted11_30_, document0_.document_name AS document12_30_, document0_.decsription AS decsrip13_30_,
document0_.drawing_no AS drawing14_30_, document0_.ecn_no AS ecn15_30_, document0_.modified_by AS modified16_30_,
document0_.modified_date AS modified17_30_, document0_.modified_date_doc AS modified18_30_, document0_.non_editable_doc_name
AS non19_30_, document0_.operation AS operation30_, document0_.parent_id AS parent21_30_,
document0_.parent_type_id AS parent22_30_, document0_.physical_editable_doc_name AS physical23_30_,
document0_.physical_non_editable_doc_name AS physical24_30_, document0_.status AS status30_,
document0_.status2level AS status26_30_, document0_.version AS version30_,
document0_1_.operation_name_id AS operation1_35_, document0_2_.user_id AS user1_38_, document0_3_.document_type_id AS document1_33_,
document0_4_.sub_category_id AS sub1_37_, document0_5_.assembly_id AS assembly1_31_,
document0_6_.operation_id AS operation1_36_, document0_7_.masterSubAssemblyId AS masterSu1_34_,
document0_8_.category_id AS category1_32_
FROM document document0_
LEFT OUTER JOIN document_operation_name document0_1_ ON document0_.document_id=document0_1_.document_id
LEFT OUTER JOIN users_documents document0_2_ ON document0_.document_id=document0_2_.document_id
LEFT OUTER JOIN document_document_type document0_3_ ON document0_.document_id=document0_3_.document_id
LEFT OUTER JOIN sub_categories_documents document0_4_ ON document0_.document_id=document0_4_.document_id
LEFT OUTER JOIN assemblies_documents document0_5_ ON document0_.document_id=document0_5_.document_id
LEFT OUTER JOIN document_operation_number document0_6_ ON document0_.document_id=document0_6_.document_id
LEFT OUTER JOIN mastersubassemblies_documents document0_7_ ON document0_.document_id=document0_7_.document_id
LEFT OUTER JOIN categories_documents document0_8_ ON document0_.document_id=document0_8_.document_id
INNER JOIN document_type documentty1_ ON document0_3_.document_type_id=documentty1_.document_type_id
LEFT OUTER JOIN users_document_type documentty1_1_ ON documentty1_.document_type_id=documentty1_1_.document_id
LEFT OUTER JOIN workflow_doctype documentty1_2_ ON documentty1_.document_type_id=documentty1_2_.document_type_id
LEFT OUTER JOIN doc_types_category_document_type documentty1_3_ ON documentty1_.document_type_id=documentty1_3_.document_type_id
INNER JOIN mastersubassemblies mastersuba2_ ON document0_7_.masterSubAssemblyId=mastersuba2_.masterSubAssemblyId
LEFT OUTER JOIN users_mastersubassemblies mastersuba2_1_ ON mastersuba2_.masterSubAssemblyId=mastersuba2_1_.masterSubAssemblyId
INNER JOIN subassemblies_mastersubassemblies subassembl3_ ON mastersuba2_.masterSubAssemblyId=subassembl3_.masterSubAssemblyId
INNER JOIN sub_assemblies subassembl4_ ON subassembl3_.subAssemblyId=subassembl4_.sub_assembly_id
LEFT OUTER JOIN assembly_subassembly subassembl4_1_ ON subassembl4_.sub_assembly_id=subassembl4_1_.sub_assembly_id
LEFT OUTER JOIN subassembly_parentassembly subassembl4_2_ ON subassembl4_.sub_assembly_id=subassembl4_2_.sub_assembly_id
LEFT OUTER JOIN users_sub_assemblies subassembl4_3_ ON subassembl4_.sub_assembly_id=subassembl4_3_.sub_assembly_id
LEFT OUTER JOIN subassemblies_mastersubassemblies subassembl4_4_ ON subassembl4_.sub_assembly_id=subassembl4_4_.subAssemblyId
WHERE document0_.document_name=? AND subassembl4_.sub_assembly_id=? AND document0_.delete_status=0
AND document0_.status<>'Rejected' AND documentty1_.document_type_id=?




That's why i wrote an sql query myself to fetch the results which is like this, it doesn't take much time to fetch the same 60 Document entities from the database which has only four table joins

"SELECT d.* , " +
"FROM document d " +
"INNER JOIN document_document_type ddt ON d.document_id = ddt.document_id " +
"INNER JOIN mastersubassemblies_documents md ON d.document_id = md.document_id " +
"INNER JOIN subassemblies_mastersubassemblies sm ON md.masterSubAssemblyId = sm.masterSubAssemblyId " +
"WHERE d.document_name = ? AND sm.subAssemblyId= ? AND ddt.document_type_id= ? ").addEntity(Document.class)
.setParameter(0, documentName)
.setParameter(1, parentId)
.setParameter(2, documentTypeId).list();




And While mapping Document entity with other entities I have specified fetchType as Lazy.


Can anybody tell me how to prevent Hibernate from joining unnecessary tables. (when we write hql only the specified entites in the hql should get joined and the results should come)



 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic