QTP reads every row instead of using index?

[Etienne Rompre]

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 &lt;password&gt;' 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 &lt;password&gt;' 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 &lt;password&gt;' 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/a370d708/attachment-0001.htm>