<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>Thanks!</div><div><br></div><div>A+</div><div><br></div><div>Etienne</div><div><br></div><br><div><div>Le 2013-09-24 à 17:02, <<a href="mailto:Herald.Kaffka@westfraser.com">Herald.Kaffka@westfraser.com</a>> <<a href="mailto:Herald.Kaffka@westfraser.com">Herald.Kaffka@westfraser.com</a>> a écrit :</div><br class="Apple-interchange-newline"><blockquote type="cite"><font size="2" face="sans-serif">Think first, reply later. (gotta
remember that rule).</font>
<br>
<br><font size="2" face="sans-serif">There is/was an advantage to using Choose
(or choose with select) on RMS/ISAM.</font>
<br>
<br><font size="2" face="sans-serif">The choose (which can only operate on
the initial file on the access statement), can limit the size of the data
set which </font>
<br><font size="2" face="sans-serif">is processed. (You have to be
choosing an indexed item, only items which match the choose criteria are
driven down</font>
<br><font size="2" face="sans-serif">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</font>
<br><font size="2" face="sans-serif">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,</font>
<br><font size="2" face="sans-serif">this can be a big difference, (read
a million records, throw out the ones before yesterday, link the remaining
2000 records</font>
<br><font size="2" face="sans-serif">into a results set, as opposed to reading
2000 rows since yesterday (choose date(days(sysdate)-1)) and building the
complex...</font>
<br>
<br>
<br>
<br>
<br>
<br>
<br><font size="1" color="#5f5f5f" face="sans-serif">From:
</font><font size="1" face="sans-serif"><<a href="mailto:Herald.Kaffka@westfraser.com">Herald.Kaffka@westfraser.com</a>></font>
<br><font size="1" color="#5f5f5f" face="sans-serif">To:
</font><font size="1" face="sans-serif"><<a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a>>,
</font>
<br><font size="1" color="#5f5f5f" face="sans-serif">Date:
</font><font size="1" face="sans-serif">09/24/2013 03:48 PM</font>
<br><font size="1" color="#5f5f5f" face="sans-serif">Subject:
</font><font size="1" face="sans-serif">Re: QTP reads
every row instead of using index?</font>
<br><font size="1" color="#5f5f5f" face="sans-serif">Sent by:
</font><font size="1" face="sans-serif"><<a href="mailto:powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com">powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com</a>></font>
<br>
<hr noshade="">
<br>
<br>
<br><font size="2" face="sans-serif">Not much, as there is not a really a
separate database layer to push processing down into.</font><font size="3">
<br>
</font><font size="2" face="sans-serif"><br>
As I recall, on RMS/ISAM you can only choose things which are directly
supported by an index,</font><font size="3"> </font><font size="2" face="sans-serif"><br>
while you can select on any expression you can think of.</font><font size="3">
<br>
</font><font size="2" face="sans-serif"><br>
About the only advantage of the choose over a select that I can think of
was that it would ensure that at least <br>
the "core" of your access statement was walking an index. (but
it also meant that the individual programmer</font><font size="3"> </font><font size="2" face="sans-serif"><br>
had to know both the data and index structure, running with just selects
"anything goes", inefficient paths are</font><font size="3">
</font><font size="2" face="sans-serif"><br>
very possible, but the "new guy" or the contractor you brought
in for the special project needed much less</font><font size="3"> </font><font size="2" face="sans-serif"><br>
knowledge of your system/data). Pay me now, pay me later, burn "People
time" front, or CPU time downstream.</font><font size="3"> <br>
</font><font size="2" face="sans-serif"><br>
You can also mix choose/select on RMS/ISAM, again no real performance benefit,
but it does help delineate what</font><font size="3"> </font><font size="2" face="sans-serif"><br>
is and is not index access...</font><font size="3"> <br>
<br>
<br>
<br>
<br>
</font><font size="1" color="#5f5f5f" face="sans-serif"><br>
From: </font><font size="1" face="sans-serif">Etienne
Rompre <<a href="mailto:erompre@koala-tech.com">erompre@koala-tech.com</a>></font><font size="3"> </font><font size="1" color="#5f5f5f" face="sans-serif"><br>
To: </font><font size="1" face="sans-serif"><<a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a>>,
</font><font size="1" color="#5f5f5f" face="sans-serif"><br>
Date: </font><font size="1" face="sans-serif">09/24/2013
03:29 PM</font><font size="3"> </font><font size="1" color="#5f5f5f" face="sans-serif"><br>
Subject: </font><font size="1" face="sans-serif">Re:
QTP reads every row instead of using index?</font><font size="3"> </font><font size="1" color="#5f5f5f" face="sans-serif"><br>
Sent by: </font><font size="1" face="sans-serif"><<a href="mailto:powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com">powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com</a>></font><font size="3">
<br>
</font>
<hr noshade=""><font size="3"><br>
<br>
<br>
Hi, <br>
<br>
What effect CHOOSE would have on a OPENVMS system on RMS files? <br>
<br>
Thanks! <br>
<br>
Etienne <br>
<br>
Le 2013-09-24 à 13:05, Arnold Chan a écrit : <br>
</font><font size="2" face="Comic Sans MS"><br>
Thank you for the tip. I’ve updated my problem QTP program to use both.
It was taking 150 minutes. With the changes it now runs in less than 8
minutes!</font><font size="3"> </font><font size="2" face="Comic Sans MS"><br>
</font><font size="3"> </font><font size="2" face="Comic Sans MS"><br>
As I always tell the young folks at work: OpenVMS and Powerhouse just keep
working.</font><font size="3"> </font><font size="2" face="Comic Sans MS"><br>
</font><font size="3"> </font><font size="2" face="Comic Sans MS"><br>
Cheers, Arnold</font><font size="3"> </font><font size="2" face="Comic Sans MS"><br>
</font><font size="3"> </font><font size="2" face="Tahoma"><b><br>
From:</b> </font><a href="mailto:Herald.Kaffka@westfraser.com"><font size="2" color="blue" face="Tahoma"><u>Herald.Kaffka@westfraser.com</u></font></a><font size="2" face="Tahoma">
[</font><a href="mailto:Herald.Kaffka@westfraser.com"><font size="2" color="blue" face="Tahoma"><u>mailto:Herald.Kaffka@westfraser.com</u></font></a><font size="2" face="Tahoma">]
<b><br>
Sent:</b> Tuesday, 24 September, 2013 05:11<b><br>
To:</b> Ken Langendock<b><br>
Cc:</b> Arnold Chan; 'powerh-l'; </font><a href="mailto:powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com"><font size="2" color="blue" face="Tahoma"><u>powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com</u></font></a><font size="2" face="Tahoma"><b><br>
Subject:</b> RE: QTP reads every row instead of using index?</font><font size="3">
</font><font size="3" face="Times New Roman"><br>
</font><font size="3"> </font><font size="2" face="Arial"><br>
I think of it as this way:</font><font size="3" face="Times New Roman"> </font><font size="2" face="Arial"><br>
<br>
Select = Tell the database to send me all rows, that I may throw away the
ones that I don't want.</font><font size="3" face="Times New Roman"> </font><font size="2" face="Arial"><br>
<br>
Choose = Tell the database to send me the rows that match the following
the criteria.</font><font size="3" face="Times New Roman"> </font><font size="2" face="Arial"><br>
<br>
You can mix the two, choose something simple and selective, and select
on something complex.</font><font size="3" face="Times New Roman"> </font><font size="2" face="Arial"><br>
Basic rule is that choose happens down in the database layer, while selects
happen in the Powerhouse layer.</font><font size="3" face="Times New Roman">
</font><font size="2" face="Arial"><br>
<br>
(running as much as possible in the database layer is usually more efficient).</font><font size="3" face="Times New Roman">
</font><font size="2" face="Arial"><br>
<br>
So yes, choose where possible, and save the selects for more complex expressions....</font><font size="3" face="Times New Roman">
<br>
<br>
<br>
</font><font size="1" color="#5f5f5f" face="Arial"><br>
<br>
From: </font><font size="1" face="Arial">Ken Langendock
<</font><a href="mailto:ken.langendock@rogers.com"><font size="1" color="blue" face="Arial"><u>ken.langendock@rogers.com</u></font></a><font size="1" face="Arial">></font><font size="3" face="Times New Roman">
</font><font size="1" color="#5f5f5f" face="Arial"><br>
To: </font><font size="1" face="Arial">"'Arnold
Chan'" <</font><a href="mailto:Chan@camosun.bc.ca"><font size="1" color="blue" face="Arial"><u>Chan@camosun.bc.ca</u></font></a><font size="1" face="Arial">>,
"'powerh-l'" <</font><a href="mailto:powerh-l@lists.sowder.com"><font size="1" color="blue" face="Arial"><u>powerh-l@lists.sowder.com</u></font></a><font size="1" face="Arial">>,
</font><font size="1" color="#5f5f5f" face="Arial"><br>
Date: </font><font size="1" face="Arial">09/23/2013
06:19 PM</font><font size="3" face="Times New Roman"> </font><font size="1" color="#5f5f5f" face="Arial"><br>
Subject: </font><font size="1" face="Arial">RE:
QTP reads every row instead of using index?</font><font size="3" face="Times New Roman">
</font><font size="1" color="#5f5f5f" face="Arial"><br>
Sent by: </font><font size="1" face="Arial"><</font><a href="mailto:powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com"><font size="1" color="blue" face="Arial"><u>powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com</u></font></a><font size="1" face="Arial">></font><font size="3">
</font>
<div align="center">
<hr noshade=""></div>
<br><font size="3" face="Times New Roman"><br>
<br>
</font><font size="2" face="Courier New"><br>
<br>
When using an SQL database, stop using SELECT and CHOOSE everything.<br>
<br>
> RUN ECE_TEST<br>
> REQUEST ECE_TEST1<br>
> ACCESS CON8 IN IFAS_DB<br>
> SET LIST SQL<br>
> CHOOSE BCSAPFOS_ID "XDG1"<br>
> output con8 in ifas_db update<br>
> item bcsapfos_id = "XDG1"<br>
> GO<br>
<br>
-----Original Message-----<br>
From: </font><a href="mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com"><font size="2" color="blue" face="Courier New"><u>powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com</u></font></a><font size="2" face="Courier New"><br>
[</font><a href="mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com"><font size="2" color="blue" face="Courier New"><u>mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com</u></font></a><font size="2" face="Courier New">]
On<br>
Behalf Of Arnold Chan<br>
Sent: Monday, September 23, 2013 7:13 PM<br>
To: 'powerh-l'<br>
Subject: QTP reads every row instead of using index?<br>
<br>
Hello,<br>
<br>
We're running PH 8.40d with Oracle8i (really) on OpenVMS. We have a problem<br>
with QTP. It correctly applies the SELECT criteria but it does so by reading<br>
every row in the table instead of using the index. Unfortunately something
I<br>
can't get SET LIST SQL to show anything either. Here is my test results
are<br>
below. I have a index on the bcsapfos_id column but it insists on reading<br>
every row. Its not a problem in my test data with 643 rows but the<br>
production table is much larger.<br>
<br>
The index couldn't be more straightforward. I tried using a plain b-tree<br>
index too. It did not make a difference.<br>
CREATE BITMAP INDEX con8_idx2 ON con8 (bcsapfos_id);<br>
<br>
Any assistance would be *greatly* appreciated.<br>
<br>
Regards,<br>
Arnold Chan,<br>
Registrar's Office,<br>
Camosun College<br>
<br>
<br>
> RUN ECE_TEST<br>
> REQUEST ECE_TEST1<br>
> ACCESS CON8 IN IFAS_DB<br>
> SET LIST SQL<br>
> SELECT CON8 IF (BCSAPFOS_ID = "XDG1")<br>
> output con8 in ifas_db update<br>
> item bcsapfos_id = "XDG1"<br>
> GO<br>
Executing request ECE_TEST1 ...<br>
Records read:<br>
CON8
643<br>
Transactions processed: 4<br>
Records processed: Added
Updated Unchanged Deleted<br>
CON8
0 0
4 0<br>
Finished.<br>
<br>
---<br>
--<br>
= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>
Mailing list: </font><a href="mailto:powerh-l@lists.sowder.com"><font size="2" color="blue" face="Courier New"><u>powerh-l@lists.sowder.com</u></font></a><font size="2" face="Courier New"><br>
Subscribe: 'subscribe' in message body to </font><a href="mailto:powerh-l-request@lists.sowder.com"><font size="2" color="blue" face="Courier New"><u>powerh-l-request@lists.sowder.com</u></font></a><font size="2" face="Courier New"><br>
Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to</font><font size="3" color="blue"><u><br>
</u></font><a href="mailto:powerh-l-request@lists.sowder.com"><font size="2" color="blue" face="Courier New"><u>powerh-l-request@lists.sowder.com</u></font></a><font size="3" color="blue"><u><br>
</u></font><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><font size="2" color="blue" face="Courier New"><u>http://lists.sowder.com/mailman/listinfo/powerh-l</u></font></a><font size="2" face="Courier New"><br>
This list is closed, thus to post to the list you must be a subscriber.<br>
Add 'site:lists.sowder.com powerh-l' to your search terms to search the
list<br>
archive at Google.<br>
<br>
--<br>
= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>
Mailing list: </font><a href="mailto:powerh-l@lists.sowder.com"><font size="2" color="blue" face="Courier New"><u>powerh-l@lists.sowder.com</u></font></a><font size="2" face="Courier New"><br>
Subscribe: 'subscribe' in message body to </font><a href="mailto:powerh-l-request@lists.sowder.com"><font size="2" color="blue" face="Courier New"><u>powerh-l-request@lists.sowder.com</u></font></a><font size="2" face="Courier New"><br>
Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to
</font><a href="mailto:powerh-l-request@lists.sowder.com"><font size="2" color="blue" face="Courier New"><u>powerh-l-request@lists.sowder.com</u></font></a><font size="3" color="blue"><u><br>
</u></font><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><font size="2" color="blue" face="Courier New"><u>http://lists.sowder.com/mailman/listinfo/powerh-l</u></font></a><font size="2" face="Courier New"><br>
This list is closed, thus to post to the list you must be a subscriber.<br>
Add 'site:lists.sowder.com powerh-l' to your search terms to search the
list archive at Google.</font><font size="3" face="Times New Roman"><br>
<br>
<br>
<br>
<br>
---------------------------- <br>
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</font><font size="3"> <br>
--<br>
= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>
Mailing list: </font><a href="mailto:powerh-l@lists.sowder.com"><font size="3" color="blue"><u>powerh-l@lists.sowder.com</u></font></a><font size="3"><br>
Subscribe: 'subscribe' in message body to </font><a href="mailto:powerh-l-request@lists.sowder.com"><font size="3" color="blue"><u>powerh-l-request@lists.sowder.com</u></font></a><font size="3"><br>
Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to
</font><a href="mailto:powerh-l-request@lists.sowder.com"><font size="3" color="blue"><u>powerh-l-request@lists.sowder.com</u></font></a><font size="3" color="blue"><u><br>
</u></font><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><font size="3" color="blue"><u>http://lists.sowder.com/mailman/listinfo/powerh-l</u></font></a><font size="3"><br>
This list is closed, thus to post to the list you must be a subscriber.<br>
Add 'site:lists.sowder.com powerh-l' to your search terms to search the
list archive at Google. </font><tt><font size="2"><br>
--<br>
= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>
Mailing list: <a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a><br>
Subscribe: 'subscribe' in message body to <a href="mailto:powerh-l-request@lists.sowder.com">powerh-l-request@lists.sowder.com</a><br>
Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to
<a href="mailto:powerh-l-request@lists.sowder.com">powerh-l-request@lists.sowder.com</a></font></tt><font size="3" color="blue"><u><br>
</u></font><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><tt><font size="2" color="blue"><u>http://lists.sowder.com/mailman/listinfo/powerh-l</u></font></tt></a><tt><font size="2"><br>
This list is closed, thus to post to the list you must be a subscriber.<br>
Add 'site:lists.sowder.com powerh-l' to your search terms to search the
list archive at Google.</font></tt><font size="3"> <br>
<br>
<br>
---------------------------- <br>
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 </font><tt><font size="2">--<br>
= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>
Mailing list: <a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a><br>
Subscribe: 'subscribe' in message body to <a href="mailto:powerh-l-request@lists.sowder.com">powerh-l-request@lists.sowder.com</a><br>
Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to
<a href="mailto:powerh-l-request@lists.sowder.com">powerh-l-request@lists.sowder.com</a><br>
</font></tt><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><tt><font size="2">http://lists.sowder.com/mailman/listinfo/powerh-l</font></tt></a><tt><font size="2"><br>
This list is closed, thus to post to the list you must be a subscriber.<br>
Add 'site:lists.sowder.com powerh-l' to your search terms to search the
list archive at Google.</font></tt>
<br>
<br>
<br>----------------------------
<br>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
--<br>= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>Mailing list: <a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a><br>Subscribe: 'subscribe' in message body to <a href="mailto:powerh-l-request@lists.sowder.com">powerh-l-request@lists.sowder.com</a><br>Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to <a href="mailto:powerh-l-request@lists.sowder.com">powerh-l-request@lists.sowder.com</a><br><a href="http://lists.sowder.com/mailman/listinfo/powerh-l">http://lists.sowder.com/mailman/listinfo/powerh-l</a><br>This list is closed, thus to post to the list you must be a subscriber.<br>Add 'site:lists.sowder.com powerh-l' to your search terms to search the list archive at Google.</blockquote></div><br></body></html>