<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META content="MSHTML 6.00.2900.2802" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=301050405-04042006><FONT face=Arial color=#0000ff
size=2>Ken,</FONT></SPAN></DIV>
<DIV><SPAN class=301050405-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=301050405-04042006><FONT face=Arial color=#0000ff
size=2>Thanks, but no thanks (*wink*). I am trying to avoid cursors, to simplify
the code for other developers as much as possible. I would prefer to use them of
course, but we have people here that jump at "new and improved" ways of doing
things. ;)</FONT></SPAN></DIV>
<DIV><SPAN class=301050405-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=301050405-04042006><FONT face=Arial color=#0000ff
size=2>cheers</FONT></SPAN></DIV>
<DIV><SPAN class=301050405-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Ken@Langendock.com
[mailto:Ken@Langendock.com] <BR><B>Sent:</B> Tuesday, 4 April 2006 3:00
PM<BR><B>To:</B> Fernando Olmos; powerh-l@lists.sowder.com<BR><B>Subject:</B>
RE: Which is faster?<BR><BR></FONT></DIV>
<DIV><SPAN class=389445704-04042006><FONT face=Arial color=#0000ff size=2>No
"select tbl If" is faster. It does not access the second file if it doesn't pass
the select on the first file.</FONT></SPAN></DIV>
<DIV><SPAN class=389445704-04042006><FONT face=Arial color=#0000ff size=2>It is
faster than select if.</FONT></SPAN></DIV>
<DIV><SPAN class=389445704-04042006><FONT face=Arial color=#0000ff size=2>But
cursors are incredibly faster.</FONT></SPAN></DIV>
<DIV><SPAN class=389445704-04042006><FONT face=Arial color=#0000ff size=2>run a
test on your data and see the results.</FONT></SPAN></DIV>
<DIV><SPAN class=389445704-04042006></SPAN> </DIV>
<DIV><SPAN class=389445704-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com
[mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com] <B>On
Behalf Of </B>fernando.olmos@hpa.com.au<BR><B>Sent:</B> April 4, 2006 12:48
AM<BR><B>To:</B> powerh-l@lists.sowder.com<BR><B>Subject:</B> RE: Which is
faster?<BR><BR></FONT></DIV>
<DIV><SPAN class=960024704-04042006><FONT face=Arial color=#0000ff size=2>So in
the case of PH and Oracle, a SELECT IF and a SELECT tbl IF makes no difference
in performance? But that contradicts my tests.</FONT></SPAN></DIV>
<DIV><SPAN class=960024704-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=960024704-04042006><FONT face=Arial color=#0000ff size=2>PH
must be telling Oracle to filter "better" if I say SELECT tbl
IF.</FONT></SPAN></DIV>
<DIV><SPAN class=960024704-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Ken@Langendock.com
[mailto:Ken@Langendock.com] <BR><B>Sent:</B> Tuesday, 4 April 2006 2:46
PM<BR><B>To:</B> Fernando Olmos; powerh-l@lists.sowder.com<BR><B>Subject:</B>
RE: Which is faster?<BR><BR></FONT></DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Arial color=#0000ff size=2>If you
are using Oracle you have plenty more options.</FONT></SPAN></DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Arial color=#0000ff size=2>Yes
the query is going to run in the background, pull all the records back, quiz is
then going to filter them.</FONT></SPAN></DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Arial color=#0000ff size=2>You
really should use a cursor. This gets filtered before it returns the results to
QUIZ.</FONT></SPAN></DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Arial color=#0000ff size=2>This
is incredibly fast.</FONT></SPAN></DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Courier color=#0000ff size=2>SQL
IN mydatabase &</FONT></SPAN></DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Courier color=#0000ff
size=2> DECLARE C_DATA CURSOR FOR &<BR>
SELECT field1.table_x, &<BR>
field2.table_x, & ; only put the
fields that you need<BR>
field1.table_y, &<BR>
field2.table_y &<BR>
FROM table_x, &</FONT></SPAN></DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Courier color=#0000ff
size=2>
table_y
&<BR> WHERE table_x.field1 = '1234' and
&<BR>
table_y.order_no = table_x.order_no<BR></FONT></SPAN></DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Courier color=#0000ff
size=2><FONT face=Arial>ACCESS C_DATA</FONT></FONT></SPAN></DIV>
<DIV><SPAN class=530523704-04042006><FONT face=Courier color=#0000ff
size=2><FONT face=Arial></FONT> </DIV></FONT></SPAN>
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com
[mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com] <B>On
Behalf Of </B>fernando.olmos@hpa.com.au<BR><B>Sent:</B> April 4, 2006 12:35
AM<BR><B>To:</B> powerh-l@lists.sowder.com<BR><B>Subject:</B> RE: Which is
faster?<BR><BR></FONT></DIV>
<DIV><SPAN class=860493204-04042006><FONT face=Arial color=#0000ff size=2>I am
using QUIZ, but I know what you mean in QTP. The database is
Oracle.</FONT></SPAN></DIV>
<DIV><SPAN class=860493204-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=860493204-04042006><FONT face=Arial color=#0000ff
size=2>However, I am not sure what the "input phase" really means. Is an ACCESS
statement going to run a Oracle query in the background and then return the
records to PH to filter out (the SELECT IF), or is PH going to filter the data
in the query before retrieving the records (as in SELECT tbl
IF)?</FONT></SPAN></DIV>
<DIV><SPAN class=860493204-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Ken@Langendock.com
[mailto:Ken@Langendock.com] <BR><B>Sent:</B> Tuesday, 4 April 2006 2:17
PM<BR><B>To:</B> Fernando Olmos; powerh-l@lists.sowder.com<BR><B>Subject:</B>
RE: Which is faster?<BR><BR></FONT></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff size=2>Hello
Fernando, </FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff size=2>You
didn't specify if you were using QTP or QUIZ.</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff size=2>You
also didn't tell us the database (Oracle, ISAM, Image, RDB,
etc)</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff size=2>If you
are using QTP, another way is to open the second file in the output
phase.</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff size=2>Access
table_x</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff size=2>
select if field1 of table_x = 1234</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff size=2>Output
table_y &</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2> add
&</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2> update
&</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2> if 1 eq 2
&</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2> noitems
&</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2> via order_no
&</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2> using
order_no</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff size=2>
set file table_y open read.</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff size=2>I
found that by opening every file, that is not required in the input phase, this
way speed up the processing dramatically. This way the input buffers are smaller
for processing/sorting etc.</FONT></SPAN></DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=249371104-04042006><FONT face=Arial color=#0000ff
size=2>Ken</FONT></SPAN></DIV>
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com
[mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com] <B>On
Behalf Of </B>fernando.olmos@hpa.com.au<BR><B>Sent:</B> April 3, 2006 11:54
PM<BR><B>To:</B> powerh-l@lists.sowder.com<BR><B>Subject:</B> Which is
faster?<BR><BR></FONT></DIV><!-- Converted from text/rtf format -->
<P><FONT face=Arial size=2>I have a table (X) which has over 200 thousand
records and is linked to another table (Y) as one-to-one.</FONT> </P>
<P><FONT face=Arial size=2>I need to select certain records from the entire
complex, and I was wondering which one of these would do it faster?</FONT> </P>
<P><FONT face=Arial size=2>[1] access table_x link
order_no to order_no of table_y</FONT>
<BR> <FONT face=Arial size=2>select if
field1 of table_x = 1234</FONT> </P>
<P><FONT face=Arial size=2>of is this faster?</FONT> </P>
<P><FONT face=Arial size=2>[2] access table_x link
order_no to order_no of table_y</FONT>
<BR> <FONT face=Arial size=2>select
table_x if field1 of table_x = 1234</FONT> </P>
<P><FONT face=Arial size=2>I figured that either way is the same, but I've timed
the 2nd option and it's remarkably faster. Is this because PH is actually
filtering out the records in the query, at the table level, if you say "select
table if", as opposed to waiting for the entire record complex and then applying
the filter?</FONT></P>
<P><FONT face=Arial size=2>Thanks guys</FONT> </P>
<P><I><FONT face=Arial color=#0000ff size=4>Fernando Olmos</FONT></I>
<BR><B><FONT face=Arial size=2>MIS</FONT></B> <BR><B><FONT face=Arial
size=2>Senior Analyst Programmer</FONT></B> </P>
<P><SPAN lang=en-au><B><FONT face=Arial color=#0000ff
size=2>HPA</FONT></B></SPAN><B><SPAN lang=en-us></SPAN></B><SPAN
lang=en-us></SPAN><SPAN lang=en-us></SPAN><SPAN lang=en-us><FONT
face=Arial><BR></FONT></SPAN><SPAN lang=en-au></SPAN><SPAN lang=en-au><FONT
face=Arial color=#000000 size=2>Direct: 03 9217 5411</FONT></SPAN><SPAN
lang=en-us></SPAN><SPAN lang=en-us></SPAN><SPAN lang=en-us><BR></SPAN><SPAN
lang=en-us></SPAN><SPAN lang=en-au></SPAN><SPAN lang=en-au></SPAN><SPAN
lang=en-au><FONT face=Arial color=#000000 size=2>Mobile:
0410 382 857</FONT></SPAN><SPAN lang=en-us></SPAN><SPAN
lang=en-us></SPAN><SPAN lang=en-us><FONT face=Arial><BR></FONT></SPAN><SPAN
lang=en-au></SPAN><SPAN lang=en-au><FONT face=Arial color=#000000
size=2>Fax: 03 9217 5716</FONT><FONT
face=Arial></FONT></SPAN><SPAN lang=en-us></SPAN><SPAN lang=en-us> </SPAN></P>
<P><SPAN lang=en-au></SPAN><A
href="file:///H:/Appdata/Microsoft/Signatures/www.hpa.com.au"><SPAN
lang=en-au></SPAN><SPAN lang=en-au><U></U></SPAN><SPAN lang=en-au><U><B><FONT
face=Arial color=#0000ff size=2>www.hpa.com.au</FONT></B></U></SPAN><SPAN
lang=en-au></SPAN></A><SPAN lang=en-au></SPAN><SPAN lang=en-au></SPAN><SPAN
lang=en-us></SPAN><SPAN lang=en-us></SPAN><SPAN lang=en-us></SPAN><SPAN
lang=en-us></SPAN> </P><BR><PRE>**********************************************************************
IMPORTANT
The contents of this e-mail and its attachments are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you received this e-mail in error, please notify the HPA Postmaster, postmaster@hpa.com.au,
then delete the e-mail.
This footnote also confirms that this e-mail message has been swept for the
presence of computer viruses by Ironport. Before opening or using any
attachments, check them for viruses and defects.
Our liability is limited to resupplying any affected attachments.
HPA collects personal information to provide and market our services. For more
information about use, disclosure and access see our Privacy Policy at
www.hpa.com.au
**********************************************************************
</PRE><PRE>**********************************************************************
IMPORTANT
The contents of this e-mail and its attachments are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you received this e-mail in error, please notify the HPA Postmaster, postmaster@hpa.com.au,
then delete the e-mail.
This footnote also confirms that this e-mail message has been swept for the
presence of computer viruses by Ironport. Before opening or using any
attachments, check them for viruses and defects.
Our liability is limited to resupplying any affected attachments.
HPA collects personal information to provide and market our services. For more
information about use, disclosure and access see our Privacy Policy at
www.hpa.com.au
**********************************************************************
</PRE><PRE>**********************************************************************
IMPORTANT
The contents of this e-mail and its attachments are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you received this e-mail in error, please notify the HPA Postmaster, postmaster@hpa.com.au,
then delete the e-mail.
This footnote also confirms that this e-mail message has been swept for the
presence of computer viruses by Ironport. Before opening or using any
attachments, check them for viruses and defects.
Our liability is limited to resupplying any affected attachments.
HPA collects personal information to provide and market our services. For more
information about use, disclosure and access see our Privacy Policy at
www.hpa.com.au
**********************************************************************
</PRE><pre>**********************************************************************
IMPORTANT
The contents of this e-mail and its attachments are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you received this e-mail in error, please notify the HPA Postmaster, postmaster@hpa.com.au,
then delete the e-mail.
This footnote also confirms that this e-mail message has been swept for the
presence of computer viruses by Ironport. Before opening or using any
attachments, check them for viruses and defects.
Our liability is limited to resupplying any affected attachments.
HPA collects personal information to provide and market our services. For more
information about use, disclosure and access see our Privacy Policy at
www.hpa.com.au
**********************************************************************
</pre></BODY></HTML>