[ http://issues.apache.org/jira/browse/DERBY-1205?page=comments#action_12374135 ]
Prasenjit Sarkar commented on DERBY-1205: ----------------------------------------- Sorry, db name is TPCDB, user name TPC, pwd whatever... > Unexpectedly very slow performance compared to DB2 > -------------------------------------------------- > > Key: DERBY-1205 > URL: http://issues.apache.org/jira/browse/DERBY-1205 > Project: Derby > Type: Bug > Components: Performance > Versions: 10.1.2.1, 10.1.2.3 > 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. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
