QTP reads every row instead of using index?
Herald.Kaffka at Westfraser.com
Herald.Kaffka at Westfraser.com
Wed Sep 25 14:22:01 CDT 2013
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 :-)
---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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130925/16339168/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/gif
Size: 105 bytes
Desc: not available
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130925/16339168/attachment.gif>
More information about the powerh-l
mailing list