Derby
  1. Derby
  2. DERBY-1205

Unexpectedly very slow performance compared to DB2

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 10.1.2.1, 10.1.3.1
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      Windows XP SP2 4 GB RAM SCSI 10k disks dual Xeon 2.8 GHz
    • Bug behavior facts:
      Performance

      Description

      We are porting a commercial application from DB2 to Derby and are seeing performance problems that cannot be explained easily. The following queries are very slow on Derby while they take < 2 seconds on DB2.

      Query 1: (takes 6.5 minutes)

      SELECT OTHER2PORT.OTHER_ID OTHER_ID,
      OTHER2PORT.PREFIX_ID OTHER_PREFIX_ID, PORT2PORT.PORT_ID2 OTHER_PORT_ID,
      PORT2PORT.PORT_ID1 SWITCH_PORT_ID, PORT2PORT.PORT2PORT_ID PORT2PORT_ID,
      PORT2PORT.DETECTABLE DETECTABLE, SWITCH.PREFIX_ID SWITCH_PREFIX_ID,
      SWITCH.SWITCH_ID SWITCH_ID FROM T_VIEW_VSWITCH SWITCH left outer
      join T_RES_SWITCH SWITCH2 on SWITCH.SWITCH_WWN = SWITCH2.PARENT_SWITCH_WWN,
      T_VIEW_VSWITCH2PORT SWITCH2PORT, T_VIEW_PORT2PORT PORT2PORT,
      T_VIEW_VOTHER2PORT OTHER2PORT WHERE SWITCH2.SWITCH_WWN IS NULL and
      SWITCH.PREFIX_ID = SWITCH2PORT.PREFIX_ID and SWITCH.SWITCH_ID = SWITCH2PORT.SWITCH_ID
      and SWITCH2PORT.PORT_ID = PORT2PORT.PORT_ID1 and PORT2PORT.PORT_ID2
      = OTHER2PORT.PORT_ID

      Query 2: (takes 20 seconds)

      SELECT SWITCH.PREFIX_ID SWITCH_PREFIX_ID,
      SWITCH.SWITCH_ID SWITCH_ID FROM T_VIEW_VSWITCH SWITCH left outer
      join T_RES_SWITCH SWITCH2 on SWITCH.SWITCH_WWN = SWITCH2.PARENT_SWITCH_WWN,
      T_VIEW_VSWITCH2PORT SWITCH2PORT where SWITCH.PREFIX_ID = SWITCH2PORT.PREFIX_ID and SWITCH.SWITCH_ID = SWITCH2PORT.SWITCH_ID
      and SWITCH2.SWITCH_WWN IS NULL

      I cant find a way to attach the db to the issue - but can ship a copy to the developer assigned.

      1. TPCDB.zip
        740 kB
        Prasenjit Sarkar

        Activity

        Hide
        Kristian Waagan added a comment -

        I tried running this with 10.6.
        The first query took ~10 seconds from ij, the subsequent run took less than half a second.
        If I changed the query slightly, i.e. added some whitespace to the end, the query took ~8 seconds, which suggests that the optimizer is spending some time to generate a plan for the query.
        This serves as a reminder to use prepared statements

        I'm unable to reproduce a run-time of more than six minutes.

        Closing this issue, reopen if more data suggesting a bug in Derby is presented.

        Show
        Kristian Waagan added a comment - I tried running this with 10.6. The first query took ~10 seconds from ij, the subsequent run took less than half a second. If I changed the query slightly, i.e. added some whitespace to the end, the query took ~8 seconds, which suggests that the optimizer is spending some time to generate a plan for the query. This serves as a reminder to use prepared statements I'm unable to reproduce a run-time of more than six minutes. Closing this issue, reopen if more data suggesting a bug in Derby is presented.
        Hide
        Kristian Waagan added a comment -

        With trunk I see more reasonable execution times for these queries now.
        On a Toshiba Tecra M9 2.2 GHz (dual core, Intel Centrino vPro), both queries run in between 1 and 2 seconds on a warmed up db (i.e. data in cache).
        The initial queries took ~14 seconds and ~6 seconds (both on newly booted databases).

        Timed in ij using "ELAPSEDTIME ON", copied queries from description above.

        Show
        Kristian Waagan added a comment - With trunk I see more reasonable execution times for these queries now. On a Toshiba Tecra M9 2.2 GHz (dual core, Intel Centrino vPro), both queries run in between 1 and 2 seconds on a warmed up db (i.e. data in cache). The initial queries took ~14 seconds and ~6 seconds (both on newly booted databases). Timed in ij using "ELAPSEDTIME ON", copied queries from description above.
        Hide
        Prasenjit Sarkar added a comment -

        Sorry, db name is TPCDB, user name TPC, pwd whatever...

        Show
        Prasenjit Sarkar added a comment - Sorry, db name is TPCDB, user name TPC, pwd whatever...
        Hide
        Prasenjit Sarkar added a comment -

        This is the copy of the database where the query should be run to see the performance issues

        Show
        Prasenjit Sarkar added a comment - This is the copy of the database where the query should be run to see the performance issues

          People

          • Assignee:
            Unassigned
            Reporter:
            Prasenjit Sarkar
          • Votes:
            2 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development