Benefits of declaring foreign key links.

[Ruslan Zasukhin]

On 12/23/05 5:06 AM, "Ed Kleban" <Ed at Kleban.com> wrote:

Hi Ed,
 
> Page 13 of the Kernel.pdf doc states: " ObjectPtr field works in 4
> times faster than FK based on ulong KEY and PTR fields."
> 
> Why is that?  
> 
> Where does this speed improvement come from?

 one index jump instead of 2.  Other dbs have also primary index.

 
> What specific API calls are 4 times faster?  Or is this only realized with
> SQL accesses?

In SQL this is JOIN.

In API -- other dbs do not have API level, Ed. So we cannot compare here.
 

> I currently have several fields with foreign key records that stored as
> Byte, UShort, or UMedium because V2 does not yet support BytePtr, ShortPtr,
> or MediumPtr.  But considering the advertised performance improvement, I am
> tempted to just make these all (long)  ObjectPtr fields.

Hard to say here. I did not test such combination.

I think difference will start visible only for db which have size bigger of
your RAM.
 
> The other reason I am tempted to do this is that I see the advantage of
> having these links explicitly declared so that Valentina Studio will show
> the related links in the Data Browser.

Right. 

Hmm, not right. IF you have also create FOREIGN KEY on your byte fields,
then Vstudio also will show links.

> Declaring them as ObjectPtr will do
> this automatically, although I'll probably want to make sure I name the
> links as well for clarity.

> Right now since I'm just using FindValue(anRid),
> VS is unaware they are links at all.  I'm wondering what other performance
> benefits there may be to declaring them as links.

When yo move from child to parent now:

    you take byte value. Now you need do index search
    to find recid of parent.

With ObjectPtr you simply get that recID.



-- 
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]