Linking to a subfile (was Subphyla question)

[Robert J.M. Edis]

G'day Matt, Bob

Actually you may have hit the nail on the head Bob.  The report I mentioned
to Matt did have a more complex ACCESS than I intimated.  It was more along
the lines of 

ACCESS table1 &
  LINK TO table2 OPTIONAL &
  LINK TO RECORD 0 OF *subfile

SELECT IF ....

This took about 45 minutes to run even though only a few thousand records
were involved.

By changing it to

ACCESS table1 &
  LINK TO table2 OPTIONAL
CHOOSE SYSTEMVALUE ...
SELECT IF ...

reduced the run time down to about 5 minutes even though the same number of
records were read from the primary file.

Blue

-----Original Message-----
From: Deskin, Bob
To: Ohmes, Matt; 'Robert J.M. Edis'
Sent: 3/1/00 6:40 AM
Subject: RE: Subphyla question

Theoretically (love that) a link to a specific record of a subfile (via
LINK
TO RECORD) should be treated like a link to a unique index. As long as
the
index value doesn't change, QUIZ doesn't read the record over because it
"knows" that the record already in the buffer is the correct one. That's
the
simple case. However, if you throw in SELECTs and SELECT file IFs and
OPTIONALs, the situation gets more complicated. There are cases where
the
input buffers are cleared because otherwise a DEFINE could be evaluated
using data belonging to another complex.

The way to test this is something like

ACCESS A LINK TO B LINK TO RECORD 0/1 OF C
GO

The 0 or 1 to get the first record depends on the platform. C can be a
direct file or a subfile, in which case put an asterisk on the front.

There should be very little difference between using C or not using C
(ACCESS A LINK TO B). But of course you have to run something that takes
a
reasonable amount of time because there will be file open overhead.

As Matt suggested, this used to work but probably hasn't been checked in
a
long time. In fact I think it's documented that way, at least somewhere
in
all the PH manuals on my shelves :-)

Bob

-----Original Message-----
From: Ohmes, Matt 
Sent: February 29, 2000 10:12 PM
To: 'Robert J.M. Edis'
Cc: Deskin, Bob
Subject: RE: Subphyla question


Hmmm... Well, to tell you the truth, I've never specifically tested it,
but
I'd heard this is the way it worked.  And I've never had the type of
performance problem you describe. I've used this technique a lot over
the
years and never saw the type of performance you described, but I've
never
specifically tried to test it. 

Now I must admit, I rarely worked on a UNIX box. My HP experience almost
exclusively on HP3000, but I've also used the technique extensively on
VAX.

Also, were you accessing a relational table then linking to the subfile?
If
so, the performance enhancement is almost certainly attributable to the
CHOOSE vs. a SELECT and not the Variable per se.  

Do I understand correctly the way we read a single record subfile Bob?
Matt

-----Original Message-----
From: Robert J.M. Edis [mailto:robert.edis@creatcomp.com]
Sent: Tuesday, February 29, 2000 5:29 PM
To: 'Ohmes, Matt '
Subject: RE: Subphyla question


G'day Matt

I have to challenge you on what you said about "Quiz will read the
subfile
record once".  I 'fixed' a report for a client last year on a HP/UX box
where a report was taking 45 minutes to run.  This report was reading a
table and linking to record 1 of a subfile.  It read the subfile for
each
occrrence on the primary file.

I replaced the logic using an environment variable and a CHOOSE on the
primary file instead.  This reduced the run time to only 5 minutes.

Blue 

-----Original Message-----
From: Ohmes, Matt
To: 'Scott, Valerie'; POWERH-L@lists.swau.edu
Sent: 2/29/00 5:48 PM
Subject: RE: Subphyla question

Actually you can link to the subfile from your data file. Try this:

ACCESS datafile LINK TO RECORD 1 OF *subfile

SELECT IF dateitem of subfile = dateitem of datafile

Quiz will read the subfile record once and keep it in memory through the
entire run, so it should be quite effecient.  

You didn't say what machine you are on, but the above works for VAX and
UNIX
(I believe). If you are on an HP3000, substitute a zero for the "1" in
the
ACCESS statement.

Matt

Matt.Ohmes@Cognos.Com
Cognos Corporation
909 E. Las Colinas Blvd.
Suite 1900
Irving, TX  75039
214-259-6200
"Matt doesn't really know anything.  He just likes to pontificate a lot.
We refuse to acknowledge that he works for Cognos or that we have ever
met him or anyone with whom he's ever been associated.  Don't lend him
money and don't let him talk to your sister!" ;-)

-----Original Message-----
From: Scott, Valerie [mailto:vscott@solanocounty.com]
Sent: Tuesday, February 29, 2000 4:30 PM
To: POWERH-L@lists.swau.edu
Subject: Subphyla question


How can I avoid needing a parm prompt entry in all 4 passes of a report?
I
am unable to pass the user date range beyond the first report.

I am writing a Quiz program that runs 4 passes of data.
Each pass for data require the same user entered Date range.
I created a one record subfile(a) to accept the PARM PROMPTED date
value.
On the first report, I use a USE to run the subfile(a), prompting for
the
date.
The remaining reports access other files and append to a separate
subfile,
which at the end is sorted and reported by type of record.
I don't believe I can link to my 'date' subfile, because the data file
is
driving the reporting.
Once the date range is captured, is there a way to hold the dates,
throughout all four passes?

Thank You,
Valerie
Solano County


 

Thank you,

Valerie Scott


= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
= =
Subscribe: "subscribe powerh-l" in message body to
majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to
majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
= =
Subscribe: "subscribe powerh-l" in message body to
majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to
majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.