[
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.
