Select vs Choose -Reply

[Lindley HILL]

Is select <filename> any different from select if .... in this regard? I was always led to believe it was more efficient to have selects by file where applicable rather than one large 'select if' which included all of the conditions.

Lindley Hill
NSW Dept Of Health
Sydney  Aust


>>> "Ohmes, Matt" <Matt.Ohmes@COGNOS.com> 2/September/1999 12:13am >>>
Oh good! Something I know something about!
You are correct Mark.  And so is Michael Lee and Bob Deskin (of course!).  I
am assuming you are talking about relational tables here since you mentioned
everything being done at the database level, but regardless, if you know a
specific value (or values) for an item and you can code that into a CHOOSE,
you will get better results.  It works like this.

If you are talking non-relational, you may only CHOOSE on index items in the
Primary file.  Any other item listed will cause a syntax error.  If you are
talking relational, you may CHOOSE on any item in the Primary file or in the
Cursor. (You can use a Cursor to join several relational tables together
with SQL syntax and then ACCESS the Cursor and CHOOSE on that, if you wish.)

In non-relational files, the CHOOSE will substitute the choose values into
the actual file read, so ONLY those records will be read and returned to
Quiz/QTP.  

In a relational setup, the CHOOSE values will be inserted into the generated
SQL code that is passed to the database where (in theory) the database query
optimizer will find the best method for reading the files.

In both cases, the SELECT will ONLY come into play after the records/rows
have been read AND RETURNED TO QUIZ/QTP. In a worst case scenario, if you
used SELECT instead of CHOOSE when you had the option, you might have to
read and return millions of records/rows to Quiz to have it sift through and
select the 1 you wanted to see.

In relational, you can never be sure how the query optimizer will fulfill
your query request, but it usually faster to let it return just what you
want than to let Quiz filter through the results.  

I remember a specific example where it was taking hours for a result to come
back when the developer was using SELECT on a non-indexed item in a large
relational table.  I told him to try a CHOOSE even though it was not
indexed.  The answer came back in 2 seconds.

Bottom line?  When possible, use a CHOOSE.

Good luck!
Matt

Matt.Ohmes@Cognos.Com
Cognos Corporation
909 E. Las Colinas Blvd.
Suite 1900
Irving, TX  75039
214-259-6200
"Matt doesn't really know anything.  He just likes to pontificate a lot.
We refuse to acknowledge that he works for Cognos or that we have ever
met him or anyone with whom he's ever been associated.  Don't lend him
money and don't let him talk to your sister!" ;-)


-----Original Message-----
From: stewm@canada.com [mailto:stewm@canada.com]
Sent: Monday, August 30, 1999 9:00 PM
To: powerh-l@lists.swau.edu
Subject: Select vs Choose


I was recently in a debate at work about whether it was better to put
selection criteria values in the CHOOSE or the SELECT.  The debate was if
the item in the CHOOSE statement is not in the index of the table that it
has no benefit over coding it in the SELECT statement.  I was always under
the understanding that if can put it in the CHOOSE then do so.  What
difference does it make if the item is in the index?  Does it still speed
things up using a CHOOSE even if the item is not a part of an index?  I
thought the CHOOSE would do everything at the database level and ,not matter
what, retreival would be faster.  The are a few people that think otherwise.

Mark Stewart
Consultants Club
Windsor, Ontario

----------------------------------------------------------
Get your FREE personalized e-mail at http://www.canada.com
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
This list is closed, thus to post to the list, you must be a subscriber.

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
This list is closed, thus to post to the list, you must be a subscriber.