QTP reads every row instead of using index?
Etienne Rompre
erompre at koala-tech.com
Wed Sep 25 08:14:29 CDT 2013
Thanks!
A+
Etienne
Le 2013-09-24 à 17:02, <Herald.Kaffka at westfraser.com> <Herald.Kaffka at westfraser.com> a écrit :
> Think first, reply later. (gotta remember that rule).
>
> There is/was an advantage to using Choose (or choose with select) on RMS/ISAM.
>
> The choose (which can only operate on the initial file on the access statement), can limit the size of the data set which
> is processed. (You have to be choosing an indexed item, only items which match the choose criteria are driven down
> into the link x of table a to y of table b logic). With just a select, all of table A will be read, POWERHOUSE will apply the
> select, and then use that to drive out the the other tables in the Join. If table A is very big, and the select is very selective,
> this can be a big difference, (read a million records, throw out the ones before yesterday, link the remaining 2000 records
> into a results set, as opposed to reading 2000 rows since yesterday (choose date(days(sysdate)-1)) and building the complex...
>
>
>
>
>
>
> From: <Herald.Kaffka at westfraser.com>
> To: <powerh-l at lists.sowder.com>,
> Date: 09/24/2013 03:48 PM
> Subject: Re: QTP reads every row instead of using index?
> Sent by: <powerh-l-bounces+herald.kaffka=westfraser.com at lists.sowder.com>
>
>
>
> 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]
> 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.
> --
> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> 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.
>
>
> ----------------------------
> 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20130925/21ff2463/attachment-0001.htm>
More information about the powerh-l
mailing list