[
https://issues.apache.org/jira/browse/DERBY-6859?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15113088#comment-15113088
]
somebody commented on DERBY-6859:
---------------------------------
That sounds like a reasonable theory. My understanding and experience with
every database I have worked with is that the database should hide this kind of
stuff
from the user and pick a near optimal (by estimates, query plan etc) solution
that performs reasonably fast. Unfortunately this is a big blocker for me for
using
Derby because since I'm running into these performance issues on only 2500 rows
with very simple tables, I have serious concerns that Derby won't scale to
larger
data sets. I'm hoping you guys can help me and hopefully make some
optimizations in the code or give me a solution that always performs fast
without having to do
workarounds like disabling foreign keys. Also please see my other post
https://issues.apache.org/jira/browse/DERBY-6858 as well as the issues might be
related.
Also please consult the stack overflow posts as well if you haven't already as
they have a bit of additional information (the issue descriptions are the same
but
there are more comments). The really concerning thing is that this slowdown
happens both on a platter hard drive and on my brand new SSD drive and the
behaviour
and timings are pretty much identical. The other concerning part is that this
is happening on a database with a total size of 3.5 MB. This size includes all
the
logs files and everything. Plase make this a priority if possible as I have
spent days and weeks troubleshooting this issue and nothing has worked and
nothing
makes sense. This is a complete showstopper for me.
> 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)
