Performances and Foreign keys

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

Performances and Foreign keys

Bubu Bubu
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?

Thanks a lot
_______________________________________________
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: Performances and Foreign keys

Dominique Devienne
On Mon, Apr 24, 2017 at 2:31 PM, Bubu Bubu <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performances and Foreign keys

Bubu Bubu
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 <[hidden email]>:

> On Mon, Apr 24, 2017 at 2:31 PM, Bubu Bubu <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: Performances and Foreign keys

R Smith
In reply to this post by Bubu Bubu

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performances and Foreign keys

Clemens Ladisch
Bubu Bubu wrote:
> Can someone tell me if there can really be performance issues when one uses
> foreign keys in their database?

The documentation <http://www.sqlite.org/foreignkeys.html#fk_indexes>
warns against a case where some operations can be slow if you do not
have an index on the child key.

But if you have all suggested indexes, then all the checks done to
enforce (foreign key) constraints are simple index lookups.  These are
mostly harmless, unless the number of rows you are changing is so large
that all the individual operations add up to something noticeable.
(If you predict that that happens, you can simply disable foreign key
constraint checks.)


Regards,
Clemens
_______________________________________________
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: Performances and Foreign keys

Simon Slavin-3

On 24 Apr 2017, at 3:20pm, Clemens Ladisch <[hidden email]> wrote:

> Bubu Bubu wrote:
>> Can someone tell me if there can really be performance issues when one uses
>> foreign keys in their database?
>
> The documentation <http://www.sqlite.org/foreignkeys.html#fk_indexes>
> warns against a case where some operations can be slow if you do not
> have an index on the child key.

Yeah.  To do FOREIGN KEYs with speed you need two indexes: a UNIQUE one on the parent key columns and one on the child key columns.  To understand why you need the one on the child table, consider what should happen if you delete a row in the parent table.

> But if you have all suggested indexes, then all the checks done to
> enforce (foreign key) constraints are simple index lookups.  These are
> mostly harmless, unless the number of rows you are changing is so large
> that all the individual operations add up to something noticeable.
> (If you predict that that happens, you can simply disable foreign key
> constraint checks.)

Right.  As this post and others have mentioned, there will be a small increase in time taken, but this increase is tiny compared to any other way of ensuring integrity.  FOREIGN KEYs really are the most efficient way to do this.

Simon.
_______________________________________________
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: Performances and Foreign keys

John Found
In reply to this post by Bubu Bubu
Simply make some tests?

The experiment is the only measure for the truth.

Personally I am using SQLite for my project AsmBB (web forum software in assembly language). The project widely uses FK in order to provide DB consistency.

It was tested during several "slashdot effect" kind of events and
demonstrated very high performance.

So, in my experience using foreign keys does not degrade the performance noticeably.

Regards.


On Mon, 24 Apr 2017 14:31:32 +0200
Bubu Bubu <[hidden email]> 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?
>
> Thanks a lot
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
http://fresh.flatassembler.net
http://asm32.info
John Found <[hidden email]>
_______________________________________________
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: Performances and Foreign keys

David Raymond
Another situational space/speed helper I found for when you have a sparsely populated foreign key field is that the child key index can be a partial index and still work.

create table child (id primary key, parentID references parent, stuff);--parentID is nullable

I had cases where parentID would be populated only about 10% of the time or less and didn't like knowing I had an index 90% full of nulls. We found out that a partial index still works:

create index childOfParentNotNull on child (parentID) where parentID is not null;
--still used by the foreign key checker, 10% the size, quicker lookups etc.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of John Found
Sent: Monday, April 24, 2017 12:24 PM
To: [hidden email]
Subject: Re: [sqlite] Performances and Foreign keys

Simply make some tests?

The experiment is the only measure for the truth.

Personally I am using SQLite for my project AsmBB (web forum software in assembly language). The project widely uses FK in order to provide DB consistency.

It was tested during several "slashdot effect" kind of events and
demonstrated very high performance.

So, in my experience using foreign keys does not degrade the performance noticeably.

Regards.


On Mon, 24 Apr 2017 14:31:32 +0200
Bubu Bubu <[hidden email]> 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?
>
> Thanks a lot
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
http://fresh.flatassembler.net
http://asm32.info
John Found <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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: Performances and Foreign keys

Bubu Bubu
All of your feedbacks are really insightful, I deeply thank you guys, I'm
meeting with my boss later with all that stuff to discuss about. Great
community and thanks again

2017-04-24 18:58 GMT+02:00 David Raymond <[hidden email]>:

> Another situational space/speed helper I found for when you have a
> sparsely populated foreign key field is that the child key index can be a
> partial index and still work.
>
> create table child (id primary key, parentID references parent,
> stuff);--parentID is nullable
>
> I had cases where parentID would be populated only about 10% of the time
> or less and didn't like knowing I had an index 90% full of nulls. We found
> out that a partial index still works:
>
> create index childOfParentNotNull on child (parentID) where parentID is
> not null;
> --still used by the foreign key checker, 10% the size, quicker lookups etc.
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of John Found
> Sent: Monday, April 24, 2017 12:24 PM
> To: [hidden email]
> Subject: Re: [sqlite] Performances and Foreign keys
>
> Simply make some tests?
>
> The experiment is the only measure for the truth.
>
> Personally I am using SQLite for my project AsmBB (web forum software in
> assembly language). The project widely uses FK in order to provide DB
> consistency.
>
> It was tested during several "slashdot effect" kind of events and
> demonstrated very high performance.
>
> So, in my experience using foreign keys does not degrade the performance
> noticeably.
>
> Regards.
>
>
> On Mon, 24 Apr 2017 14:31:32 +0200
> Bubu Bubu <[hidden email]> 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?
> >
> > Thanks a lot
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found <[hidden email]>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users