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