On 10/03/2015 03:19 AM, Andrew Cunningham wrote: > > > SQLite uses a counter to enforce deferred foreign keys. Each time an > operation violates a constraint it increments that counter. > > If the counter is greater than 0, then for every operation that might > potentially correct the violation (which means virtually every > operation > on any table that is the parent or child in an FK constraint) it does > extra seeks to see if it really does. i.e. each time you insert > into the > parent table, it seeks within the child table to see if the insert > fixes > a foreign key violation. And if it does, the counter is decremented. > > So we know there is overhead, but I'm quite surprised that it is 1000x > slower. > > How large is the database? So large that these extra seeks could be > performing real IO (i.e. having to go all the way to the disk for > every > seek, not just finding the data in the OS cache)? > > > OK, that kinds of makes sense in explaining where the problem comes > from. I really do see an exponential growth in execution time > depending on how many INSERTS are made after the first FK violation > occurs. > > I have a very simple example that , for example, takes 0.2s with no FK > violations and 25s ( about 100x slower) when a single FK violation is > forced before I start doing the "mass creation". > > This is a small database, creating 500 parent objects, each with 50 > child objects. > If I change (double) the number of objects to 1000 the time changes to > 0.4s and 100s. It looks like an N^2 problem. So you can see that very > quickly my factor of 1000x is very easy to reach...... > > > When FK checking is in DEFERRED mode, the implication is that no FK > checking is made until the COMMIT. > > That's very useful, as often it is impossible to make the > modifications done in the correct order to satisfy all the > FK=IMMEDIATE constraints, even though the end result is a > "referentially correct" database. > > So what you are saying above makes DEFERRED mode a lot less useful. > > DISCLAIMER: I am use ODB ( the awesome C++/ORM tool) to do these tests. > > >
Hi, Ok. My first guess is that you are missing the "suggested index" on the child table. Without this, if there is a foreign key violation in the db, each insert on the parent table will cause a linear scan of the entire child table. With the index, it's just a simple seek. https://www.sqlite.org/foreignkeys.html#fk_indexes If you're unsure, open your database using the command line tool and dump the schema using the ".schema" command. Post that here and someone should be able to tell you if you're missing an index or not. Dan.
