On 10/01/2015 10:32 PM, Andrew Cunningham wrote: > I have noticed that when FOREIGN KEY is in DEFERRED mode and a FOREIGN KEY > violation occurs, SQLite will continue to run, but performance slows down > by about 1000x. Breaking into the code shows that it seems to be > continually re-reading the database. When I finally get to COMMIT , an > exception is thrown, but this can take hours , literally as I have a single > global transactions. > > Of course the solution is to debug the problem by setting the mode to > IMMEDIATE, detecting the issue at the source, then returning to DEFERRED. > > But the '1000x slowdown' behavior is an interesting quirk I don't really > understand. >
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)? Thanks, Dan.
