QTP reads every row instead of using index?
Richard Witkopp
RWitkopp at phxa.com
Tue Sep 24 15:42:30 CDT 2013
It uses the index(es) on the RMS (ISAM) files, similar to what it does on a database.
________________________________
From: powerh-l-bounces+rwitkopp=phxa.com at lists.sowder.com [mailto:powerh-l-bounces+rwitkopp=phxa.com at lists.sowder.com] On Behalf Of Etienne Rompre
Sent: Tuesday, September 24, 2013 1:29 PM
To: powerh-l at lists.sowder.com
Subject: Re: QTP reads every row instead of using index?
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> [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<mailto: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<mailto:ken.langendock at rogers.com>>
To: "'Arnold Chan'" <Chan at camosun.bc.ca<mailto:Chan at camosun.bc.ca>>, "'powerh-l'" <powerh-l at lists.sowder.com<mailto: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<mailto: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>
[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<mailto:powerh-l at lists.sowder.com>
Subscribe: 'subscribe' in message body to powerh-l-request at lists.sowder.com<mailto:powerh-l-request at lists.sowder.com>
Unsubscribe: 'unsubscribe <password>' in message body to
powerh-l-request at lists.sowder.com<mailto: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<mailto:powerh-l at lists.sowder.com>
Subscribe: 'subscribe' in message body to powerh-l-request at lists.sowder.com<mailto:powerh-l-request at lists.sowder.com>
Unsubscribe: 'unsubscribe <password>' in message body to powerh-l-request at lists.sowder.com<mailto: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<mailto:powerh-l at lists.sowder.com>
Subscribe: 'subscribe' in message body to powerh-l-request at lists.sowder.com<mailto:powerh-l-request at lists.sowder.com>
Unsubscribe: 'unsubscribe <password>' in message body to powerh-l-request at lists.sowder.com<mailto: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.
securemail.phxa.com made the following annotations
---------------------------------------------------------------------
NOTICE: The information contained in this e-mail and
any attachments is confidential and may be privileged
or otherwise protected from disclosure.This e-mail is
intended solely for the use of the named addressee.
Any other use, printing, copying, disclosure or
dissemination may be subject to legal restriction. If
you are not the intended recipient, please contact the
sender and delete all copies including any attachments.
---------------------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130924/eb8d02d6/attachment-0001.htm>
More information about the powerh-l
mailing list