Select vs Choose -Reply

[Deskin, Bob]

Both SELECT and SELECT file operate on the records returned from the
retrieval. They apply the selection at different times and in different
ways. In the following discussion I use the word file, but it could apply to
a table or a cursor.

SELECT file applies the selection condition when the file referenced is
read. If the condition fails, the record is skipped and the next record in
that file is retrieved. In other words, the record complex to that point is
retained. Only if there are no more records and the file is not OPTIONAL
does the record complex get tossed out.

SELECT applies to the entire record complex. However it too is evaluated as
the complex is built so as to reject or pass it as soon as possible.

SELECT file is often used with parallel details because a SELECT would
reject the entire complex but what is probably desired is to bypass a single
record.

There's lots more subtleties, but it would be a paper. Speaking of which, I
believe one of the papers being given by Nick Falkingham at the upcoming
user conference next week deals with the Input Phase of QUIZ and QTP.

Yes this is a shameless plug for both the conference and Nick. Hope to see
you there.

Bob Deskin              
PowerHouse Web Product Manager and Senior Product Advisor
Application Development Tools, Cognos Inc.
bob.deskin@cognos.com (613) 738-1338 ext 4205 FAX: (613) 228-3149
3755 Riverside Drive P.O. Box 9707 Stn. T, Ottawa ON K1G 4K9 CANADA


> ----------
> From: 	Lindley HILL[SMTP:LHILL@doh.health.nsw.gov.au]
> Sent: 	September 2, 1999 7:01 PM
> To: 	stewm@canada.com; Matt.Ohmes@Cognos.COM; powerh-l@lists.swau.edu
> Subject: 	RE: Select vs Choose -Reply
> 
> 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.
> 
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.