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

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development