Fine Tuning

[Ruslan Zasukhin]

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

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

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