QTP reads every row instead of using index?

[Herald.Kaffka at westfraser.com]

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



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130924/b6ab4918/attachment.htm>