Derby
  1. Derby
  2. DERBY-3714

Significant performance degradation if Hibernate creates different order of attributes

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Critical Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.3.2.1, 10.4.1.3
    • Fix Version/s: None
    • Component/s: SQL
    • Environment:
      Windows XP with Java 6u5 (JavaDB de-installed!), various hardware (single + dual core processors)
    • Urgency:
      Normal
    • Bug behavior facts:
      Performance

      Description

      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

        Activity

          People

          • Assignee:
            Unassigned
            Reporter:
            Michael Gerz
          • Votes:
            1 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development