QTP reads every row instead of using index?
Matt Ohmes
mohmes at us.ibm.com
Wed Sep 25 14:26:50 CDT 2013
I do remember; you, IP and MDM. :-) (Although the memories are getting
fuzzier these days.)
Good to still be around, considering the alternative. Cheers!
From: <Herald.Kaffka at westfraser.com>
To: <powerh-l at lists.sowder.com>,
Date: 09/25/2013 02:23 PM
Subject: Re: QTP reads every row instead of using index?
Sent by: powerh-l-bounces+mohmes=us.ibm.com at lists.sowder.com
Small world, when I think back on it, I think it was you who tried to
explain the difference between Choose/Select file If /Select IF
in a PH class out in "Lost Colanis" many many moons ago...
Used to Work for International Paper way back then, a few years back they
"got Medieval" and sold their Wood Products
division to West Fraser in a "Castle and attendant Serfs" type transaction.
(My status in that was "Attendant Serf").
If you remember our old sales system MDS (Materials Distribution System),
its still running. Started out on RMS/ISAM, Vaxes, vms 5.1,
nowadays we're running on Itanium machines, vms 8.4 against oracle 11.2 (db
is on a Solaris server these days).
We had to put some effort into the migration to oracle, but everything
since then has been "mostly" recompile and go.
Too bad there's not really a marketing department that cares about things
like that anymore...
Good to see some old names every now and then...
From: Matt Ohmes <mohmes at us.ibm.com>
To: <powerh-l at lists.sowder.com>,
Date: 09/25/2013 01:53 PM
Subject: Re: QTP reads every row instead of using index?
Sent by: <powerh-l-bounces
+herald.kaffka=westfraser.com at lists.sowder.com>
I couldn't resist responding to this, even though I haven't used PowerHouse
in 10 years (at least).
First, always use Choose if you can. That limits the number of rows read
from your source. All forms of Select only are checked after the data has
been read (although it may not have been added to the record complex yet).
Now...
Select If and Select <file> If are similar but slightly different commands.
* You can use Select <file> If when all the selection criteria are related
to items in that single file.
* You have to use Select If when you compare items in separate files.
Also, you can have one Select <file> If statement for EACH file in your
Access statement but ONLY ONE Select If statement.
Access Employees link to Dependants
Select Employees If Employees.Birth_Date > '1950-01-01'
Select If Employees.Hire_Date > Dependants.Birth_Date
This is essentially the same as:
Access Employees link to Dependants
Select If Employees.Birth_Date > '1950-01-01' AND Employees.Hire_Date >
Dependants.Birth_Date
Mostly, I used both versions; to make it easier to read and understand.
HOWEVER... Select If and Select <file> If are only identical if your
Access statement is the basic form ( "Access A link to B link to C"). If
you are constructing a "parallel" Access statement (Access A link to B AND
to C), there is a BIG difference between the two Select forms. Essentially
you can't guarantee you'll get the record records from files B and C if you
don't use Select <file> If.
I had some good examples in papers I wrote 20 years ago that are somewhere
on backup CDs. I'll try to recreate one:
Access Employees link to Dependants AND to Payroll
Dependants and Payroll records have nothing directly to do with one
another; they are only related through Employees. Say I have 2 dependants
and 3 payroll records.
Employee Dependant Payroll
1 Spouse 2013-01-01
Child 2013-01-15
2013-02-01
If you use "Access Employees link to Dependants LINK to Payroll", you would
get 6 record complexes (1:n:n)
1 Spouse 2013-01-01
1 Spouse 2013-01-15
1 Spouse 2013-02-01
1 Child 2013-01-01
1 Child 2013-01-15
1 Child 2013-02-01
This is probably not what you wanted.
If you use "Access Employees link to Dependants AND to Payroll" you only
get 3; Dependants and Payroll are read in parallel.
1 Spouse 2013-01-01
1 Child 2013-01-15
1 <blank> 2013-02-01
This is probably closer to what you want.
Now, what if you only wanted to see payroll records after the first of 2013
but you still wanted to see all the employee's dependants?
On the parallel linkage, if you used "Select If Payroll.pay_date >
'2013-01-02'" then the first record COMPLEX would be rejected (first
payroll record AND the first dependant record) and you would get
1 Child 2013-01-15
1 <blank> 2013-02-01
You just lost one of the dependants.
If you used "Select Payroll If Payroll.pay_date > '2013-01-02'" then the
payroll record would be discarded before it reached the record complex and
your first dependant would be preserved.
1 Spouse 2013-01-15
1 Child 2013-02-01
And that is WHY there is a Select <file> If statement in the first
place. :-)
Anyway, hardly anyone uses Parallel Access statements, so it's probably not
relevant.
Just remembering the 'good old days'.
Matt :-)
Inactive hide details for ---09/25/2013 08:53:14 AM---(Had to trim most of
the history, size of this message thread of was get---09/25/2013 08:53:14
AM---(Had to trim most of the history, size of this message thread of was
getting too big for this list.
From: <Herald.Kaffka at westfraser.com>
To: <powerh-l at lists.sowder.com>,
Date: 09/25/2013 08:53 AM
Subject: Re: QTP reads every row instead of using index?
Sent by: powerh-l-bounces+mohmes=us.ibm.com at lists.sowder.com
(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 --
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.--
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.
----------------------------
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 --
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130925/739c7442/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: graycol.gif
Type: image/gif
Size: 105 bytes
Desc: not available
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130925/739c7442/attachment-0001.gif>
More information about the powerh-l
mailing list