<html><head><base href="x-msg://152/"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Hi,<div><br></div><div>What effect CHOOSE would have on a OPENVMS system on RMS files?</div><div><br></div><div>Thanks!</div><div><br></div><div>Etienne</div><div><br><div><div><div>Le 2013-09-24 à 13:05, Arnold Chan a écrit :</div><br class="Apple-interchange-newline"><blockquote type="cite"><span class="Apple-style-span" style="border-collapse: separate; font-family: 'Andale Mono'; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><div lang="EN-CA" link="blue" vlink="purple"><div class="WordSection1" style="page: WordSection1; "><div style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 11pt; font-family: 'Comic Sans MS'; ">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!<o:p></o:p></span></div><div style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 11pt; font-family: 'Comic Sans MS'; "><o:p> </o:p></span></div><div style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 11pt; font-family: 'Comic Sans MS'; ">As I always tell the young folks at work: OpenVMS and Powerhouse just keep working.<o:p></o:p></span></div><div style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 11pt; font-family: 'Comic Sans MS'; "><o:p> </o:p></span></div><div style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 11pt; font-family: 'Comic Sans MS'; ">Cheers, Arnold<o:p></o:p></span></div><div style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 11pt; font-family: 'Comic Sans MS'; "><o:p> </o:p></span></div><div style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><b><span lang="EN-US" style="font-size: 10pt; font-family: Tahoma, sans-serif; ">From:</span></b><span lang="EN-US" style="font-size: 10pt; font-family: Tahoma, sans-serif; "><span class="Apple-converted-space"> </span><a href="mailto:Herald.Kaffka@westfraser.com" style="color: blue; text-decoration: underline; ">Herald.Kaffka@westfraser.com</a><span class="Apple-converted-space"> </span>[mailto:Herald.Kaffka@westfraser.com]<span class="Apple-converted-space"> </span><br><b>Sent:</b><span class="Apple-converted-space"> </span>Tuesday, 24 September, 2013 05:11<br><b>To:</b><span class="Apple-converted-space"> </span>Ken Langendock<br><b>Cc:</b><span class="Apple-converted-space"> </span>Arnold Chan; 'powerh-l';<span class="Apple-converted-space"> </span><a href="mailto:powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com</a><br><b>Subject:</b><span class="Apple-converted-space"> </span>RE: QTP reads every row instead of using index?<o:p></o:p></span></div><div style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div><div style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 10pt; font-family: Arial, sans-serif; ">I think of it as this way:</span><span class="Apple-converted-space"> </span><br><br><span style="font-size: 10pt; font-family: Arial, sans-serif; ">Select = Tell the database to send me all rows, that I may throw away the ones that I don't want.</span><span class="Apple-converted-space"> </span><br><br><span style="font-size: 10pt; font-family: Arial, sans-serif; ">Choose = Tell the database to send me the rows that match the following the criteria.</span><span class="Apple-converted-space"> </span><br><br><span style="font-size: 10pt; font-family: Arial, sans-serif; ">You can mix the two, choose something simple and selective, and select on something complex.</span><span class="Apple-converted-space"> </span><br><span style="font-size: 10pt; font-family: Arial, sans-serif; ">Basic rule is that choose happens down in the database layer, while selects happen in the Powerhouse layer.</span><span class="Apple-converted-space"> </span><br><br><span style="font-size: 10pt; font-family: Arial, sans-serif; ">(running as much as possible in the database layer is usually more efficient).</span><span class="Apple-converted-space"> </span><br><br><span style="font-size: 10pt; font-family: Arial, sans-serif; ">So yes, choose where possible, and save the selects for more complex expressions....</span><span class="Apple-converted-space"> </span><br><br><br><br><br><span style="font-size: 7.5pt; font-family: Arial, sans-serif; color: rgb(95, 95, 95); ">From: </span><span style="font-size: 7.5pt; font-family: Arial, sans-serif; ">Ken Langendock <<a href="mailto:ken.langendock@rogers.com" style="color: blue; text-decoration: underline; ">ken.langendock@rogers.com</a>></span><span class="Apple-converted-space"> </span><br><span style="font-size: 7.5pt; font-family: Arial, sans-serif; color: rgb(95, 95, 95); ">To: </span><span style="font-size: 7.5pt; font-family: Arial, sans-serif; ">"'Arnold Chan'" <<a href="mailto:Chan@camosun.bc.ca" style="color: blue; text-decoration: underline; ">Chan@camosun.bc.ca</a>>, "'powerh-l'" <<a href="mailto:powerh-l@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l@lists.sowder.com</a>>,<span class="Apple-converted-space"> </span></span><br><span style="font-size: 7.5pt; font-family: Arial, sans-serif; color: rgb(95, 95, 95); ">Date: </span><span style="font-size: 7.5pt; font-family: Arial, sans-serif; ">09/23/2013 06:19 PM</span><span class="Apple-converted-space"> </span><br><span style="font-size: 7.5pt; font-family: Arial, sans-serif; color: rgb(95, 95, 95); ">Subject: </span><span style="font-size: 7.5pt; font-family: Arial, sans-serif; ">RE: QTP reads every row instead of using index?</span><span class="Apple-converted-space"> </span><br><span style="font-size: 7.5pt; font-family: Arial, sans-serif; color: rgb(95, 95, 95); ">Sent by: </span><span style="font-size: 7.5pt; font-family: Arial, sans-serif; "><<a href="mailto:powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com</a>></span><o:p></o:p></div><div class="MsoNormal" align="center" style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; text-align: center; "><hr size="3" width="100%" noshade="" align="center" style="color: rgb(160, 160, 160); "></div><div style="margin-top: 0in; margin-right: 0in; margin-left: 0in; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><br><br><br><tt style="font-family: 'Courier New'; "><span style="font-size: 10pt; ">When using an SQL database, stop using SELECT and CHOOSE everything.</span></tt><span style="font-size: 10pt; font-family: 'Courier New'; "><br><br><tt style="font-family: 'Courier New'; ">> RUN ECE_TEST</tt><br><tt style="font-family: 'Courier New'; ">> REQUEST ECE_TEST1</tt><br><tt style="font-family: 'Courier New'; ">> ACCESS CON8 IN IFAS_DB</tt><br><tt style="font-family: 'Courier New'; ">> SET LIST SQL</tt><br><tt style="font-family: 'Courier New'; ">> CHOOSE BCSAPFOS_ID "XDG1"</tt><br><tt style="font-family: 'Courier New'; ">> output con8 in ifas_db update</tt><br><tt style="font-family: 'Courier New'; ">> item bcsapfos_id = "XDG1"</tt><br><tt style="font-family: 'Courier New'; ">> GO</tt><br><br><tt style="font-family: 'Courier New'; ">-----Original Message-----</tt><br><tt style="font-family: 'Courier New'; ">From:<span class="Apple-converted-space"> </span><a href="mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com</a></tt><br><tt style="font-family: 'Courier New'; ">[</tt></span><a href="mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com" style="color: blue; text-decoration: underline; "><tt style="font-family: 'Courier New'; "><span style="font-size: 10pt; ">mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com</span></tt></a><tt style="font-family: 'Courier New'; "><span style="font-size: 10pt; ">] On</span></tt><span style="font-size: 10pt; font-family: 'Courier New'; "><br><tt style="font-family: 'Courier New'; ">Behalf Of Arnold Chan</tt><br><tt style="font-family: 'Courier New'; ">Sent: Monday, September 23, 2013 7:13 PM</tt><br><tt style="font-family: 'Courier New'; ">To: 'powerh-l'</tt><br><tt style="font-family: 'Courier New'; ">Subject: QTP reads every row instead of using index?</tt><br><br><tt style="font-family: 'Courier New'; ">Hello,</tt><br><br><tt style="font-family: 'Courier New'; ">We're running PH 8.40d with Oracle8i (really) on OpenVMS. We have a problem</tt><br><tt style="font-family: 'Courier New'; ">with QTP. It correctly applies the SELECT criteria but it does so by reading</tt><br><tt style="font-family: 'Courier New'; ">every row in the table instead of using the index. Unfortunately something I</tt><br><tt style="font-family: 'Courier New'; ">can't get SET LIST SQL to show anything either. Here is my test results are</tt><br><tt style="font-family: 'Courier New'; ">below. I have a index on the bcsapfos_id column but it insists on reading</tt><br><tt style="font-family: 'Courier New'; ">every row. Its not a problem in my test data with 643 rows but the</tt><br><tt style="font-family: 'Courier New'; ">production table is much larger.</tt><br><br><tt style="font-family: 'Courier New'; ">The index couldn't be more straightforward. I tried using a plain b-tree</tt><br><tt style="font-family: 'Courier New'; ">index too. It did not make a difference.</tt><br><tt style="font-family: 'Courier New'; ">CREATE BITMAP INDEX con8_idx2 ON con8 (bcsapfos_id);</tt><br><br><tt style="font-family: 'Courier New'; ">Any assistance would be *greatly* appreciated.</tt><br><br><tt style="font-family: 'Courier New'; ">Regards,</tt><br><tt style="font-family: 'Courier New'; ">Arnold Chan,</tt><br><tt style="font-family: 'Courier New'; ">Registrar's Office,</tt><br><tt style="font-family: 'Courier New'; ">Camosun College</tt><br><br><br><tt style="font-family: 'Courier New'; ">> RUN ECE_TEST</tt><br><tt style="font-family: 'Courier New'; ">> REQUEST ECE_TEST1</tt><br><tt style="font-family: 'Courier New'; ">> ACCESS CON8 IN IFAS_DB</tt><br><tt style="font-family: 'Courier New'; ">> SET LIST SQL</tt><br><tt style="font-family: 'Courier New'; ">> SELECT CON8 IF (BCSAPFOS_ID = "XDG1")</tt><br><tt style="font-family: 'Courier New'; ">> output con8 in ifas_db update</tt><br><tt style="font-family: 'Courier New'; ">> item bcsapfos_id = "XDG1"</tt><br><tt style="font-family: 'Courier New'; ">> GO</tt><br><tt style="font-family: 'Courier New'; ">Executing request ECE_TEST1 ...</tt><br><tt style="font-family: 'Courier New'; ">Records read:</tt><br><tt style="font-family: 'Courier New'; "> CON8 643</tt><br><tt style="font-family: 'Courier New'; ">Transactions processed: 4</tt><br><tt style="font-family: 'Courier New'; ">Records processed: Added Updated Unchanged Deleted</tt><br><tt style="font-family: 'Courier New'; "> CON8 0 0 4 0</tt><br><tt style="font-family: 'Courier New'; ">Finished.</tt><br><br><tt style="font-family: 'Courier New'; ">---</tt><br><tt style="font-family: 'Courier New'; ">--</tt><br><tt style="font-family: 'Courier New'; ">= = = = = = = = = = = = = = = = = = = = = = = = = = = =</tt><br><tt style="font-family: 'Courier New'; ">Mailing list:<span class="Apple-converted-space"> </span><a href="mailto:powerh-l@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l@lists.sowder.com</a></tt><br><tt style="font-family: 'Courier New'; ">Subscribe: 'subscribe' in message body to<span class="Apple-converted-space"> </span><a href="mailto:powerh-l-request@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l-request@lists.sowder.com</a></tt><br><tt style="font-family: 'Courier New'; ">Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to</tt><br><tt style="font-family: 'Courier New'; "><a href="mailto:powerh-l-request@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l-request@lists.sowder.com</a></tt><br></span><a href="http://lists.sowder.com/mailman/listinfo/powerh-l" style="color: blue; text-decoration: underline; "><tt style="font-family: 'Courier New'; "><span style="font-size: 10pt; ">http://lists.sowder.com/mailman/listinfo/powerh-l</span></tt></a><span style="font-size: 10pt; font-family: 'Courier New'; "><br><tt style="font-family: 'Courier New'; ">This list is closed, thus to post to the list you must be a subscriber.</tt><br><tt style="font-family: 'Courier New'; ">Add 'site:lists.sowder.com powerh-l' to your search terms to search the list</tt><br><tt style="font-family: 'Courier New'; ">archive at Google.</tt><br><br><tt style="font-family: 'Courier New'; ">--</tt><br><tt style="font-family: 'Courier New'; ">= = = = = = = = = = = = = = = = = = = = = = = = = = = =</tt><br><tt style="font-family: 'Courier New'; ">Mailing list:<span class="Apple-converted-space"> </span><a href="mailto:powerh-l@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l@lists.sowder.com</a></tt><br><tt style="font-family: 'Courier New'; ">Subscribe: 'subscribe' in message body to<span class="Apple-converted-space"> </span><a href="mailto:powerh-l-request@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l-request@lists.sowder.com</a></tt><br><tt style="font-family: 'Courier New'; ">Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to<span class="Apple-converted-space"> </span><a href="mailto:powerh-l-request@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l-request@lists.sowder.com</a></tt><br></span><a href="http://lists.sowder.com/mailman/listinfo/powerh-l" style="color: blue; text-decoration: underline; "><tt style="font-family: 'Courier New'; "><span style="font-size: 10pt; ">http://lists.sowder.com/mailman/listinfo/powerh-l</span></tt></a><span style="font-size: 10pt; font-family: 'Courier New'; "><br><tt style="font-family: 'Courier New'; ">This list is closed, thus to post to the list you must be a subscriber.</tt><br><tt style="font-family: 'Courier New'; ">Add 'site:lists.sowder.com powerh-l' to your search terms to search the list archive at Google.</tt><br><br></span><br><br><br>----------------------------<span class="Apple-converted-space"> </span><br>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<o:p></o:p></div></div>--<br>= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>Mailing list:<span class="Apple-converted-space"> </span><a href="mailto:powerh-l@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l@lists.sowder.com</a><br>Subscribe: 'subscribe' in message body to<span class="Apple-converted-space"> </span><a href="mailto:powerh-l-request@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l-request@lists.sowder.com</a><br>Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to<span class="Apple-converted-space"> </span><a href="mailto:powerh-l-request@lists.sowder.com" style="color: blue; text-decoration: underline; ">powerh-l-request@lists.sowder.com</a><br><a href="http://lists.sowder.com/mailman/listinfo/powerh-l" style="color: blue; text-decoration: underline; ">http://lists.sowder.com/mailman/listinfo/powerh-l</a><br>This list is closed, thus to post to the list you must be a subscriber.<br>Add 'site:lists.sowder.com powerh-l' to your search terms to search the list archive at Google.</div></span></blockquote></div><br></div></div></body></html>