David:

I have done many of this tricks in the past but should've mentioned the we're using Entrinsik Informer for the reporting end - so I don't have control to do things like caching results in common, etc.

Thanks for your feedback

On 11/10/2012 04:03 PM, David A. Green wrote:
Reports have the following:

1. Input report parameters (Use whatever tool you want to have as an
interface with your users)
2. Select needed data (I use UniQuery here)
3. Manipulate data (I write a UniBasic program to populate a temp file with
the combined data from as many tables as I need to use)
4. Sort data (Again using UniQuery to Sort my temp file)
5. Output data (Use whatever reporting tool fits best, web, xml, printer,
terminal, email, fax, etc.)

The trick is to keep each of the above steps separate.

You can capsulate the "Report" steps in a Paragraph.

---

Another method if your just using UniQuery is to create an UniBasic
subroutine with a named common area that keeps track of the LAST.PERSON.ID
and the LAST.PERSON.REC.  Only read the PERSON file if the LAST.PERSON.ID
doesn't equal the current @ID.

---

Another way is: if you are getting most of your report data from PERSON then
create the I-Desc to get the needed DONOR data and use PERSON as the base
file.

David A. Green
(480) 813-1725
DAG Consulting

-----Original Message-----
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jeff Butera
Sent: Saturday, November 10, 2012 12:55 PM
To: U2 Users List
Subject: [U2] Improving performance

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
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


--
Jeff Butera, PhD
Associate Director for Applications and Web Services
Information Technology
Hampshire College
413-559-5556

_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Reply via email to