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.