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)
