Re: SQLite slows by a factor of 1000 after foreign key

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slows by a factor of 1000 after foreign key

KlaasV
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

When any violation occurs it's wise to use '.bail on' (ref. https://www.sqlite.org/cli.html )
and eventually abort the application (creating a core dump if necessary to get an investigation done by a developer) as soon as it stops to run for this reason.

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - LinkedIn 437429414
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Klaas "Z4us" V, MetaDBA at InnocentIsArt.EU
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slows by a factor of 1000 after foreign key

Andrew Cunningham
Hi,
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.


http://cs.stanford.edu/people/widom/cs145/sqlite/SQLiteRefIntegrity.html
refers to something similar "Bulk-Loading
<http://cs.stanford.edu/people/widom/cs145/sqlite/SQLiteLoad.html> into a
SQLite database while checking referential integrity is very, very slow"
Note sure if that is related.




On Thu, Oct 1, 2015 at 9:36 AM, Klaas V <[hidden email]> wrote:

> 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
>
> When any violation occurs it's wise to use '.bail on' (ref.
> https://www.sqlite.org/cli.html )
> and eventually abort the application (creating a core dump if necessary to
> get an investigation done by a developer) as soon as it stops to run for
> this reason.
>
> Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
> Klaas `Z4us` V  - LinkedIn 437429414
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite slows by a factor of 1000 after foreign key

Simon Slavin-3

On 1 Oct 2015, at 6:36pm, Andrew Cunningham <[hidden email]> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users