Subject Re: AW: [firebird-support] Query optimization help
Author Alexandre Benson Smith
Alexander Gräf wrote:

>A FK is a declarative way of defining relationships between tables. For example, I'm using an ORM-tool which automatically maps relations between tables to 1:n and n:m class collections.
Hi, Alexander !

I agree with you, I prefer declarative constraints than procedural
implementation of the same.

BUT, If I encounter a place in a critical section of my app that are
suffering because of this limitation on FB, I will drop the FK and
implement it via triggers. If this is critical, then I need to do
something about. I think that what Helen is telling is how it could be
handle such situations, the old-timers FB users know this limitations
and tend to use on of the follwing approachs:
1.) Drop FK constraint and use Triggers to enforce it
2.) When find a query that is affected by the bad index add a +0 to it
to "disable" it.
3.) Force the plan on problematic queries

Instead of forcing the plan (that will disable completely the optimizer)
I prefer to add +0 or '' to the affected columns, to make the optimzer
unable to chosse the bad indices.

The sollutions could be IMHO:
1.) Make the index creation an optional step on the FK constraint (maybe
the default one, but the user can override it)
2.) Make the optimizer smarter to handle such situations

One step ahead was the FB 2.0 new index structure that handles nicely
index with lots of duplicates, so, let's wait for another step, the
engine is getting better each day, one day, bad index selectivity will
be a forgotten problem, until this time, let's look on how will could
handle it, anyone choose what he considers the best option...

see you !


Alexandre Benson Smith
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.13 - Release Date: 16/04/2005