Which is faster?

Tue Apr 4 17:55:32 CDT 2006

Sorry guys and I don' mean to tell you both how to "suck eggs", but the
CHOOSE statement is *meant to be used* with indexes, and not just any
column. I was thought to believe that a CHOOSE on a non-indexed column,
PH would convert it to a "SELECT tbl IF col = value", so what is the
difference?

I think what I am getting to, with all these questions and this thread
of discussion, is how EXACTLY does PH convert ACCESS statements (or FILE
statements in QDESIGN) into SQL queries? Now this is going into the
bowels of the PH language and how the parser modules of the language
actually work, I guess.

-----Original Message-----
From: Bob.Deskin at Cognos.COM [mailto:Bob.Deskin at Cognos.COM] 
Sent: Wednesday, 5 April 2006 4:45 AM
To: darren.reely at latticesemi.com; Fernando Olmos
Cc: powerh-l at lists.sowder.com
Subject: RE: Which is faster?

Excellent point. And just to be clear as to why this is so useful, for
relational the item can be any column, not just an index segment. So in
the example, it could be

access table_x link order_no to order_no of table_y
choose field1 1234

This should be faster than the SELECT FILE IF because it pushes the
selection on field1 down to the database.

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 Darren Reely
Sent: April 4, 2006 2:26 PM
To: fernando.olmos at hpa.com.au
Cc: powerh-l at lists.sowder.com
Subject: Re: Which is faster?

I'm surprised I haven't seen it mentioned yet. When you can, use CHOOSE 
instead. It pushes the decision into the actual Oracle query that is 
generated. Something you would likely do if you were writing a cursor.

fernando.olmos at hpa.com.au wrote:
>
> 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
>
> _*www.hpa.com.au*_ 
> <file:///H:/Appdata/Microsoft/Signatures/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
> **********************************************************************
>   
-- 
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: "subscribe" in message body to
powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe &lt;password&gt;" in message body to
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.

     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.
**********************************************************************
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
**********************************************************************