Ok, I'll test with and without FKs. As you said, since one can turn them on and off, maybe there is something to do there.
Thanks for your answer 2017-04-24 14:52 GMT+02:00 Dominique Devienne <ddevie...@gmail.com>: > On Mon, Apr 24, 2017 at 2:31 PM, Bubu Bubu <sivada...@gmail.com> wrote: > > > Foreign keys have been implemented in sqlite since 3.6.19. My boss has > > always been reluctant to use this mechanism in our development under the > > pretext of performance loss. He told me he read that somewhere once, but > he > > can't recall precisely the reasons that lead him think that. > > > > I've read 3.6.19 release note and try to find info that cover that issue > > without finding any. > > > > Can someone tell me if there can really be performance issues when one > uses > > foreign keys in their database? > > > > There's always a cost to FKs, since inserting a child row implies checking > the PK > of the parent row(s) existence, a check that's not necessary if FKs are > disabled. > > You can't make it faster than not doing it at all. That overhead really > depends on > what you value most, performance at all cost, or enforced data integrity. > > SQLite is flexible, with FKs disabled by default. So always design with > FKs, but > do not necessarily always turn them ON. You get your as-fast-as-possible > inserts, > when it's OFF, but you can at least check integrity "after-the-fact" via > pragmas. > > I often decide myself data integrity is more important than speed, but > that's > a personal trade-off. Measure your perf with FKs both ON and OFF, and see > for yourself. > > One caveats of FKs ON is with deletes, if you don't index your child FK > columns, > and you have ON DELETE CASCADE, because that implies a FULL SCAN of > child tables (to locate child rows that need deleting when the parent gets > deleted). > But there's a new way to find those, in the official shell I think. > (or is that part of the new optimize pragma?). --DD > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
