db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "somebody (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-6859) Apache Derby Database Performance slow with a foreign key
Date Thu, 21 Jan 2016 11:05:39 GMT
somebody created DERBY-6859:

             Summary: Apache Derby Database Performance slow with a foreign key
                 Key: DERBY-6859
                 URL: https://issues.apache.org/jira/browse/DERBY-6859
             Project: Derby
          Issue Type: Bug
    Affects Versions:,
         Environment: windows 7 64 bit
            Reporter: somebody
            Priority: Blocker

I have the following schema: TableA Name

TableB Name

A foreign key from TableB.Name to TableA.Name

I insert 2500 records into table B and one record into TableA.

I then issue the update statement "update TableB set Name = 'new' where Name = 'old'". I then
issue the update statement to change TableA.Name from 'old' to 'new' in the same transaction.

I put timing code just around the update statement for TableB and discovered that with the
foreign key it taks 9.7 seconds to perform the update statement. Without the foreign key it
takes about 300 milliseconds. Also if i have the FK and then disable it right before the update
of TableB and then re-enable it right after the whole test is just as fast as not having the
FK in the first place.

The query plan is completely identical in the above scenarios except for the timings. the
timings vary from about 11 to 33 or so in some cases, and the number of indexes goes up by
1 when I have the FK.

I also discovered that having the FK the whole time and adding an index on Name and one of
the boolean columns that isn't even used at all in any of the queries also results in the
fast update statement of about 300 milliseconds - 400 milliseconds.

Also removing some of the columns from TableB that arent't part of the FK or PK of any other
table (ie. columns that aren't even used in any of the queries aside from the initial inserts)
improves the speed again of the update.

I have tried pretty much everything and I am not sure why this behaviour is so bad in Derby.
I'm only dealing with 2500 rows in the entire table. It should not be this slow to update
an index. I tried analyzing the query plans (which are identical except for slower timing
stats), I tried forcing updating of statistics. Forcing flushing data to disk for Derby. I
tried using an external database browser and running the same update statements (2 mentioned
above) and got the same slowness. I also tried many other things.

The behaviour is always consistent with the same test scenarios (eg. disable foreign key before
update or not) but it's completely illogical. Why would updating an FK with only 2500 rows
be that slow, this makes the database unusable. Why would creating an index on the Name column
and a completely unrelated column improve performance.

Also the "Execute Time: " in the query plan right near the beginning is always a large negative
number yet the timestamps below showing the total compile and execute times are correct for
what I'm observing If i subtract them off. Is something wrong with Derby? Why is the time

I'm also on the latest version of Apache Derby, which I only tried as a diagnostic
step because I was on when i found this behaviour.

I would really appreciate a lot of help on this one.


Also please see:

This message was sent by Atlassian JIRA

View raw message