<font size=2 face="sans-serif">Not much, as there is not a really a separate
database layer to push processing down into.</font>
<br>
<br><font size=2 face="sans-serif">As I recall, on RMS/ISAM you can only
choose things which are directly supported by an index,</font>
<br><font size=2 face="sans-serif">while you can select on any expression
you can think of.</font>
<br>
<br><font size=2 face="sans-serif">About the only advantage of the choose
over a select that I can think of was that it would ensure that at least
</font>
<br><font size=2 face="sans-serif">the "core" of your access
statement was walking an index. (but it also meant that the individual
programmer</font>
<br><font size=2 face="sans-serif">had to know both the data and index
structure, running with just selects "anything goes", inefficient
paths are</font>
<br><font size=2 face="sans-serif">very possible, but the "new guy"
or the contractor you brought in for the special project needed much less</font>
<br><font size=2 face="sans-serif">knowledge of your system/data). Pay
me now, pay me later, burn "People time" front, or CPU time downstream.</font>
<br>
<br><font size=2 face="sans-serif">You can also mix choose/select on RMS/ISAM,
again no real performance benefit, but it does help delineate what</font>
<br><font size=2 face="sans-serif">is and is not index access...</font>
<br>
<br>
<br>
<br>
<br>
<br><font size=1 color=#5f5f5f face="sans-serif">From:
</font><font size=1 face="sans-serif">Etienne Rompre <erompre@koala-tech.com></font>
<br><font size=1 color=#5f5f5f face="sans-serif">To:
</font><font size=1 face="sans-serif"><powerh-l@lists.sowder.com>,
</font>
<br><font size=1 color=#5f5f5f face="sans-serif">Date:
</font><font size=1 face="sans-serif">09/24/2013 03:29 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"><powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com></font>
<br>
<hr noshade>
<br>
<br>
<br><font size=3>Hi,</font>
<br>
<br><font size=3>What effect CHOOSE would have on a OPENVMS system on RMS
files?</font>
<br>
<br><font size=3>Thanks!</font>
<br>
<br><font size=3>Etienne</font>
<br>
<br><font size=3>Le 2013-09-24 à 13:05, Arnold Chan a écrit :</font>
<br>
<br><font size=2 face="Comic Sans MS">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>
<br><font size=2 face="Comic Sans MS"> </font>
<br><font size=2 face="Comic Sans MS">As I always tell the young folks
at work: OpenVMS and Powerhouse just keep working.</font>
<br><font size=2 face="Comic Sans MS"> </font>
<br><font size=2 face="Comic Sans MS">Cheers, Arnold</font>
<br><font size=2 face="Comic Sans MS"> </font>
<br><font size=2 face="Tahoma"><b>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 face="Tahoma">mailto:Herald.Kaffka@westfraser.com</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>
<br><font size=3 face="Times New Roman"> </font>
<br><font size=2 face="Arial">I think of it as this way:</font><font size=3 face="Times New Roman">
<br>
</font><font size=2 face="Arial"><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"> <br>
</font><font size=2 face="Arial"><br>
Choose = Tell the database to send me the rows that match the following
the criteria.</font><font size=3 face="Times New Roman"> <br>
</font><font size=2 face="Arial"><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">
<br>
</font><font size=2 face="Arial"><br>
(running as much as possible in the database layer is usually more efficient).</font><font size=3 face="Times New Roman">
<br>
</font><font size=2 face="Arial"><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>
<br>
</font><font size=1 color=#5f5f5f face="Arial"><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>
<div align=center>
<hr noshade></div>
<br><font size=3 face="Times New Roman"><br>
<br>
</font><font size=2 face="Courier New"><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=2 color=blue face="Courier New"><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 face="Times New Roman"><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 face="Times New Roman"><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.<br>
</font><font size=3 face="Times New Roman"><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>
<br><font size=3>--<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>
<br><tt><font size=2>--<br>
= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>
Mailing list: powerh-l@lists.sowder.com<br>
Subscribe: 'subscribe' in message body to powerh-l-request@lists.sowder.com<br>
Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to
powerh-l-request@lists.sowder.com<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