-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
dimitrij.zub
 Post subject: Some performance discussion on ManyToMany...
PostPosted: Mon May 05, 2008 4:13 am 
Newbie

Joined: Sat Apr 19, 2008 7:58 am
Posts: 14
Hi all who want to discuss.

I have a simple n:m relationship EJB 3.0 annotated:


TestCaseRevision.java

Code:
   @ManyToMany(mappedBy="testCaseRevisionList", cascade={CascadeType.MERGE,CascadeType.PERSIST,CascadeType.REFRESH}, fetch=FetchType.LAZY)   
   public List<TestTarget> getTargetList() {return _targetList;}
   public void setTargetList(List<TestTarget> p_targetList) {_targetList = p_targetList;}
   


TestTarget.java

Code:
@ManyToMany(cascade={CascadeType.MERGE,CascadeType.PERSIST,CascadeType.REFRESH}, fetch=FetchType.LAZY)
   @JoinTable(name="qs_mapping_TestTarget")
   public List<TestCaseRevision> getTestCaseRevisionList() {return _testCaseRevisionList;}
   public void setTestCaseRevisionList(List<TestCaseRevision> p_testCaseRevisionList) {_testCaseRevisionList = p_testCaseRevisionList;}


Basically a testcase revision is to be executed vs. several testtargets.

Now. with this Junit test:

Code:
for (int i = 0; i < 5; i++) {
//fast - create 5 targets
         target = tcm.createTestTarget("Windows XP SP"+i, "Windows XP operating system");
         targets.add(target);
      }
      //fast - delete them
      tcm.setTestTargets(tcase.getId(), targets);      
      tcm.deleteTestCase(tcase.getId());
      
      //do the really bad stuff - assign 5 target to 10 = 100 revisions tcases //damn slow
      for (int i = 0; i < 10; i++) {
         tcase = tcm.createTestCase(tgrp.getId(), tcase_uid, tcase_uid+"_description");
         tcm.setTestTargets(tcase.getId(), targets);
      }
      
//really damn slow
      for (TestTarget testTarget : targets)
         tcm.deleteTestTarget(testTarget.getTarget());


So just in order to create 50 manytomany and then delete them is extreamly slow on mysql inno db (man its jsut 100 entries in the db)

So TestTarget is owning the relation ship thats why i go forth and do this in TestCaseRevision:

Code:
   @PreRemove
   //remove this test target from all testrevisionlists including this one
   //need todo this because TestTarget is relationship owner.
   public void clearPointingTestTargets() {
      for (TestTarget target: getTargetList())         
         target.getTestCaseRevisionList().remove(this);
   }   


As i cant call just cleare on the testcaserevision itself as its not owning the ManyToMany mapping. (Am I right here? Nothing else worked?)

All this is done in a simple JTA transaction. Does anyone see bad coding in those sections?

Second one:
MyISAM is 10x faster with Hibernate then InnoDB. Do you guys think its safe regarding to JTA to use MyISAM with Hibernate?

Thanks :)
Top
 Profile  
 
sagimann
 Post subject: suggestions
PostPosted: Tue May 06, 2008 7:14 am 
Senior
Senior

Joined: Sun Jun 11, 2006 10:41 am
Posts: 164
Hi,
1. When doing performance testing, start your mysql with query tracing, e.g. mysqld --log=/tmp/q.log
2. Pls post the queries that are generated in the SLOW part.
3. Pls post your DDL. Did you create it or hibernate did?

Based on the above, there may be faster ways to perform the delete operation. I would consider a bulk-delete, but it depends on what your DDL is and how it was created.
Top
 Profile  
 
dimitrij.zub
 Post subject:
PostPosted: Tue May 06, 2008 8:01 am 
Newbie

Joined: Sat Apr 19, 2008 7:58 am
Posts: 14
Quote:
1. When doing performance testing, start your mysql with query tracing, e.g. mysqld --log=/tmp/q.log


Thanks for the hint :)

Quote:
2. Pls post the queries that are generated in the SLOW part.


The funny stuff is: It seems that
Code:
       for (qs.ejb.vo.flat.test.TestTarget testTarget : p_targets) {
         TestTarget target = _em.find(TestTarget.class, testTarget.getTarget());
         if (target == null)
            throw new IllegalStateException("Trying to set a non existing target. Please use createTestTarget() first.");
         
         target.getTestCaseRevisionList().add(n_revision);
      }   


generates this:

Code:
"2008-05-06 13:49:33","qsadmin[qsadmin] @ localhost [127.0.0.1]",6,0,"Query","/*  */ delete from qs_mapping_TestTarget where targetList_target='WinXPSP1'"
"2008-05-06 13:49:33","qsadmin[qsadmin] @ localhost [127.0.0.1]",6,0,"Query","/*  */ insert into qs_mapping_TestTarget (targetList_target, testCaseRevisionList_id) values ('WinXPSP1', 27)"
"2008-05-06 13:49:33","qsadmin[qsadmin] @ localhost [127.0.0.1]",6,0,"Query","/*  */ insert into qs_mapping_TestTarget (targetList_target, testCaseRevisionList_id) values ('WinXPSP1', 29)"
"2008-05-06 13:49:33","qsadmin[qsadmin] @ localhost [127.0.0.1]",6,0,"Query","/*  */ insert into qs_mapping_TestTarget (targetList_target, testCaseRevisionList_id) values ('WinXPSP1', 31)"
"2008-05-06 13:49:33","qsadmin[qsadmin] @ localhost [127.0.0.1]",6,0,"Query","/*  */ insert into qs_mapping_TestTarget (targetList_target, testCaseRevisionList_id) values ('WinXPSP1', 33)"
"2008-05-06 13:49:33","qsadmin[qsadmin] @ localhost [127.0.0.1]",6,0,"Query","/*  */ insert into qs_mapping_TestTarget (targetList_target, testCaseRevisionList_id) values ('WinXPSP1', 35)"
"2008-05-06 13:49:33","qsadmin[qsadmin] @ localhost [127.0.0.1]",6,0,"Query","/*  */ insert into qs_mapping_TestTarget (targetList_target, testCaseRevisionList_id) values ('WinXPSP1', 37)"
"2008-05-06 13:49:33","qsadmin[qsadmin] @ localhost [127.0.0.1]",6,0,"Query","/*  */ insert into qs_mapping_TestTarget (targetList_target, testCaseRevisionList_id) values ('WinXPSP1', 39)"
"2008-05-06 13:49:33","qsadmin[qsadmin] @ localhost [127.0.0.1]",6,0,"Query","/*  */ insert into qs_mapping_TestTarget (targetList_target, testCaseRevisionList_id) values ('WinXPSP1', 41)"
"2008-05-06 13:49:33","qsadmin[qsadmin] @ localhost [127.0.0.1]",6,0,"Query","/*  */ insert into qs_mapping_TestTarget (targetList_target, testCaseRevisionList_id) values ('WinXPSP1', 43)"


It seems to drop alle the values from the n:m mapping and then insert them all over again.. Why? Shall I use set instead?

Quote:
3. Pls post your DDL. Did you create it or hibernate did?


EJB 3.0 annotated so Hibernate did it by itself.

So far for the strange parts i have discovered.
Top
 Profile  
 
sagimann
 Post subject: more questions
PostPosted: Tue May 06, 2008 8:19 am 
Senior
Senior

Joined: Sun Jun 11, 2006 10:41 am
Posts: 164
It seems that the last piece of code is NOT the one mentioned earlier as the "slow" section. Pls post the part of the query log that corresponds to the "slow" code.
Top
 Profile  
 
dimitrij.zub
 Post subject:
PostPosted: Tue May 06, 2008 8:48 am 
Newbie

Joined: Sat Apr 19, 2008 7:58 am
Posts: 14
I have made some more checks. It seams that the slow part i was thinking about is not the problem.

As well the problem of Dropping All and Adding each again was solved by the Set.

However the biggest issue is that hibernate is doing a LOT of selected before droping the Group all the testcases belong to:

Code:
tcm.deleteTestGroup(tgrp.getId())


As we have this tree: TGrp 1:n TCase 1:n TCaseRevision n:m TTarget

it is doing this.. before dropping the grp:

Code:
127.0.0.1]",37,0,"Query","/*  */ select revisionli0_.testCase_id as testCase10_2_, revisionli0_.id as id2_, revisionli0_.id as id37_1_, revisionli0_.author_login as author9_37_1_, revisionli0_.comment as comment37_1_, revisionli0_.createDate as createDate37_1_, revisionli0_.revision as revision37_1_, revisionli0_.visibility as visibility37_1_, revisionli0_.description as descript6_37_1_, revisionli0_.executionNumber as executio7_37_1_, revisionli0_.name as name37_1_, revisionli0_.testCase_id as testCase10_37_1_, user1_.login as login31_0_, user1_.email as email31_0_, user1_.fistName as fistName31_0_, user1_.lastName as lastName31_0_, user1_.password as password31_0_ from qs_revision_TestCase revisionli0_ left outer join qs_User user1_ on revisionli0_.author_login=user1_.login where revisionli0_.testCase_id=125 order by revisionli0_.revision DESC"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select targetlist0_.testCaseRevisionList_id as testCase2_1_, targetlist0_.targetList_target as targetList1_1_, testtarget1_.target as target27_0_, testtarget1_.description as descript2_27_0_, testtarget1_.name as name27_0_ from qs_mapping_TestTarget targetlist0_ left outer join qs_TestTarget testtarget1_ on targetlist0_.targetList_target=testtarget1_.target where targetlist0_.testCaseRevisionList_id=248"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select targetlist0_.testCaseRevisionList_id as testCase2_1_, targetlist0_.targetList_target as targetList1_1_, testtarget1_.target as target27_0_, testtarget1_.description as descript2_27_0_, testtarget1_.name as name27_0_ from qs_mapping_TestTarget targetlist0_ left outer join qs_TestTarget testtarget1_ on targetlist0_.targetList_target=testtarget1_.target where targetlist0_.testCaseRevisionList_id=247"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select instructio0_.testCase_id as testCase2_1_, instructio0_.id as id1_, instructio0_.id as id29_0_, instructio0_.testCase_id as testCase2_29_0_ from qs_TestInstruction instructio0_ where instructio0_.testCase_id=126"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select revisionli0_.testCase_id as testCase10_2_, revisionli0_.id as id2_, revisionli0_.id as id37_1_, revisionli0_.author_login as author9_37_1_, revisionli0_.comment as comment37_1_, revisionli0_.createDate as createDate37_1_, revisionli0_.revision as revision37_1_, revisionli0_.visibility as visibility37_1_, revisionli0_.description as descript6_37_1_, revisionli0_.executionNumber as executio7_37_1_, revisionli0_.name as name37_1_, revisionli0_.testCase_id as testCase10_37_1_, user1_.login as login31_0_, user1_.email as email31_0_, user1_.fistName as fistName31_0_, user1_.lastName as lastName31_0_, user1_.password as password31_0_ from qs_revision_TestCase revisionli0_ left outer join qs_User user1_ on revisionli0_.author_login=user1_.login where revisionli0_.testCase_id=126 order by revisionli0_.revision DESC"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select targetlist0_.testCaseRevisionList_id as testCase2_1_, targetlist0_.targetList_target as targetList1_1_, testtarget1_.target as target27_0_, testtarget1_.description as descript2_27_0_, testtarget1_.name as name27_0_ from qs_mapping_TestTarget targetlist0_ left outer join qs_TestTarget testtarget1_ on targetlist0_.targetList_target=testtarget1_.target where targetlist0_.testCaseRevisionList_id=250"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select targetlist0_.testCaseRevisionList_id as testCase2_1_, targetlist0_.targetList_target as targetList1_1_, testtarget1_.target as target27_0_, testtarget1_.description as descript2_27_0_, testtarget1_.name as name27_0_ from qs_mapping_TestTarget targetlist0_ left outer join qs_TestTarget testtarget1_ on targetlist0_.targetList_target=testtarget1_.target where targetlist0_.testCaseRevisionList_id=249"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select instructio0_.testCase_id as testCase2_1_, instructio0_.id as id1_, instructio0_.id as id29_0_, instructio0_.testCase_id as testCase2_29_0_ from qs_TestInstruction instructio0_ where instructio0_.testCase_id=127"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select revisionli0_.testCase_id as testCase10_2_, revisionli0_.id as id2_, revisionli0_.id as id37_1_, revisionli0_.author_login as author9_37_1_, revisionli0_.comment as comment37_1_, revisionli0_.createDate as createDate37_1_, revisionli0_.revision as revision37_1_, revisionli0_.visibility as visibility37_1_, revisionli0_.description as descript6_37_1_, revisionli0_.executionNumber as executio7_37_1_, revisionli0_.name as name37_1_, revisionli0_.testCase_id as testCase10_37_1_, user1_.login as login31_0_, user1_.email as email31_0_, user1_.fistName as fistName31_0_, user1_.lastName as lastName31_0_, user1_.password as password31_0_ from qs_revision_TestCase revisionli0_ left outer join qs_User user1_ on revisionli0_.author_login=user1_.login where revisionli0_.testCase_id=127 order by revisionli0_.revision DESC"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select targetlist0_.testCaseRevisionList_id as testCase2_1_, targetlist0_.targetList_target as targetList1_1_, testtarget1_.target as target27_0_, testtarget1_.description as descript2_27_0_, testtarget1_.name as name27_0_ from qs_mapping_TestTarget targetlist0_ left outer join qs_TestTarget testtarget1_ on targetlist0_.targetList_target=testtarget1_.target where targetlist0_.testCaseRevisionList_id=252"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select targetlist0_.testCaseRevisionList_id as testCase2_1_, targetlist0_.targetList_target as targetList1_1_, testtarget1_.target as target27_0_, testtarget1_.description as descript2_27_0_, testtarget1_.name as name27_0_ from qs_mapping_TestTarget targetlist0_ left outer join qs_TestTarget testtarget1_ on targetlist0_.targetList_target=testtarget1_.target where targetlist0_.testCaseRevisionList_id=251"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select instructio0_.testCase_id as testCase2_1_, instructio0_.id as id1_, instructio0_.id as id29_0_, instructio0_.testCase_id as testCase2_29_0_ from qs_TestInstruction instructio0_ where instructio0_.testCase_id=128"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select revisionli0_.testCase_id as testCase10_2_, revisionli0_.id as id2_, revisionli0_.id as id37_1_, revisionli0_.author_login as author9_37_1_, revisionli0_.comment as comment37_1_, revisionli0_.createDate as createDate37_1_, revisionli0_.revision as revision37_1_, revisionli0_.visibility as visibility37_1_, revisionli0_.description as descript6_37_1_, revisionli0_.executionNumber as executio7_37_1_, revisionli0_.name as name37_1_, revisionli0_.testCase_id as testCase10_37_1_, user1_.login as login31_0_, user1_.email as email31_0_, user1_.fistName as fistName31_0_, user1_.lastName as lastName31_0_, user1_.password as password31_0_ from qs_revision_TestCase revisionli0_ left outer join qs_User user1_ on revisionli0_.author_login=user1_.login where revisionli0_.testCase_id=128 order by revisionli0_.revision DESC"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select targetlist0_.testCaseRevisionList_id as testCase2_1_, targetlist0_.targetList_target as targetList1_1_, testtarget1_.target as target27_0_, testtarget1_.description as descript2_27_0_, testtarget1_.name as name27_0_ from qs_mapping_TestTarget targetlist0_ left outer join qs_TestTarget testtarget1_ on targetlist0_.targetList_target=testtarget1_.target where targetlist0_.testCaseRevisionList_id=254"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [127.0.0.1]",37,0,"Query","/*  */ select targetlist0_.testCaseRevisionList_id as testCase2_1_, targetlist0_.targetList_target as targetList1_1_, testtarget1_.target as target27_0_, testtarget1_.description as descript2_27_0_, testtarget1_.name as name27_0_ from qs_mapping_TestTarget targetlist0_ left outer join qs_TestTarget testtarget1_ on targetlist0_.targetList_target=testtarget1_.target where targetlist0_.testCaseRevisionList_id=253"
"2008-05-06 14:34:16","qsadmin[qsadmin] @ localhost [12 .....


Its is refreshing the WHOLE tree before dropping the group (i guess because of the cascade delete of the group) So THIS is the slow part now :(

So the question is: How can i avoid that the whole tree is refetched before the actuall delete? Do i have the owning classes wrong?
Top
 Profile  
 
dimitrij.zub
 Post subject: Resolved
PostPosted: Wed May 07, 2008 5:51 am 
Newbie

Joined: Sat Apr 19, 2008 7:58 am
Posts: 14
Just FYI

The issue was the Cascade.ALL on ManyToMany.

Hibernate is fetching all childern with this type on delete which makes the delete operation very slow.

So on ManyToMany a Mapping Entity is recommended with adding
OnDelete(Cascade)

Would be then A n:1 Mapping n:1 B.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.