<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Comic Sans MS";
panose-1:3 15 7 2 3 3 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
tt
{mso-style-priority:99;
font-family:"Courier New";}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";
mso-fareast-language:EN-CA;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Comic Sans MS";
color:windowtext;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-CA" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;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></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Comic Sans MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;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></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Comic Sans MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Comic Sans MS"">Cheers, Arnold<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Comic Sans MS""><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Herald.Kaffka@westfraser.com [mailto:Herald.Kaffka@westfraser.com]
<br>
<b>Sent:</b> Tuesday, 24 September, 2013 05:11<br>
<b>To:</b> Ken Langendock<br>
<b>Cc:</b> Arnold Chan; 'powerh-l'; powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com<br>
<b>Subject:</b> RE: QTP reads every row instead of using index?<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">I think of it as this way:</span>
<br>
<br>
<span style="font-size:10.0pt;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>
<br>
<br>
<span style="font-size:10.0pt;font-family:"Arial","sans-serif"">Choose = Tell the database to send me the rows that match the following the criteria.</span>
<br>
<br>
<span style="font-size:10.0pt;font-family:"Arial","sans-serif"">You can mix the two, choose something simple and selective, and select on something complex.</span>
<br>
<span style="font-size:10.0pt;font-family:"Arial","sans-serif"">Basic rule is that choose happens down in the database layer, while selects happen in the Powerhouse layer.</span>
<br>
<br>
<span style="font-size:10.0pt;font-family:"Arial","sans-serif"">(running as much as possible in the database layer is usually more efficient).</span>
<br>
<br>
<span style="font-size:10.0pt;font-family:"Arial","sans-serif"">So yes, choose where possible, and save the selects for more complex expressions....</span>
<br>
<br>
<br>
<br>
<br>
<span style="font-size:7.5pt;font-family:"Arial","sans-serif";color:#5F5F5F">From: </span><span style="font-size:7.5pt;font-family:"Arial","sans-serif"">Ken Langendock <<a href="mailto:ken.langendock@rogers.com">ken.langendock@rogers.com</a>></span>
<br>
<span style="font-size:7.5pt;font-family:"Arial","sans-serif";color:#5F5F5F">To: </span><span style="font-size:7.5pt;font-family:"Arial","sans-serif"">"'Arnold Chan'" <<a href="mailto:Chan@camosun.bc.ca">Chan@camosun.bc.ca</a>>, "'powerh-l'" <<a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a>>,
</span><br>
<span style="font-size:7.5pt;font-family:"Arial","sans-serif";color:#5F5F5F">Date: </span><span style="font-size:7.5pt;font-family:"Arial","sans-serif"">09/23/2013 06:19 PM</span>
<br>
<span style="font-size:7.5pt;font-family:"Arial","sans-serif";color:#5F5F5F">Subject: </span><span style="font-size:7.5pt;font-family:"Arial","sans-serif"">RE: QTP reads every row instead of using index?</span>
<br>
<span style="font-size:7.5pt;font-family:"Arial","sans-serif";color:#5F5F5F">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">powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com</a>></span>
<o:p></o:p></p>
<div class="MsoNormal" align="center" style="text-align:center">
<hr size="3" width="100%" noshade="" style="color:#A0A0A0" align="center">
</div>
<p class="MsoNormal"><br>
<br>
<br>
<tt><span style="font-size:10.0pt">When using an SQL database, stop using SELECT and CHOOSE everything.</span></tt><span style="font-size:10.0pt;font-family:"Courier New""><br>
<br>
<tt>> RUN ECE_TEST</tt><br>
<tt>> REQUEST ECE_TEST1</tt><br>
<tt>> ACCESS CON8 IN IFAS_DB</tt><br>
<tt>> SET LIST SQL</tt><br>
<tt>> CHOOSE BCSAPFOS_ID "XDG1"</tt><br>
<tt>> output con8 in ifas_db update</tt><br>
<tt>> item bcsapfos_id = "XDG1"</tt><br>
<tt>> GO</tt><br>
<br>
<tt>-----Original Message-----</tt><br>
<tt>From: <a href="mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com">
powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com</a></tt><br>
<tt>[</tt></span><a href="mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com"><tt><span style="font-size:10.0pt">mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com</span></tt></a><tt><span style="font-size:10.0pt">] On</span></tt><span style="font-size:10.0pt;font-family:"Courier New""><br>
<tt>Behalf Of Arnold Chan</tt><br>
<tt>Sent: Monday, September 23, 2013 7:13 PM</tt><br>
<tt>To: 'powerh-l'</tt><br>
<tt>Subject: QTP reads every row instead of using index?</tt><br>
<br>
<tt>Hello,</tt><br>
<br>
<tt>We're running PH 8.40d with Oracle8i (really) on OpenVMS. We have a problem</tt><br>
<tt>with QTP. It correctly applies the SELECT criteria but it does so by reading</tt><br>
<tt>every row in the table instead of using the index. Unfortunately something I</tt><br>
<tt>can't get SET LIST SQL to show anything either. Here is my test results are</tt><br>
<tt>below. I have a index on the bcsapfos_id column but it insists on reading</tt><br>
<tt>every row. Its not a problem in my test data with 643 rows but the</tt><br>
<tt>production table is much larger.</tt><br>
<br>
<tt>The index couldn't be more straightforward. I tried using a plain b-tree</tt><br>
<tt>index too. It did not make a difference.</tt><br>
<tt>CREATE BITMAP INDEX con8_idx2 ON con8 (bcsapfos_id);</tt><br>
<br>
<tt>Any assistance would be *greatly* appreciated.</tt><br>
<br>
<tt>Regards,</tt><br>
<tt>Arnold Chan,</tt><br>
<tt>Registrar's Office,</tt><br>
<tt>Camosun College</tt><br>
<br>
<br>
<tt>> RUN ECE_TEST</tt><br>
<tt>> REQUEST ECE_TEST1</tt><br>
<tt>> ACCESS CON8 IN IFAS_DB</tt><br>
<tt>> SET LIST SQL</tt><br>
<tt>> SELECT CON8 IF (BCSAPFOS_ID = "XDG1")</tt><br>
<tt>> output con8 in ifas_db update</tt><br>
<tt>> item bcsapfos_id = "XDG1"</tt><br>
<tt>> GO</tt><br>
<tt>Executing request ECE_TEST1 ...</tt><br>
<tt>Records read:</tt><br>
<tt> CON8 643</tt><br>
<tt>Transactions processed: 4</tt><br>
<tt>Records processed: Added Updated Unchanged Deleted</tt><br>
<tt> CON8 0 0 4 0</tt><br>
<tt>Finished.</tt><br>
<br>
<tt>---</tt><br>
<tt>--</tt><br>
<tt>= = = = = = = = = = = = = = = = = = = = = = = = = = = =</tt><br>
<tt>Mailing list: <a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a></tt><br>
<tt>Subscribe: 'subscribe' in message body to <a href="mailto:powerh-l-request@lists.sowder.com">
powerh-l-request@lists.sowder.com</a></tt><br>
<tt>Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to</tt><br>
<tt><a href="mailto:powerh-l-request@lists.sowder.com">powerh-l-request@lists.sowder.com</a></tt><br>
</span><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><tt><span style="font-size:10.0pt">http://lists.sowder.com/mailman/listinfo/powerh-l</span></tt></a><span style="font-size:10.0pt;font-family:"Courier New""><br>
<tt>This list is closed, thus to post to the list you must be a subscriber.</tt><br>
<tt>Add 'site:lists.sowder.com powerh-l' to your search terms to search the list</tt><br>
<tt>archive at Google.</tt><br>
<br>
<tt>--</tt><br>
<tt>= = = = = = = = = = = = = = = = = = = = = = = = = = = =</tt><br>
<tt>Mailing list: <a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a></tt><br>
<tt>Subscribe: 'subscribe' in message body to <a href="mailto:powerh-l-request@lists.sowder.com">
powerh-l-request@lists.sowder.com</a></tt><br>
<tt>Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to <a href="mailto:powerh-l-request@lists.sowder.com">
powerh-l-request@lists.sowder.com</a></tt><br>
</span><a href="http://lists.sowder.com/mailman/listinfo/powerh-l"><tt><span style="font-size:10.0pt">http://lists.sowder.com/mailman/listinfo/powerh-l</span></tt></a><span style="font-size:10.0pt;font-family:"Courier New""><br>
<tt>This list is closed, thus to post to the list you must be a subscriber.</tt><br>
<tt>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>
---------------------------- <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></p>
</div>
</body>
</html>