[jira] [Created] (DERBY-6859) Apache Derby Database Performance slow with a foreign key

["somebody (JIRA)" <ji...@apache.org>]

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: 10.12.1.1, 10.11.1.1
         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 negative?

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

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

Thanks

Also please see:
http://stackoverflow.com/questions/34765186/apache-derby-database-performance-slow-with-a-foreign-key



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)