[
https://issues.apache.org/jira/browse/DERBY-6859?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6859:
---------------------------------
Urgency: Normal (was: Blocker)
> 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.11.1.1, 10.12.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)
