Which is faster?

Tue Apr 4 05:31:53 CDT 2006

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.

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.

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.

Now back to the original question...

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.

Bob

-----Original Message-----
From: powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com [mailto:powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com]On Behalf Of fernando.olmos at hpa.com.au
Sent: April 4, 2006 1:05 AM
To: powerh-l at lists.sowder.com
Subject: RE: Which is faster?

Ken,

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.    ;)

cheers

-----Original Message-----
From: Ken at Langendock.com [mailto:Ken at Langendock.com] 
Sent: Tuesday, 4 April 2006 3:00 PM
To: Fernando Olmos; powerh-l at lists.sowder.com
Subject: RE: Which is faster?

No "select tbl If" is faster. It does not access the second file if it doesn't pass the select on the first file.
It is faster than select if.
But cursors are incredibly faster.
run a test on your data and see the results.

-----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 fernando.olmos at hpa.com.au
Sent: April 4, 2006 12:48 AM
To: powerh-l at lists.sowder.com
Subject: RE: Which is faster?

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.

PH must be telling Oracle to filter "better" if I say SELECT tbl IF.

-----Original Message-----
From: Ken at Langendock.com [mailto:Ken at Langendock.com] 
Sent: Tuesday, 4 April 2006 2:46 PM
To: Fernando Olmos; powerh-l at lists.sowder.com
Subject: RE: Which is faster?

If you are using Oracle you have plenty more options.
Yes the query is going to run in the background, pull all the records back, quiz is then going to filter them.

You really should use a cursor. This gets filtered before it returns the results to QUIZ.
This is incredibly fast.

SQL IN mydatabase &
    DECLARE C_DATA CURSOR FOR &
     SELECT field1.table_x, &
            field2.table_x, & ; only put the fields that you need
            field1.table_y, &
            field2.table_y &
       FROM table_x, &
            table_y &
      WHERE table_x.field1 = '1234' and &
            table_y.order_no = table_x.order_no

ACCESS C_DATA

-----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 fernando.olmos at hpa.com.au
Sent: April 4, 2006 12:35 AM
To: powerh-l at lists.sowder.com
Subject: RE: Which is faster?

I am using QUIZ, but I know what you mean in QTP. The database is Oracle.

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)?

-----Original Message-----
From: Ken at Langendock.com [mailto:Ken at Langendock.com] 
Sent: Tuesday, 4 April 2006 2:17 PM
To: Fernando Olmos; powerh-l at lists.sowder.com
Subject: RE: Which is faster?

Hello Fernando, 
You didn't specify if you were using QTP or QUIZ.
You also didn't tell us the database (Oracle, ISAM, Image, RDB, etc)

If you are using QTP, another way is to open the second file in the output phase.

Access table_x
  select if field1 of table_x = 1234

Output table_y &
          add &
          update &
          if 1 eq 2 &
          noitems &
          via order_no &
         using order_no

  set file table_y open read.

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.

Ken
-----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 fernando.olmos at hpa.com.au
Sent: April 3, 2006 11:54 PM
To: powerh-l at lists.sowder.com
Subject: Which is faster?

I have a table (X) which has over 200 thousand records and is linked to another table (Y) as one-to-one. 

I need to select certain records from the entire complex, and I was wondering which one of these would do it faster? 

[1]     access table_x link order_no to order_no of table_y 
        select if field1 of table_x = 1234 

of is this faster? 

[2]     access table_x link order_no to order_no of table_y 
        select table_x if field1 of table_x = 1234 

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?

Thanks guys 

Fernando Olmos 
MIS 
Senior Analyst Programmer 

HPA
Direct:  03 9217 5411
Mobile: 0410 382 857
Fax:     03 9217 5716 

 <file:///H:/Appdata/Microsoft/Signatures/www.hpa.com.au> www.hpa.com.au 

**********************************************************************

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 at 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

**********************************************************************
**********************************************************************

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 at 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

**********************************************************************
**********************************************************************

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 at 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

**********************************************************************
**********************************************************************

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 at 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

**********************************************************************

     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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20060404/0ec93891/attachment-0001.html