On 2017/04/24 2:31 PM, Bubu Bubu wrote:
Hi everyone,
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?
YES, But...
That very much depends on what you mean by "performance issues". Does it
come at a processing cost? Yes of course, like everything else, but is
very cheap by comparison.
For everything in life that you do, you pay a cost in energy. Whether
you simply look to your left - that costs energy - but you may not mind
that or worry about it, and so can look both left and right often
without fearing the energy it saps from you - plus it becomes
ridiculously cheap when you consider the benefits, especially when
crossing busy roads.
A foreign key is simply a data integrity tool, Set-theory doesn't demand
it - not all data are equal, some may justify it, others may not. (I
certainly don't always use them).
I think I can safely state that any performance degradation due to a
foreign-key check pales in comparison to the advantages in the cases
where they are needed. To put this into perspective, SQLite has improved
in speed for bog-standard database operations since 3.6.19 by a factor
that completely dwarfs any speed-penalty that a foreign-key check might
impose. Another point that might be pertinent is that lots of DBs all
over the world use foreign keys - without much shunning it for
performance problems (which is why the lack of google results - though
there are some).
Let me caution about one situation that do need special consideration. A
foreign key constraint requires a unique index (because you can't have a
relation to a plural item) and to maintain a unique index on a really
large table (high record count) *IS* actually somewhat expensive and
adds to the overhead of checking the key on most operations, and if that
index is not useful for anything other than to maintain the foreign key
relation, then perhaps you may need to reconsider - but that is a very
remote case.
Also, if you do experience very slow inserts, you could turn the foreign
keys off in most DBs and run large inserts without them, though that
obviates the point a bit. That said, gratuitously adding hundreds of
foreign keys is also bad. It's like everything in life - Use it if it
serves the purpose, but use only where needed and only as much as is needed.
I realize the above is not very specific, but it's really hard to give a
precise answer on a value-based concept. Some people simply won't wear a
seat-belt because it is too much effort - others would wear it even if
took half-an-hour to strap into. It's the same with foreign keys.
I would say this though: Foreign keys work fine; Your debates with your
boss should be about when to use them and when not based on the
system/data needs - It shouldn't be about whether the "foreign keys"
functionality itself is a valid tool or not.
Pardon my rattling on a bit...
Good luck :)
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
