Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Cannot Reproduce
-
10.1.2.1, 10.1.3.1
-
None
-
None
-
None
-
Windows XP SP2 4 GB RAM SCSI 10k disks dual Xeon 2.8 GHz
-
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.