QTP reads every row instead of using index?
Richard Witkopp
RWitkopp at phxa.com
Tue Sep 24 15:54:08 CDT 2013
Holy cow, I have to disagree with this. Unless you have small files, an indexed read is much, much faster than reading the entire file end to end.
Also, if you hire a contractor that doesn't take the 60 seconds to run a QSHOW on the files he's using to see if indexes are available, you are paying him too much, no matter how little it is. Fire him (and hire me?). :)
________________________________
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 Herald.Kaffka at westfraser.com
Sent: Tuesday, September 24, 2013 1:45 PM
To: powerh-l at lists.sowder.com
Subject: Re: QTP reads every row instead of using index?
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> [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.
--
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
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
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/e49c0196/attachment-0001.htm>
More information about the powerh-l
mailing list