powerh-l Digest, Vol 98, Issue 20
vical1 at eastlink.ca
vical1 at eastlink.ca
Wed Sep 25 13:07:46 CDT 2013
Yes, Select FILE if does eliminate building of unnecessary record structures on secondary (linked) files. If the record does not match the criteria, it is discarded immediately resulting in fewer record complexes built and less sequential reads for any subsequent "select if" or other processing.
I have seen it decrease processing time significantly if used well -- ie, against first secondary file that could have many 1:n joins. If it's on the 8th file in your Access statement and it was a 1:1 join anyway, unlikely to help very much. It helps to arrange your Access statement appropriately if you intend to use select File if.
Vicki
Sent on the TELUS Mobility network with BlackBerry
-----Original Message-----
From: powerh-l-request at lists.sowder.com
Sender: powerh-l-bounces+vical1=eastlink.ca at lists.sowder.com
Date: Wed, 25 Sep 2013 12:54:53
To: <powerh-l at lists.sowder.com>
Reply-to: powerh-l at lists.sowder.com
Subject: powerh-l Digest, Vol 98, Issue 20
Send powerh-l mailing list submissions to
powerh-l at lists.sowder.com
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.sowder.com/mailman/listinfo/powerh-l
or, via email, send a message with subject or body 'help' to
powerh-l-request at lists.sowder.com
You can reach the person managing the list at
powerh-l-owner at lists.sowder.com
When replying, please edit your Subject line so it is more specific
than "Re: Contents of powerh-l digest..."
Today's Topics:
1. RE:QTP reads every row instead of using index? (Arnold Chan)
2. RE:QTP reads every row instead of using index? (Conrad Whittall)
----------------------------------------------------------------------
Message: 1
Date: Wed, 25 Sep 2013 17:26:12 +0000
From: Arnold Chan <Chan at camosun.bc.ca>
Subject: RE: QTP reads every row instead of using index?
To: "'Herald.Kaffka at westfraser.com'" <Herald.Kaffka at westfraser.com>,
"'powerh-l at lists.sowder.com'" <powerh-l at lists.sowder.com>
Message-ID:
<3794E69AC274B44E8300EE2D55F5470F6068BF5A at EX10MBX02.intra.camosun.bc.ca>
Content-Type: text/plain; charset="us-ascii"
That is my recollection as well. I've always believed that SELECT IF would minimise i/o by reducing the number of record complexes that would be created. I *think* I read it in an old PH manual back in the day...
From: powerh-l-bounces+chan=camosun.bc.ca at lists.sowder.com [mailto:powerh-l-bounces+chan=camosun.bc.ca at lists.sowder.com] On Behalf Of Herald.Kaffka at westfraser.com
Sent: Wednesday, 25 September, 2013 06:52
To: powerh-l at lists.sowder.com
Subject: Re: QTP reads every row instead of using index?
(Had to trim most of the history, size of this message thread of was getting too big for this list...).
========================================================================
I also seem to remember something about "select file if" conditions short circuiting the build of a record complex,
(Select File if was supposed to kick in as soon as the file entered the record complex, and would prevent PH from
growing the record complex with unneeded records, while select was evaluated "after all records were retreived").
Don't know if this is an urban legend, an relic of an early PH version, or if there some truth to it...
To this day, (If I'm not working with a cursor), I still tend to
"Choose" on the primary file,
select file if as soon as possible on the other files in the access list (esp if selecting on some field condition in just that file such as "select inventory_balance if status of inventory_balance = "A").
and run a final select on cross table fields at the bottom.
Don't know if really more efficient or not (at one time I was told it was), but it does tend to make the logic on a large ugly join a little clearer (at least to me).
I'm not likely to change my style at this point, but I do idly wonder if "I'm right" or if it's just a "Crusty Old Guy" thing...
(Or if the answer is like all good fairy tales and begins "once upon a time" this was correct, but after version x it doesn't matter any more...).
===========================================================================
Thanks!
A+
Etienne
============================================================================
Think first, reply later. (gotta remember that rule).
There is/was an advantage to using Choose (or choose with select) on RMS/ISAM.
The choose (which can only operate on the initial file on the access statement), can limit the size of the data set which
is processed. (You have to be choosing an indexed item, only items which match the choose criteria are driven down
into the link x of table a to y of table b logic). With just a select, all of table A will be read, POWERHOUSE will apply the
select, and then use that to drive out the the other tables in the Join. If table A is very big, and the select is very selective,
this can be a big difference, (read a million records, throw out the ones before yesterday, link the remaining 2000 records
into a results set, as opposed to reading 2000 rows since yesterday (choose date(days(sysdate)-1)) and building the complex...
----------------------------
This e-mail message and any attachments are confidential. Any dissemination or use of this information by a person other than the intended recipient is unauthorized. If you are not the intended recipient, please notify me by return e-mail, do not open any attachment and delete this communication and any copy. Thank you
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130925/1b717264/attachment-0001.htm>
------------------------------
Message: 2
Date: Wed, 25 Sep 2013 13:54:38 -0400
From: Conrad Whittall <Conrad.Whittall at ca.ibm.com>
Subject: RE: QTP reads every row instead of using index?
To: powerh-l at lists.sowder.com
Message-ID:
<OF15BF21E4.897A28DE-ON85257BF1.0061806A-85257BF1.00626477 at ca.ibm.com>
Content-Type: text/plain; charset="utf-8"
That is correct -- on p.123 of the QTP 8.4G reference manual...
SELECT [FILE] record-structure IF condition
Applies a condition to a data record as it is read. Specifies that if the
selection condition is satisfied, the data record is included in the
transaction. If the condition is not satisfied, the data record, not the
transaction, is bypassed, and the next data record is read before the
transaction is constructed.
With the QUIZ equivalent being on p.113 of that reference manual...
SELECT record-structure [IF condition]
Applies a condition to a data record as it is read. Specifies that if the
selection condition is satisfied, the data record is included in the
record complex. If the condition isn?t satisfied, the data record is
bypassed and the next data record is read before the record complex is
constructed.
BTW, links to PDFs of the currently available documentation may be found
in this TechNote...
http://www-01.ibm.com/support/docview.wss?uid=swg27014618
Regards,
Conrad
========================================================================
From: Arnold Chan <Chan at camosun.bc.ca>
To: "'Herald.Kaffka at westfraser.com'" <Herald.Kaffka at westfraser.com>,
"'powerh-l at lists.sowder.com'" <powerh-l at lists.sowder.com>,
Date: 2013-09-25 13:27
Subject: RE: QTP reads every row instead of using index?
Sent by:
powerh-l-bounces+conrad.whittall=ca.ibm.com at lists.sowder.com
That is my recollection as well. I?ve always believed that SELECT IF
would minimise i/o by reducing the number of record complexes that would
be created. I *think* I read it in an old PH manual back in the day?
From: powerh-l-bounces+chan=camosun.bc.ca at lists.sowder.com [
mailto:powerh-l-bounces+chan=camosun.bc.ca at lists.sowder.com] On Behalf Of
Herald.Kaffka at westfraser.com
Sent: Wednesday, 25 September, 2013 06:52
To: powerh-l at lists.sowder.com
Subject: Re: QTP reads every row instead of using index?
(Had to trim most of the history, size of this message thread of was
getting too big for this list...).
========================================================================
I also seem to remember something about "select file if" conditions short
circuiting the build of a record complex,
(Select File if was supposed to kick in as soon as the file entered the
record complex, and would prevent PH from
growing the record complex with unneeded records, while select was
evaluated "after all records were retreived").
Don't know if this is an urban legend, an relic of an early PH version,
or if there some truth to it...
To this day, (If I'm not working with a cursor), I still tend to
"Choose" on the primary file,
select file if as soon as possible on the other files in the access list
(esp if selecting on some field condition in just that file such as
"select inventory_balance if status of inventory_balance = "A").
and run a final select on cross table fields at the bottom.
Don't know if really more efficient or not (at one time I was told it
was), but it does tend to make the logic on a large ugly join a little
clearer (at least to me).
I'm not likely to change my style at this point, but I do idly wonder if
"I'm right" or if it's just a "Crusty Old Guy" thing...
(Or if the answer is like all good fairy tales and begins "once upon a
time" this was correct, but after version x it doesn't matter any
more...).
===========================================================================
Thanks!
A+
Etienne
============================================================================
Think first, reply later. (gotta remember that rule).
There is/was an advantage to using Choose (or choose with select) on
RMS/ISAM.
The choose (which can only operate on the initial file on the access
statement), can limit the size of the data set which
is processed. (You have to be choosing an indexed item, only items which
match the choose criteria are driven down
into the link x of table a to y of table b logic). With just a select,
all of table A will be read, POWERHOUSE will apply the
select, and then use that to drive out the the other tables in the Join.
If table A is very big, and the select is very selective,
this can be a big difference, (read a million records, throw out the ones
before yesterday, link the remaining 2000 records
into a results set, as opposed to reading 2000 rows since yesterday
(choose date(days(sysdate)-1)) and building the complex...
----------------------------
This e-mail message and any attachments are confidential. Any
dissemination or use of this information by a person other than the
intended recipient is unauthorized. If you are not the intended recipient,
please notify me by return e-mail, do not open any attachment and delete
this communication and any copy. Thank you --
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130925/e14ef03f/attachment.htm>
------------------------------
--
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: "subscribe" in message body to powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe <password>" in message body to powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
Add 'site:lists.sowder.com powerh-l' to your search terms to search the list archive at Google.
End of powerh-l Digest, Vol 98, Issue 20
****************************************
More information about the powerh-l
mailing list