db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kristian Waagan (JIRA)" <j...@apache.org>
Subject [jira] Closed: (DERBY-1205) Unexpectedly very slow performance compared to DB2
Date Wed, 28 Apr 2010 15:34:39 GMT

     [ 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.


Mime
View raw message