| Subject | Re: Insertion speed |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2005-02-14T20:22:17Z |
--- In firebird-support@yahoogroups.com, "Todd Pinel" wrote:
drop considerably more (either each issue has too few customers or
each customer buy too many copies!).
made the optimizer "think". But I can give you my 'novice version':
You have an index on issue and one on buyer. Each issue sells
considerably more copies than each buyer buys. Hence, if you locate
one issue in its index, you still have a lot of buyers to choose from.
However, if you go the opposite way finding the buyer in its index,
then he has bought so few issues that going through all of them to
find the correct issue isn't very demanding.
When the indexes are approximately equally selective, then you may
benefit from using both indexes, when you have one selective and one
non-selective, then preventing use of the non-selective one may
greatly affect execution speed (it wouldn't surprise me if - in
extreme cases - it was possible to improve from hours to subsecond,
though I've never experienced that dramatic an improvement)
InterBase.
primarily when the referenced table rarely change. Indexes ought to be
good, it's the optimizer that isn't perfect (although it is greatly
improved since Firebird 1.0, which I'm still on). Not knowing your
tables, I think I'd recommend you to keep your foreign keys and rather
use the trick I showed you whenever appropriate.
Set
> Wow, you were right the execute time dropped from ~40s to ~20s. I'mNice to hear you're happy, I was actually disappointed that it didn't
> happy with that!
drop considerably more (either each issue has too few customers or
each customer buy too many copies!).
> I have some other things I need to tweak before I get too hung up onArno is the expert able to tell you what's going on and exactly how he
> performance like this. This was great! Now could someone explain
> why this worked the way it did?;)
made the optimizer "think". But I can give you my 'novice version':
You have an index on issue and one on buyer. Each issue sells
considerably more copies than each buyer buys. Hence, if you locate
one issue in its index, you still have a lot of buyers to choose from.
However, if you go the opposite way finding the buyer in its index,
then he has bought so few issues that going through all of them to
find the correct issue isn't very demanding.
When the indexes are approximately equally selective, then you may
benefit from using both indexes, when you have one selective and one
non-selective, then preventing use of the non-selective one may
greatly affect execution speed (it wouldn't surprise me if - in
extreme cases - it was possible to improve from hours to subsecond,
though I've never experienced that dramatic an improvement)
> Also I have been trying to be very selective in where I place my ownGood, I started off creating too many indexes when I first began using
> indexes, I've found its only helped me in a couple select places and
> I can justify them due to the performance benefit they've given.
InterBase.
> I have assumed while building my DB that as I create foreign keysSometimes it may be justified to replace foreign keys with triggers,
> between tables indices are created automatically. The indices you
> see in use in the plan below are indices created from adding foreign
> keys, should I take them out? (I've always assumed an index was
> good, with negative performance if you went overboard with them)
primarily when the referenced table rarely change. Indexes ought to be
good, it's the optimizer that isn't perfect (although it is greatly
improved since Firebird 1.0, which I'm still on). Not knowing your
tables, I think I'd recommend you to keep your foreign keys and rather
use the trick I showed you whenever appropriate.
Set