[
https://issues.apache.org/jira/browse/DERBY-1205?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dag H. Wanvik updated DERBY-1205:
---------------------------------
Derby Categories: [Performance]
> Unexpectedly very slow performance compared to DB2
> --------------------------------------------------
>
> Key: DERBY-1205
> URL: https://issues.apache.org/jira/browse/DERBY-1205
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.1.2.1, 10.1.3.1
> Environment: Windows XP SP2 4 GB RAM SCSI 10k disks dual Xeon 2.8 GHz
> Reporter: Prasenjit Sarkar
> Attachments: TPCDB.zip
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
