QTP reads every row instead of using index?
Herald.Kaffka at westfraser.com
Herald.Kaffka at westfraser.com
Tue Sep 24 15:44:54 CDT 2013
Not much, as there is not a really a separate database layer to push
processing down into.
As I recall, on RMS/ISAM you can only choose things which are directly
supported by an index,
while you can select on any expression you can think of.
About the only advantage of the choose over a select that I can think of
was that it would ensure that at least
the "core" of your access statement was walking an index. (but it also
meant that the individual programmer
had to know both the data and index structure, running with just selects
"anything goes", inefficient paths are
very possible, but the "new guy" or the contractor you brought in for the
special project needed much less
knowledge of your system/data). Pay me now, pay me later, burn "People
time" front, or CPU time downstream.
You can also mix choose/select on RMS/ISAM, again no real performance
benefit, but it does help delineate what
is and is not index access...
From: Etienne Rompre <erompre at koala-tech.com>
To: <powerh-l at lists.sowder.com>,
Date: 09/24/2013 03:29 PM
Subject: Re: QTP reads every row instead of using index?
Sent by:
<powerh-l-bounces+herald.kaffka=westfraser.com at lists.sowder.com>
Hi,
What effect CHOOSE would have on a OPENVMS system on RMS files?
Thanks!
Etienne
Le 2013-09-24 à 13:05, Arnold Chan a écrit :
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!
As I always tell the young folks at work: OpenVMS and Powerhouse just keep
working.
Cheers, Arnold
From: Herald.Kaffka at westfraser.com [mailto:Herald.Kaffka at westfraser.com]
Sent: Tuesday, 24 September, 2013 05:11
To: Ken Langendock
Cc: Arnold Chan; 'powerh-l';
powerh-l-bounces+herald.kaffka=westfraser.com at lists.sowder.com
Subject: RE: QTP reads every row instead of using index?
I think of it as this way:
Select = Tell the database to send me all rows, that I may throw away the
ones that I don't want.
Choose = Tell the database to send me the rows that match the following
the criteria.
You can mix the two, choose something simple and selective, and select on
something complex.
Basic rule is that choose happens down in the database layer, while
selects happen in the Powerhouse layer.
(running as much as possible in the database layer is usually more
efficient).
So yes, choose where possible, and save the selects for more complex
expressions....
From: Ken Langendock <ken.langendock at rogers.com>
To: "'Arnold Chan'" <Chan at camosun.bc.ca>, "'powerh-l'" <
powerh-l at lists.sowder.com>,
Date: 09/23/2013 06:19 PM
Subject: RE: QTP reads every row instead of using index?
Sent by: <
powerh-l-bounces+herald.kaffka=westfraser.com at lists.sowder.com>
When using an SQL database, stop using SELECT and CHOOSE everything.
> RUN ECE_TEST
> REQUEST ECE_TEST1
> ACCESS CON8 IN IFAS_DB
> SET LIST SQL
> CHOOSE BCSAPFOS_ID "XDG1"
> output con8 in ifas_db update
> item bcsapfos_id = "XDG1"
> GO
-----Original Message-----
From: powerh-l-bounces+ken.langendock=rogers.com at lists.sowder.com
[mailto:powerh-l-bounces+ken.langendock=rogers.com at lists.sowder.com] On
Behalf Of Arnold Chan
Sent: Monday, September 23, 2013 7:13 PM
To: 'powerh-l'
Subject: QTP reads every row instead of using index?
Hello,
We're running PH 8.40d with Oracle8i (really) on OpenVMS. We have a
problem
with QTP. It correctly applies the SELECT criteria but it does so by
reading
every row in the table instead of using the index. Unfortunately something
I
can't get SET LIST SQL to show anything either. Here is my test results
are
below. I have a index on the bcsapfos_id column but it insists on reading
every row. Its not a problem in my test data with 643 rows but the
production table is much larger.
The index couldn't be more straightforward. I tried using a plain b-tree
index too. It did not make a difference.
CREATE BITMAP INDEX con8_idx2 ON con8 (bcsapfos_id);
Any assistance would be *greatly* appreciated.
Regards,
Arnold Chan,
Registrar's Office,
Camosun College
> RUN ECE_TEST
> REQUEST ECE_TEST1
> ACCESS CON8 IN IFAS_DB
> SET LIST SQL
> SELECT CON8 IF (BCSAPFOS_ID = "XDG1")
> output con8 in ifas_db update
> item bcsapfos_id = "XDG1"
> GO
Executing request ECE_TEST1 ...
Records read:
CON8 643
Transactions processed: 4
Records processed: Added Updated Unchanged Deleted
CON8 0 0 4 0
Finished.
---
--
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.
--
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
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/20130924/80c78230/attachment-0001.htm>
More information about the powerh-l
mailing list