QTP reads every row instead of using index?
Robert Deskin
bob.deskin at me.com
Tue Sep 24 15:46:24 CDT 2013
As Richard says. A CHOOSE on an RMS ISAM file will use an index, so if you're providing a value, it only returns records with that index value.
On 2013-09-24, at 4:42 PM, Richard Witkopp <RWitkopp at phxa.com> wrote:
> 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]
> 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.
>
> 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.
>
> ---------------------------------------------------------------------
> --
> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130924/7d078cea/attachment-0001.htm>
More information about the powerh-l
mailing list