[jira] Commented: (DERBY-3714) Significant performance degradation if Hibernate creates different order of attributes

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603316#action_12603316 ] 

Knut Anders Hatlen commented on DERBY-3714:
-------------------------------------------

It would be helpful if you could post

  1) the complete schema for the tables involved in the query (you could use dblook: http://db.apache.org/derby/docs/10.4/tools/ctoolsdblook.html)

  2) the query plans for the two queries (you can make Derby write query plans to derby.log by setting the derby.language.logQueryPlan property, see http://db.apache.org/derby/docs/10.4/tuning/rtunproper43414.html)

This problem might be caused by outdated index cardinality statistics, see DERBY-269. You may want to try the work-around mentioned there (alter table ... compress) to see if the problem is related to that issue.

> Significant performance degradation if Hibernate creates different order of attributes
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-3714
>                 URL: https://issues.apache.org/jira/browse/DERBY-3714
>             Project: Derby
>          Issue Type: Bug
>          Components: Performance
>    Affects Versions: 10.3.2.1, 10.4.1.3
>         Environment: Windows XP with Java 6u5 (JavaDB de-installed!), various hardware (single + dual core processors)
>            Reporter: Michael Gerz
>            Priority: Critical
>
> In our project we use Derby 10.4.1.3 in combination with the latest Hibernate Core 3.2.6.
> When we migrated from Java 5 to 6, we noticed a huge performance hit.
> After thorough analysis, we managed to pin down the problem to the order of the attributes in a select statement created by Hibernate. The order seems to make a huge impact on the performance, which is really strange.
> A (very simplified) example of the problem is shown below. If more attributes and more joins are added, the performance difference increases:
> Bad Performance (5 result set entries in 3672ms) :
> =======================================
> select
> 	logevent0_.clazz_ as clazz_ from (
> 		select
> 			nullif('x','x') as RECEIVER,
> 			TEST_RUN_ID,
> 			2 as clazz_ from USER_LOG_EVENT
> 		union all select
> 			RECEIVER,
> 			TEST_RUN_ID,
> 			4 as clazz_ from DATA_FLOW_LOG_EVENT ) 
> 	logevent0_ where logevent0_.TEST_RUN_ID=?
> Good Performance (5 entries in 610ms) :
> =======================================
> select
> 	logevent0_.clazz_ as clazz_ from (
> 		select
> 			TEST_RUN_ID,
> 			nullif('x','x') as RECEIVER,
> 			2 as clazz_ from USER_LOG_EVENT
> 		union all select
> 			TEST_RUN_ID,
> 			RECEIVER,
> 			4 as clazz_ from DATA_FLOW_LOG_EVENT ) 
> 	logevent0_ where logevent0_.TEST_RUN_ID=?
> Table DATA_FLOW_LOG_EVENT has the attributes 
> 	TEST_RUN_ID BIGINT, 
> 	RECEIVER VARCHAR,...
> wheras table USER_LOG_EVENT does NOT have the attribute RECEIVER.
> 	
> As hibernate generates these select statements automatically, we are not able the change the order of the attributes.
> The real question is why there is such a difference in the execution speed, and how to avoid this problem. (The complete query takes about 1-2sec in the fast version, and more than 50sec in the slow version). This makes it impossible for us to use Derby+Hibernate with Java 6!
> Any ideas?
> Kind regards,
> Michael

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.