Fine Tuning

[Ed Kleban]

On 11/18/05 12:53 AM, "Ruslan Zasukhin" <sunshine at public.kherson.ua> wrote:

> On 11/18/05 8:41 AM, "Ed Kleban" <Ed at Kleban.com> wrote:
> 
>> Howdy Ruslan,
>> 
>> I've been fine tuning my schema and think I may have found a performance
>> issue I hadn't considered before.
>> 
>> Since every column is stored as a separate table, I started trying to
>> combine some of the fields in parallel side tables into my main one.
>> Messier on the page, but has the benefit of direct access indexing rather
>> than the overhead of a binary search when you need access to those fields.
>> 
>> Then I realized, "Doh!" Every time I do...
>> 
>>     "aTable.recID = aRecordNumberOfInterest"
>> 
>> ...Valentina is going to lookup and load the value of every one of those
>> columns for me to access, whether I need them or not.  Right?
> 
> Right. Cache can compensate a lots this.
> And we going to improve this using lazy reading.

What is this?  Ah, read on need.  I get it.  Yes that would be a great thing
to have.

>  
>> Double Doh!  So then I went the opposite direction and pulled out every
>> single column I didn't expect to need upon virtually every access into a
>> separate parallel table for occasional access at the cost of a binary
>> search.
>> 
>> Better.
> 
> In fact, if you use SQL and cursors, then you can SELECT e.g. 3 fields from
> 20. Then cursor will iterate 20/3 = 7 times faster.

Ah, very good point.  I hadn't thought about that.  I was intentionally
avoiding SQL accesses in favor of the native API based on presumed superior
efficiency.  But yes, I see now.  SQL has the expressive power to limit the
work done if your SQL parser is smart enough to code it well -- which you've
just told me Valentina's is.  Makes sense.  You'd want to optimize the heck
out of this since that's how the majority of the world would access the
tables.

Ok, Delving into SQL will be next on my study list.  I believe in a previous
email I saw you comment that you cant do a search on cursor results, but
rather you can build a new table if need be.  My intended approach had been
to do Find extracts to VArraySets; but if if SQL provides me either with
that or the ability to make interim temporary tables, either of those should
be just dandy.
 
>  
>> But now I perceive another opportunity that we never discussed.   Yet
>> another efficiency tradeoff that may well be the decisive argument in your
>> favor for binary links.  If the relation is in a binary link rather than a
>> table field, I don't incur the overhead of pre-loading that link value every
>> time I make a record current whether or not I need to use that link.
> 
> Yes good point.
> 

Not just a good point, it may in my particular case be the killer
justification for using binary links occasionally, because I've seen
essentially zero benefit so far for my needs.

>> If the info is in a binary link instead, it doesn't even get looked at unless
>> I really need it.
>> 
>> That correct?
> 
> 100%.
> 
> At least until we get luck to add lazy reading.

At which point I'll have no more use for binary links again, but should
realize an increase in both space and performance efficiency.  Great!


One more thing that's come out of my study.  The Find function that I'd
really like to see is one that works like FindRange, but instead of taking a
low and high value it takes a bit mask which is anded against the value and
would conditionally include the record if:

A) All bits in mask are also set in value...  or
B) Any bit in mask is also set in value..  or
C) All bits in mask are clear in value..

I'm not sure if SQL can do this or not, but that will be one of the things I
check for.  I believe I looked before and saw boolean logic XOR and such on
conditions but not on bits.  But I'll look again :)

Thanks!
--Ed