You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Michael Gerz (JIRA)" <ji...@apache.org> on 2008/06/07 11:33:45 UTC
[jira] Created: (DERBY-3714) Significant performance degradation if
Hibernate creates different order of attributes
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.4.1.3, 10.3.2.1
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.
[jira] Commented: (DERBY-3714) Significant performance degradation
if Hibernate creates different order of attributes
Posted by "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.
[jira] Commented: (DERBY-3714) Significant performance degradation
if Hibernate creates different order of attributes
Posted by "Michael Gerz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603356#action_12603356 ]
Michael Gerz commented on DERBY-3714:
-------------------------------------
AFAICS, Derby does not have an "alter table <tableName> compress;" command. "ij" always rejects the command with a syntax error.
However, I applied the following command to all tables in my database
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', '<tableName>', 1);
Unfortunately, there was no real difference in performance :-(
> 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.
[jira] Commented: (DERBY-3714) Significant performance degradation
if Hibernate creates different order of attributes
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603390#action_12603390 ]
Bryan Pendleton commented on DERBY-3714:
----------------------------------------
The predicate (logevent0_.TEST_RUN_ID=16188523) isn't getting
"pushed down" into the union properly, so it's isn't being used in
the index scan, and the entire base table is being fetched and union'ed
together, and then qualified at the top level.
I'm not sure why this isn't happening. We have a number of tests which
demonstrate that, in general, predicate pushdown occurs correctly.
Do we have a reproducible test case? Can you attach your database to
the issue as a ZIP file?
> 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.
[jira] Updated: (DERBY-3714) Significant performance degradation if
Hibernate creates different order of attributes
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-3714:
--------------------------------------
Urgency: Normal (was: Urgent)
Triaged for 10.5.2.
> 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: SQL
> 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.
[jira] Commented: (DERBY-3714) Significant performance degradation
if Hibernate creates different order of attributes
Posted by "Michael Gerz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603368#action_12603368 ]
Michael Gerz commented on DERBY-3714:
-------------------------------------
Good-performance query plan (for simplified query):
----------------------------------------------------------------
2008-06-08 11:31:19.406 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.4.1.3 - (648739): instance a816c00e-011a-67f1-d490-0000000ceb90
on database directory XXX
Database Class Loader started - derby.database.classpath=''
2008-06-08 11:31:52.328 GMT Thread[main,5,main] (XID = 3462637), (SESSIONID = 0), 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=16188523 ******* Project-Restrict ResultSet (9):
Number of opens = 1
Rows seen = 33
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 110,00
optimizer estimated cost: 568,03
Source result set:
Project-Restrict ResultSet (8):
Number of opens = 1
Rows seen = 33
Rows filtered = 0
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 110,00
optimizer estimated cost: 568,03
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 8
Rows seen from the right = 25
Rows returned = 33
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 110,00
optimizer estimated cost: 568,03
Left result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 8
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 110,00
optimizer estimated cost: 568,03
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 8
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 11,00
optimizer estimated cost: 5,91
Source result set:
Index Scan ResultSet for USER_LOG_EVENT using constraint FK64B9199FD4311151AB41192233BF62B at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 8
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0}
Number of columns fetched=1
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=8
Number of rows visited=9
Scan type=btree
Tree height=3
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 11,00
optimizer estimated cost: 5,91
Right result set:
Project-Restrict ResultSet (7):
Number of opens = 1
Rows seen = 25
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 99,00
optimizer estimated cost: 562,12
Source result set:
Index Row to Base Row ResultSet for DATA_FLOW_LOG_EVENT:
Number of opens = 1
Rows seen = 25
Columns accessed from heap = {5}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 99,00
optimizer estimated cost: 562,12
Index Scan ResultSet for DATA_FLOW_LOG_EVENT using constraint FK64B9199FD431115378EE903 at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 25
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=25
Number of rows visited=26
Scan type=btree
Tree height=-1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 99,00
optimizer estimated cost: 562,12
> 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.
[jira] Commented: (DERBY-3714) Significant performance degradation
if Hibernate creates different order of attributes
Posted by "Michael Gerz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603370#action_12603370 ]
Michael Gerz commented on DERBY-3714:
-------------------------------------
Note: The query plans above refer to queries with 33 results. I wasn't able to re-run the example given in the initial description, because I did not remember the TEST_RUN_ID used before. However, the performance problem is independent from any concrete TEST_RUN_ID.
Do you need any other information?
Thanks in advance,
Michael
> 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.
[jira] Updated: (DERBY-3714) Significant performance degradation if
Hibernate creates different order of attributes
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-3714:
---------------------------------
Component/s: SQL
> 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: SQL
> 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.
[jira] Commented: (DERBY-3714) Significant performance degradation
if Hibernate creates different order of attributes
Posted by "Michael Gerz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603360#action_12603360 ]
Michael Gerz commented on DERBY-3714:
-------------------------------------
Query plan for bad-performance query (Java 6). Have fun!
2008-06-08 10:41:00.781 GMT Thread[RMI TCP Connection(3)-192.168.1.33,5,RMI Runtime] (XID = 3462556), (SESSIONID = 0), select logevent0_.ID as ID7_, logevent0_.INDEX_SENT as INDEX2_7_, logevent0_.TEST_RUN_ID as TEST3_7_, logevent0_.SENDER as SENDER7_, logevent0_.TIME_SENT as TIME5_7_, logevent0_.MESSAGE as MESSAGE8_, logevent0_.SEVERITY as SEVERITY8_, logevent0_.SERIALIZED_CONTENT as SERIALIZED1_11_, logevent0_.RECEIVER as RECEIVER11_, logevent0_.TIME_RECEIVED as TIME3_11_, logevent0_.INDEX_RECEIVED as INDEX4_11_, logevent0_.ACTION_REQUEST as ACTION1_13_, logevent0_.ACTION_REPLY as ACTION1_14_, logevent0_.clazz_ as clazz_ from ( select nullif('x','x') as RECEIVER, TIME_SENT, SEVERITY, nullif('2000-1-1 00:00:00','2000-1-1 00:00:00') as TIME_RECEIVED, ID, SENDER, MESSAGE, TEST_RUN_ID, nullif('x','x') as SERIALIZED_CONTENT, INDEX_SENT, nullif(0,0) as INDEX_RECEIVED, nullif('x','x') as ACTION_REQUEST, nullif('x','x') as ACTION_REPLY, 2 as clazz_ from USER_LOG_EVENT union all select nullif('x','x') as RECEIVER, TIME_SENT, SEVERITY, nullif('2000-1-1 00:00:00','2000-1-1 00:00:00') as TIME_RECEIVED, ID, SENDER, MESSAGE, TEST_RUN_ID, nullif('x','x') as SERIALIZED_CONTENT, INDEX_SENT, nullif(0,0) as INDEX_RECEIVED, nullif('x','x') as ACTION_REQUEST, nullif('x','x') as ACTION_REPLY, 3 as clazz_ from DEVEL_LOG_EVENT union all select nullif('x','x') as RECEIVER, TIME_SENT, nullif('x','x') as SEVERITY, nullif('2000-1-1 00:00:00','2000-1-1 00:00:00') as TIME_RECEIVED, ID, SENDER, nullif('x','x') as MESSAGE, TEST_RUN_ID, nullif('x','x') as SERIALIZED_CONTENT, INDEX_SENT, nullif(0,0) as INDEX_RECEIVED, ACTION_REQUEST, nullif('x','x') as ACTION_REPLY, 6 as clazz_ from ACTION_REQUEST_LOG_EVENT union all select nullif('x','x') as RECEIVER, TIME_SENT, nullif('x','x') as SEVERITY, nullif('2000-1-1 00:00:00','2000-1-1 00:00:00') as TIME_RECEIVED, ID, SENDER, nullif('x','x') as MESSAGE, TEST_RUN_ID, nullif('x','x') as SERIALIZED_CONTENT, INDEX_SENT, nullif(0,0) as INDEX_RECEIVED, nullif('x','x') as ACTION_REQUEST, ACTION_REPLY, 7 as clazz_ from ACTION_REPLY_LOG_EVENT union all select RECEIVER, TIME_SENT, nullif('x','x') as SEVERITY, TIME_RECEIVED, ID, SENDER, nullif('x','x') as MESSAGE, TEST_RUN_ID, SERIALIZED_CONTENT, INDEX_SENT, INDEX_RECEIVED, nullif('x','x') as ACTION_REQUEST, nullif('x','x') as ACTION_REPLY, 4 as clazz_ from DATA_FLOW_LOG_EVENT ) logevent0_ where logevent0_.TEST_RUN_ID=? ******* Project-Restrict ResultSet (22):
Number of opens = 1
Rows seen = 6
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 1112389,00
optimizer estimated cost: 4047453,42
Source result set:
Project-Restrict ResultSet (21):
Number of opens = 1
Rows seen = 1112364
Rows filtered = 1112358
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 1112389,00
optimizer estimated cost: 4047453,42
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 271467
Rows seen from the right = 840897
Rows returned = 1112364
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 1112389,00
optimizer estimated cost: 4047453,42
Left result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 271467
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 1112389,00
optimizer estimated cost: 4047453,42
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 270962
Rows seen from the right = 505
Rows returned = 271467
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 271487,00
optimizer estimated cost: 288473,90
Left result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 270962
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 271487,00
optimizer estimated cost: 288473,90
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 228943
Rows seen from the right = 42019
Rows returned = 270962
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 270977,00
optimizer estimated cost: 287878,82
Left result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 228943
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 270977,00
optimizer estimated cost: 287878,82
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 225313
Rows seen from the right = 3630
Rows returned = 228943
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 228953,00
optimizer estimated cost: 250730,46
Left result set:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 225313
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 225318,00
optimizer estimated cost: 243799,96
Source result set:
Table Scan ResultSet for USER_LOG_EVENT at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 225313
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=7
Number of pages visited=1634
Number of rows qualified=225313
Number of rows visited=225313
Scan type=heap
start position:
null stop position:
null qualifiers:
None
optimizer estimated row count: 225318,00
optimizer estimated cost: 243799,96
Right result set:
Project-Restrict ResultSet (8):
Number of opens = 1
Rows seen = 3630
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 3635,00
optimizer estimated cost: 6930,50
Source result set:
Table Scan ResultSet for DEVEL_LOG_EVENT at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 3630
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=7
Number of pages visited=46
Number of rows qualified=3630
Number of rows visited=3630
Scan type=heap
start position:
null stop position:
null qualifiers:
None
optimizer estimated row count: 3635,00
optimizer estimated cost: 6930,50
Right result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 42019
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 270977,00
optimizer estimated cost: 287878,82
Source result set:
Project-Restrict ResultSet (11):
Number of opens = 1
Rows seen = 42019
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 42024,00
optimizer estimated cost: 37148,36
Source result set:
Table Scan ResultSet for ACTION_REQUEST_LOG_EVENT at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 42019
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=6
Number of pages visited=243
Number of rows qualified=42019
Number of rows visited=42019
Scan type=heap
start position:
null stop position:
null qualifiers:
None
optimizer estimated row count: 42024,00
optimizer estimated cost: 37148,36
Right result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 505
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 271487,00
optimizer estimated cost: 288473,90
Source result set:
Project-Restrict ResultSet (15):
Number of opens = 1
Rows seen = 505
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 510,00
optimizer estimated cost: 595,08
Source result set:
Table Scan ResultSet for ACTION_REPLY_LOG_EVENT at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 505
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=6
Number of pages visited=4
Number of rows qualified=505
Number of rows visited=505
Scan type=heap
start position:
null stop position:
null qualifiers:
None
optimizer estimated row count: 510,00
optimizer estimated cost: 595,08
Right result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 840897
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 1112389,00
optimizer estimated cost: 4047453,42
Source result set:
Project-Restrict ResultSet (19):
Number of opens = 1
Rows seen = 840897
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 840902,00
optimizer estimated cost: 3758979,52
Source result set:
Table Scan ResultSet for DATA_FLOW_LOG_EVENT at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 840897
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=9
Number of pages visited=140486
Number of rows qualified=840897
Number of rows visited=840897
Scan type=heap
start position:
null stop position:
null qualifiers:
None
optimizer estimated row count: 840902,00
optimizer estimated cost: 3758979,52
> 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.
[jira] Commented: (DERBY-3714) Significant performance degradation
if Hibernate creates different order of attributes
Posted by "Michael Gerz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603367#action_12603367 ]
Michael Gerz commented on DERBY-3714:
-------------------------------------
Bad-performance query plan (for the simplified query above):
----------------------------------------------------------------
Booting Derby version The Apache Software Foundation - Apache Derby - 10.4.1.3 - (648739): instance a816c00e-011a-67ed-c85e-0000000ceb90
on database directory XXX
Database Class Loader started - derby.database.classpath=''
2008-06-08 11:27:12.406 GMT Thread[main,5,main] (XID = 3462628), (SESSIONID = 0), 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=16188523 ******* Project-Restrict ResultSet (9):
Number of opens = 1
Rows seen = 33
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 225417,00
optimizer estimated cost: 53055,62
Source result set:
Project-Restrict ResultSet (8):
Number of opens = 1
Rows seen = 225338
Rows filtered = 225305
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 225417,00
optimizer estimated cost: 53055,62
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 225313
Rows seen from the right = 25
Rows returned = 225338
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 225417,00
optimizer estimated cost: 53055,62
Left result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 225313
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 225417,00
optimizer estimated cost: 53055,62
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 225313
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 225318,00
optimizer estimated cost: 52493,51
Source result set:
Index Scan ResultSet for USER_LOG_EVENT using constraint FK64B9199FD4311151AB41192233BF62B at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 225313
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0}
Number of columns fetched=1
Number of deleted rows visited=0
Number of pages visited=1450
Number of rows qualified=225313
Number of rows visited=225313
Scan type=btree
Tree height=3
start position:
None
stop position:
None
qualifiers:
None
optimizer estimated row count: 225318,00
optimizer estimated cost: 52493,51
Right result set:
Project-Restrict ResultSet (7):
Number of opens = 1
Rows seen = 25
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 99,00
optimizer estimated cost: 562,12
Source result set:
Index Row to Base Row ResultSet for DATA_FLOW_LOG_EVENT:
Number of opens = 1
Rows seen = 25
Columns accessed from heap = {5}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 99,00
optimizer estimated cost: 562,12
Index Scan ResultSet for DATA_FLOW_LOG_EVENT using constraint FK64B9199FD431115378EE903 at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 25
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=25
Number of rows visited=26
Scan type=btree
Tree height=-1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 99,00
optimizer estimated cost: 562,12
> 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.
[jira] Commented: (DERBY-3714) Significant performance degradation
if Hibernate creates different order of attributes
Posted by "Michael Gerz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603361#action_12603361 ]
Michael Gerz commented on DERBY-3714:
-------------------------------------
Good-performance query plan (Java 5):
2008-06-08 10:47:29.656 GMT Thread[RMI TCP Connection(2)-192.168.1.33,5,RMI Runtime] (XID = 3462592), (SESSIONID = 0), select logevent0_.ID as ID7_, logevent0_.INDEX_SENT as INDEX2_7_, logevent0_.TEST_RUN_ID as TEST3_7_, logevent0_.SENDER as SENDER7_, logevent0_.TIME_SENT as TIME5_7_, logevent0_.MESSAGE as MESSAGE8_, logevent0_.SEVERITY as SEVERITY8_, logevent0_.SERIALIZED_CONTENT as SERIALIZED1_11_, logevent0_.RECEIVER as RECEIVER11_, logevent0_.TIME_RECEIVED as TIME3_11_, logevent0_.INDEX_RECEIVED as INDEX4_11_, logevent0_.ACTION_REQUEST as ACTION1_13_, logevent0_.ACTION_REPLY as ACTION1_14_, logevent0_.clazz_ as clazz_ from ( select TEST_RUN_ID, SENDER, MESSAGE, SEVERITY, nullif('x','x') as SERIALIZED_CONTENT, nullif('2000-1-1 00:00:00','2000-1-1 00:00:00') as TIME_RECEIVED, ID, nullif('x','x') as ACTION_REQUEST, TIME_SENT, nullif('x','x') as ACTION_REPLY, nullif('x','x') as RECEIVER, nullif(0,0) as INDEX_RECEIVED, INDEX_SENT, 2 as clazz_ from USER_LOG_EVENT union all select TEST_RUN_ID, SENDER, MESSAGE, SEVERITY, nullif('x','x') as SERIALIZED_CONTENT, nullif('2000-1-1 00:00:00','2000-1-1 00:00:00') as TIME_RECEIVED, ID, nullif('x','x') as ACTION_REQUEST, TIME_SENT, nullif('x','x') as ACTION_REPLY, nullif('x','x') as RECEIVER, nullif(0,0) as INDEX_RECEIVED, INDEX_SENT, 3 as clazz_ from DEVEL_LOG_EVENT union all select TEST_RUN_ID, SENDER, nullif('x','x') as MESSAGE, nullif('x','x') as SEVERITY, nullif('x','x') as SERIALIZED_CONTENT, nullif('2000-1-1 00:00:00','2000-1-1 00:00:00') as TIME_RECEIVED, ID, ACTION_REQUEST, TIME_SENT, nullif('x','x') as ACTION_REPLY, nullif('x','x') as RECEIVER, nullif(0,0) as INDEX_RECEIVED, INDEX_SENT, 6 as clazz_ from ACTION_REQUEST_LOG_EVENT union all select TEST_RUN_ID, SENDER, nullif('x','x') as MESSAGE, nullif('x','x') as SEVERITY, nullif('x','x') as SERIALIZED_CONTENT, nullif('2000-1-1 00:00:00','2000-1-1 00:00:00') as TIME_RECEIVED, ID, nullif('x','x') as ACTION_REQUEST, TIME_SENT, ACTION_REPLY, nullif('x','x') as RECEIVER, nullif(0,0) as INDEX_RECEIVED, INDEX_SENT, 7 as clazz_ from ACTION_REPLY_LOG_EVENT union all select TEST_RUN_ID, SENDER, nullif('x','x') as MESSAGE, nullif('x','x') as SEVERITY, SERIALIZED_CONTENT, TIME_RECEIVED, ID, nullif('x','x') as ACTION_REQUEST, TIME_SENT, nullif('x','x') as ACTION_REPLY, RECEIVER, INDEX_RECEIVED, INDEX_SENT, 4 as clazz_ from DATA_FLOW_LOG_EVENT ) logevent0_ where logevent0_.TEST_RUN_ID=? ******* Project-Restrict ResultSet (27):
Number of opens = 1
Rows seen = 6
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 109,81
optimizer estimated cost: 564,97
Source result set:
Project-Restrict ResultSet (26):
Number of opens = 1
Rows seen = 6
Rows filtered = 0
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 109,81
optimizer estimated cost: 564,97
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 6
Rows seen from the right = 0
Rows returned = 6
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 109,81
optimizer estimated cost: 564,97
Left result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 6
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 109,81
optimizer estimated cost: 564,97
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 5
Rows seen from the right = 1
Rows returned = 6
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 28,86
optimizer estimated cost: 94,85
Left result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 5
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 28,86
optimizer estimated cost: 94,85
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 2
Rows seen from the right = 3
Rows returned = 5
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 27,48
optimizer estimated cost: 87,79
Left result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 2
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 27,48
optimizer estimated cost: 87,79
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 2
Rows seen from the right = 0
Rows returned = 2
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 23,56
optimizer estimated cost: 73,32
Left result set:
Project-Restrict ResultSet (7):
Number of opens = 1
Rows seen = 2
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 20,88
optimizer estimated cost: 60,78
Source result set:
Index Row to Base Row ResultSet for USER_LOG_EVENT:
Number of opens = 1
Rows seen = 2
Columns accessed from heap = {0, 1, 3, 4, 5, 6}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 20,88
optimizer estimated cost: 60,78
Index Scan ResultSet for USER_LOG_EVENT using constraint FK64B9199FD4311151AB41192233BF62B at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 2
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=2
Number of rows visited=3
Scan type=btree
Tree height=3
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 20,88
optimizer estimated cost: 60,78
Right result set:
Project-Restrict ResultSet (10):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 2,68
optimizer estimated cost: 12,54
Source result set:
Index Row to Base Row ResultSet for DEVEL_LOG_EVENT:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0, 1, 3, 4, 5, 6}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 2,68
optimizer estimated cost: 12,54
Index Scan ResultSet for DEVEL_LOG_EVENT using constraint FK64B9199FD4311151AB4119231D4787C at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=2
Number of rows qualified=0
Number of rows visited=1
Scan type=btree
Tree height=2
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 2,68
optimizer estimated cost: 12,54
Right result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 3
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 27,48
optimizer estimated cost: 87,79
Source result set:
Project-Restrict ResultSet (14):
Number of opens = 1
Rows seen = 3
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 3,92
optimizer estimated cost: 14,47
Source result set:
Index Row to Base Row ResultSet for ACTION_REQUEST_LOG_EVENT:
Number of opens = 1
Rows seen = 3
Columns accessed from heap = {0, 1, 3, 4, 5}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 3,92
optimizer estimated cost: 14,47
Index Scan ResultSet for ACTION_REQUEST_LOG_EVENT using constraint FK64B9199FD431115B413BAD6F8960E06 at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 3
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=3
Number of rows visited=4
Scan type=btree
Tree height=3
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 3,92
optimizer estimated cost: 14,47
Right result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 28,86
optimizer estimated cost: 94,85
Source result set:
Project-Restrict ResultSet (19):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 1,38
optimizer estimated cost: 7,05
Source result set:
Index Row to Base Row ResultSet for ACTION_REPLY_LOG_EVENT:
Number of opens = 1
Rows seen = 1
Columns accessed from heap = {0, 1, 3, 4, 5}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 1,38
optimizer estimated cost: 7,05
Index Scan ResultSet for ACTION_REPLY_LOG_EVENT using constraint FK64B9199FD431115B413BAD656CF5661 at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=2
Number of rows qualified=1
Number of rows visited=2
Scan type=btree
Tree height=2
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 1,38
optimizer estimated cost: 7,05
Right result set:
Normalize ResultSet:
Number of opens = 1
Rows seen = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 109,81
optimizer estimated cost: 564,97
Source result set:
Project-Restrict ResultSet (24):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 80,95
optimizer estimated cost: 470,12
Source result set:
Index Row to Base Row ResultSet for DATA_FLOW_LOG_EVENT:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0, 1, 3, 4, 5, 6, 7, 8}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 80,95
optimizer estimated cost: 470,12
Index Scan ResultSet for DATA_FLOW_LOG_EVENT using constraint FK64B9199FD431115378EE903 at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=0
Number of rows visited=1
Scan type=btree
Tree height=3
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 80,95
optimizer estimated cost: 470,12
> 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.
[jira] Updated: (DERBY-3714) Significant performance degradation if
Hibernate creates different order of attributes
Posted by "Michael Gerz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Gerz updated DERBY-3714:
--------------------------------
Comment: was deleted
> 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.
[jira] Commented: (DERBY-3714) Significant performance degradation
if Hibernate creates different order of attributes
Posted by "Michael Gerz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603677#action_12603677 ]
Michael Gerz commented on DERBY-3714:
-------------------------------------
Unfortunately, I am not allowed to publish our database.
Maybe it helps to create a new database based on the schema above and fill it with some random values.
> 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.
[jira] Updated: (DERBY-3714) Significant performance degradation if
Hibernate creates different order of attributes
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-3714:
---------------------------------
Derby Categories: [Performance]
> 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
> 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.
[jira] Commented: (DERBY-3714) Significant performance degradation
if Hibernate creates different order of attributes
Posted by "Michael Gerz (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603359#action_12603359 ]
Michael Gerz commented on DERBY-3714:
-------------------------------------
Below please find the relevant part of our database schema. Please note that DATA_FLOW_LOG_EVENT has about 800,000 entries and USER_LOG_EVENT has about 225,000 entries.
CREATE TABLE "APP"."USER_LOG_EVENT" ("ID" BIGINT NOT NULL, "INDEX_SENT" INTEGER NOT NULL, "TEST_RUN_ID" BIGINT NOT NULL, "SENDER" VARCHAR(255) NOT NULL, "TIME_SENT" TIMESTAMP NOT NULL, "MESSAGE" VARCHAR(32672) NOT NULL, "SEVERITY" VARCHAR(255) NOT NULL);
ALTER TABLE "APP"."USER_LOG_EVENT" ADD CONSTRAINT "SQL070821150850480" PRIMARY KEY ("ID");
ALTER TABLE "APP"."USER_LOG_EVENT" ADD CONSTRAINT "FK64B9199FD4311151AB41192233BF62B" FOREIGN KEY ("TEST_RUN_ID") REFERENCES "APP"."TEST_RUN" ("ID") ON DELETE CASCADE ON UPDATE NO ACTION;
CREATE TABLE "APP"."DATA_FLOW_LOG_EVENT" ("ID" BIGINT NOT NULL, "INDEX_SENT" INTEGER NOT NULL, "TEST_RUN_ID" BIGINT NOT NULL, "SENDER" VARCHAR(255) NOT NULL, "TIME_SENT" TIMESTAMP NOT NULL, "RECEIVER" VARCHAR(255) NOT NULL, "TIME_RECEIVED" TIMESTAMP NOT NULL, "INDEX_RECEIVED" INTEGER NOT NULL, "SERIALIZED_CONTENT" VARCHAR(32672));
ALTER TABLE "APP"."DATA_FLOW_LOG_EVENT" ADD CONSTRAINT "SQL070821150848360" PRIMARY KEY ("ID");
ALTER TABLE "APP"."DATA_FLOW_LOG_EVENT" ADD CONSTRAINT "FK64B9199FD431115378EE903" FOREIGN KEY ("TEST_RUN_ID") REFERENCES "APP"."TEST_RUN" ("ID") ON DELETE CASCADE ON UPDATE NO ACTION;
CREATE TABLE "APP"."TEST_RUN" ("ID" BIGINT NOT NULL, "C2IS_ID" BIGINT, "TEST_CASE_ID" BIGINT, "STATUS" VARCHAR (255) FOR BIT DATA NOT NULL, "VERDICT" VARCHAR (255) FOR BIT DATA NOT NULL, "START_DATE" TIMESTAMP, "DURATION" BIGINT, "SUT_VERSION" VARCHAR(255), "TEST_CASE_VERSION" VARCHAR(255), "TEST_SYSTEM_VERSION" VARCHAR(255), "TEST_OPERATOR_NAME" VARCHAR(255), "COMMENTS" VARCHAR(2047));
ALTER TABLE "APP"."TEST_RUN" ADD CONSTRAINT "SQL070821150849890" PRIMARY KEY ("ID");
> 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.