Which is faster?

Tue Apr 4 19:55:55 CDT 2006

Fernando,

Taken straight from the Powerhouse Quiz manual;

"The CHOOSE statement can only be used for indexes; it uses the
record-structure system’s index information to determine which
data records to process. If the primary record-structure is a
relational table, any item in the record-structure can be used on the
CHOOSE statement."

So the CHOOSE statement is meant to be used in varying ways. It depends 
on your context.

I believe CHOOSE has been part of Quiz and QTP before support for 
relational databases was added to Powerhouse. That is the cause of the 
odd language above. It was limited to use on indexes and still is (as 
far as I know) with file structures such as OpenVMS RMS.

Your initial question of "Which is faster" has been well answered.

As far as how PH converts ACCESS statements, I've usually not cared 
much. To me the important thing is how far up the food chain can I push 
a decision. If I can push something to the top, the database engine, 
than that is what I do.

Here is the simplistic way I look at it. CHOOSE and CURSOR statements 
get sent to the db engine. SELECT <table> is executed early, on data 
already parsed by the db engine. As we've learned today, it is tricky 
too. SELECT IF & AND SELECT are executed very late when defines can be 
evaluated based on the previous two items. The earlier something gets 
decided on, the less CPU, IO, etc I expect to use, and therefore the 
faster things will be.

When do I dig into low level access statements? When something I've 
written "sucks eggs"! ;)

Darren

fernando.olmos at hpa.com.au wrote:
> 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
>> **********************************************************************
>>   
>>     