db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Prasenjit Sarkar (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-1205) Unexpectedly very slow performance compared to DB2
Date Wed, 12 Apr 2006 04:15:20 GMT
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


Mime
View raw message