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
