db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6859) Apache Derby Database Performance slow with a foreign key
Date Fri, 22 Jan 2016 04:41:39 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6859?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15111903#comment-15111903

Rick Hillegas commented on DERBY-6859:

One theory (I haven't verified this by studying the code): When the foreign key is enabled,
the update statement may cause a separate probe for each tuple which is changed in the foreign
table. When the foreign key is disabled and then re-enabled after the update, the foreign
key relationship may be checked via a join which involves bulk reads. Just a theory.

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

View raw message