On 1 Oct 2015, at 6:36pm, Andrew Cunningham <sqlite at a-cunningham.com> wrote:
> A FOREIGN KEY violation that occurs in IMMEDIATE mode, though it causes an > error, is often not actually a real error. Sometimes the "VIOLATION" (of > referential integrity) is corrected at a later point ( order of operations > is important). > Assuming the VIOLATION is corrected , then in DEFERRED mode at the time of > COMMIT (when the FOREIGN KEY constraints are checked ) no error will be > flagged as the DB is referential valid. > > > But I am trying to understand the reason for the 1000x performance hit. > SQlite continues to run, but seems to be in some perpetual state of > confusion after the 'silent' FOREIGN KEY violations occur in DEFERRED mode. One thing that might cause a performance hit is if every command after the violation results in the violation error. For every command until the end of the transaction SQLite has to notice the error, set a flag that the error has occurred, remind itself that the transaction is now invalid. This would add a little time to every single command. Not really enough to account for a 1000-fold hit bit it might be something similar to what's going on. Simon.
