QTP reads every row instead of using index?

[Robert Deskin]

There are no "old" PowerHouse programmers, only better ones :-)

On 2013-09-24, at 12:57 PM, Arnold Chan <Chan at camosun.bc.ca> wrote:

> Thank you both for your assistance and in teaching me what CHOOSE was really about. For over twenty years I've overlooked CHOOSE in favour of SELECT IF. Thank you for showing me the light. Better late than never. Even for an old Powerhouse programmer.  :-)
> 
> Regards, Arnold
> 
> 
> 
> -----Original Message-----
> From: Robert Deskin [mailto:bob.deskin at me.com] 
> Sent: Monday, 23 September, 2013 16:50
> To: Ken Langendock; Arnold Chan
> Cc: powerh-l
> Subject: Re: QTP reads every row instead of using index?
> 
> Two things.
> 
> First, SELECT IF by definition operates on every record that the ACCESS statement returns. A CHOOSE may restrict what records are returned by the ACCESS statement in which case the SELECT IF applies to chosen records.
> 
> Second, and more important, PowerHouse has no control over the way the relational database retrieves rows. PowerHouse uses index and key information when generating retrieval but the database is not bound to use it.
> 
> So even if you use a CHOOSE, the database may not use the index. However, you will push the conditioning down to the database because PowerHouse will use CHOOSE information when it constructs the SQL SELECT.
> 
> Bob
> 
> On 2013-09-23, at 7:22 PM, Ken Langendock <ken.langendock at rogers.com> wrote:
> 
>> 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.
>