powerh-l Digest, Vol 98, Issue 20
Bob Deskin
bob.deskin at me.com
Wed Sep 25 13:46:28 CDT 2013
Keep in mind the important difference between any SELECT and CHOOSE. SELECT applies the condition to a record or row in the QUIZ or QTP buffer. CHOOSE conditions or values are applied at the file or database level. Only records or rows meeting the CHOOSE criteria are read into the buffer.
QUIZ and QTP attempt to apply a SELECT IF as the complex is being built. SELECT file IF is better because it's applied in the record read loop. If a record fails, the next record is immediately read.
So in order if efficiency in most cases:
CHOOSE first
SELECT file IF
SELECT IF
Bob
> On Sep 25, 2013, at 2:07 PM, vical1 at eastlink.ca wrote:
>
> 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
> ****************************************
> --
> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> 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.
More information about the powerh-l
mailing list