At the outset, I'm a lover of Pick/MV but also have experience with 1NF
(mostly mysql/postgresql). Here's my delimma:
For the sake of simplicity, let's say I have two tables with the
following data attributes:
PERSON: id, first_name, last_name, email, phone, spouse
DONOR: id, given_amt, pledge_amt
The ID for PERSON and DONOR are same, and spouse is a X-pointer to
another PERSON record (if populated). Often our fundraisers write
reports out of DONOR and often want spouse info (name, email, phone).
This is typically accomplished with I-desc in DONOR such as:
spouse_first:
TRANS('PERSON',TRANS('PERSON',@ID,'spouse','X'),'first_name','X')
spouse_last:
TRANS('PERSON',TRANS('PERSON',@ID,'spouse','X'),'last_name','X')
spouse_email:
TRANS('PERSON',TRANS('PERSON',@ID,'spouse','X'),'email','X')
spouse_phone:
TRANS('PERSON',TRANS('PERSON',@ID,'spouse','X'),'phone','X')
Thus, for each DONOR record in my report if I want the four fields above
I've got to perform 8 reads of the PERSON table. Sure, caching will
improve this performance - but let's forget that for the moment. So for
80000 DONOR records I'm doing 640000 PERSON reads.
Am I correct about how TRANS is working in these cases?
In an attempt to improve performance, I've taken to replicating the
spouse field in the DONOR record by using an update trigger on PERSON to
ensure spouse is updated in DONOR as well. By replicating the spouse
in DONOR, I eliminate one TRANS in each field and cut my 640000 PERSON
reads to 320000 reads.
That said, I'm looking for other means - this is one place where Pick
isn't my friend as we often have reports written out of various tables
and need to use 32+ TRANS statements to pull in PERSON demographic
info. Any insight appreciated.
--
Jeff Butera, PhD
Associate Director for Applications and Web Services
Information Technology
Hampshire College
413-559-5556
_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
