You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Prasenjit Sarkar (JIRA)" <de...@db.apache.org> on 2006/04/12 06:15:20 UTC
[jira] Created: (DERBY-1205) Unexpectedly very slow performance
compared to DB2
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
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
[jira] Updated: (DERBY-1205) Unexpectedly very slow performance
compared to DB2
Posted by "Prasenjit Sarkar (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1205?page=all ]
Prasenjit Sarkar updated DERBY-1205:
------------------------------------
Attachment: TPCDB.zip
This is the copy of the database where the query should be run to see the performance issues
> 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
[jira] Commented: (DERBY-1205) Unexpectedly very slow performance
compared to DB2
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1205?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12710652#action_12710652 ]
Kristian Waagan commented on DERBY-1205:
----------------------------------------
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.
> Unexpectedly very slow performance compared to DB2
> --------------------------------------------------
>
> Key: DERBY-1205
> URL: https://issues.apache.org/jira/browse/DERBY-1205
> Project: Derby
> Issue Type: Bug
> Components: Performance
> 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.
[jira] Closed: (DERBY-1205) Unexpectedly very slow performance
compared to DB2
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1205?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kristian Waagan closed DERBY-1205.
----------------------------------
Resolution: Cannot Reproduce
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.
> 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.
[jira] Updated: (DERBY-1205) Unexpectedly very slow performance
compared to DB2
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-1205) Unexpectedly very slow performance
compared to DB2
Posted by "Prasenjit Sarkar (JIRA)" <de...@db.apache.org>.
[ 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