Benefits of declaring foreign key links.

[Ed Kleban]

On 12/23/05 1:20 AM, "Ruslan Zasukhin" <sunshine at public.kherson.ua> wrote:

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

Meaning that when going from child to parent there is:

    One binary search to find the child record containing the value_Ptr
field, and then a second binary search to locate the parent record
containing the matching key field (thus 2 indexed jumps)

instead of:

    One binary search to find the child record containing the ObjectPtr
field (thus 1 indexed jump) and then a direct reference from the ObjectPtr
field to the desired record of the parent table.

?

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?

> Other dbs have also primary index.
> 

Um yes they do.  But I'm not sure why or how that is relevant.
 
>> What specific API calls are 4 times faster?  Or is this only realized with
>> SQL accesses?
> 
> In SQL this is JOIN.
> 

Ah, enough said.

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

Good point.  I knew there was a good reason I picked Valentina.   ;-)
 
>> 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.

In terms of how fast a lookup from an ObjectPtr field to a record is
compared with a lookup from a Ushort field is?  Yes.  I agree that the
performance difference is negligible unless the short fields mean you'll end
up paging less.  But I didn't choose the short fields for better
performance, I chose them for more efficient storage.
 
>> 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.

Yes. I'm aware of that.  And the reason behind these questions is to better
understand whether I should keep the UByte and UShort fields and continue to
use them as pointers manually along with declaring them as links so that VS
will see and use them....

Or whether there is some additional benefit to instead using ObjectPtr
fields instead because of some performance gain I am unaware of at the cost
of using a lot more storage.
 
So far my conclusion based on what I've read here is that there is no real
advantage to using an ObjectPtr over using a smaller-sized UByte or UShort
rid reference field AS IF it were a BytePtr or ShortPtr in my code, and
declaring it to be a foreign key link with the RecId of the parent table
being the key field.

That make sense?

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


Yes, but one would only need to use FindValue(anRid) to go from the parent
to the child.  When going from child to parent one can simply use the
ObjectPtr as the recID or one can instead use a UByte or UShort value_Ptr to
the RecId field of the parent table as if it were a BytePtr or ShortPtr.


Ok. 

Unless you find anything to disagree with in what I've said here, I think I
now the issues involved very well.

Thanks!
--Ed