<HTML >
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<TITLE>Message</TITLE>
<META content="MSHTML 5.50.4943.400" name=GENERATOR></HEAD>
<BODY >
<DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff size=2>First
off, if you use your original access statement, PowerHouse looks at each
table/file as if it was an indexed file. In other words, and not considering any
selects, if you're using a relational database, we do a select on the first
table, fetch the first row, then use the value to construct the select to the
second table, do a select on the second table, fetch the first row in the second
table, the second row, and so on. Once the second table is done, we go back and
fetch the second row of the first table, and construct the select to the second
table and so on.</FONT></SPAN></DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff size=2>I
freely admit that it's not an efficient way to process a relational join.
We have looked at trying to automate generating a proper cursor from the ACCESS,
CHOOSE, and SELECT statements and believe me, it ain't easy. Remember, we're not
talking about just those statements, but the effect on the rest of the syntax.
Plus making it identical with the way relational comes back with missing data.
In any case, that's the way it is.</FONT></SPAN></DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff size=2>So
that's why a proper cursor is so much faster - because it uses the relational
database to do the work. It is potentially orders of magnitude faster. If you're
writing from scratch, as opposed to converting, I strongly encourage you to
consider cursors.</FONT></SPAN></DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff size=2>Now
back to the original question...</FONT></SPAN></DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff size=2>The
SELECT FILE IF does it's processing in the read loop for the associated file. In
this case, for the primary or driving file. Peter had the processing right.
Since the selection takes place against the driving file, potentially fewer of
the linked to file records/rows are read. In the case of SELECT
IF, records/rows from both files are read before the selection takes
place.</FONT></SPAN></DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=642451910-04042006><FONT face=Arial color=#0000ff
size=2>Bob</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com
[mailto:powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com]<B>On Behalf
Of </B>fernando.olmos@hpa.com.au<BR><B>Sent:</B> April 4, 2006 1:05
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=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></BLOCKQUOTE></DIV>
<DIV> </DIV>
<DIV> This message may contain privileged and/or confidential information. If you have received this e-mail in error or are not the intended recipient, you may not use, copy, disseminate or distribute it; do not open any attachments, delete it immediately from your system and notify the sender promptly by e-mail that you have done so. Thank you. </DIV></BODY></HTML>