With Informer, I think you have a process at start. One idea is to use that process slot to launch a Basic program to collect all of your information then write one record per each Person to a work file. Setup your dictionary with simple amcs off of that work file and report from it. Let the Basic front end do all the work at the start. You'd have to cater for port-specific work files or such so that multiple users can run the report at the same time, but that's easy enough in the process at start as well - if the file exists, clear it, if it doesn't, then create it.
HTH, Laura -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Jeff Butera Sent: Saturday, November 10, 2012 5:41 PM To: [email protected] Subject: Re: [U2] Improving performance 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: [email protected] > [mailto:[email protected]] 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 > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users > > _______________________________________________ > U2-Users mailing list > [email protected] > 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 [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
