QTP reads every row instead of using index?

[Conrad Whittall]

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-0001.htm>