Benefits of declaring foreign key links.

[Ruslan Zasukhin]

On 12/23/05 6:25 PM, "Ed Kleban" <Ed at Kleban.com> wrote:

> If so then I would have phrased this differently and said the speed
> improvement comes from having a direct reference from the child record to
> the parent record instead of having to do an indexed jump.
> 
> Or have I misunderstood your point and gotten this wrong?

Wrong.

Issues comes from even SINGLE TABLE.

I believe this is described with pictures in Valentina Kernel.pdf
Look on ObjectPtr description pages.

Main point is:

RDBMS have 

    * primary key field. It have PRIMARY index.

    * index of other field map
            { f2_value, pk_value }

       - so you search index f2, then you need jump into pk index.
        
       - to optimize this, many RDBMS use hidden _rowid field,
            so if you choose PK as string, you will not keep strings
            in f2 index. But you still have 2 index jumps.

Valentina instead have
            { f2_value, recid }

            after search in f2 index you KNOW recid of record.  STOP
            


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]