RE: [U2] Order of criteria
As I understand it, the query optimizer can shuffle things around as necessary, but the real issue is whether any of the fields are indexed. If the fields are indexed you might get better response by selecting those using an index first, and then subselecting the remaining ones after the initial select has completed. If you try to select an indexed field alongside a couple of nonindexed fields, the query optimizer may or may not choose to use the index; I'm still a bit unclear as to the line there. -Kevin [EMAIL PROTECTED] http://www.PrecisOnline.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Aherne, John Sent: Thursday, February 24, 2005 1:52 PM To: u2-users@listserver.u2ug.org Subject: [U2] Order of criteria Hi All, Does the order of criteria matter in a select or list statement, or is the execution order arbitrary? For example, if I wanted to select vendors who sell apples in Denver, is Select vendors with location='Denver' and products='Apples' the same as Select vendors with products='Apples' and location='Denver' Given that out of 300 vendors, 50 sell apples, and 100 are in denver, and 15 of those in Denver sell apples. If it does matter, should I put the criteria that is likely to return the smallest result set first? TIA, John --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Order of criteria
Generally speaking, you've got it right, John. A few caveats, notes, gotchas: - Use EXPLAIN keyword to get RetrieVe to show you what it is going to do. - If a field is indexed, and no select list is active when the retrieve command starts, the index will be applied before any actual select data lookup on the primary data file. - If your 'products' field were an expensive calculated value like a trans() or t-correlative to another file or an expensive subroutine call, you should probably do it after the selection on simple stored data. - If you have multiple passes over the same file, e.g., A SELECT or 2 followed by LIST, leave the list unsorted until the final pass. That way the intermediate select lists remain in disk order, resulting in less disk reads if multiple records happen to hash to the same group. - Use parentheses to better control your selection criteria. - If it gets too complicated and you want explicit control, pack all your selection criteria into one I-descriptor that returns a true or false. That way it is just basic code that you and the pgmr that follows you 2 years from now can easily understand and maintain. cds -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Aherne, John Sent: Thursday, February 24, 2005 12:52 PM To: u2-users@listserver.u2ug.org Subject: [U2] Order of criteria Hi All, Does the order of criteria matter in a select or list statement, or is the execution order arbitrary? For example, if I wanted to select vendors who sell apples in Denver, is Select vendors with location='Denver' and products='Apples' the same as Select vendors with products='Apples' and location='Denver' Given that out of 300 vendors, 50 sell apples, and 100 are in denver, and 15 of those in Denver sell apples. If it does matter, should I put the criteria that is likely to return the smallest result set first? TIA, John --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Order of criteria
I agree with Kevin's answer. Also, it may depend on which U2 product/pick-like product you are using. I always try to order the WITH statements so the one selecting the fewest records is first. However, I always place the fields with translates last since I know that translates are much more expensive than looking at data within the record. Dick Kryka Director of Applications CCCS of Greater Denver, Inc. Paragon Financial Services 303-632-2226 [EMAIL PROTECTED] Hi All, Does the order of criteria matter in a select or list statement, or is the execution order arbitrary? For example, if I wanted to select vendors who sell apples in Denver, is Select vendors with location='Denver' and products='Apples' the same as Select vendors with products='Apples' and location='Denver' Given that out of 300 vendors, 50 sell apples, and 100 are in denver, and 15 of those in Denver sell apples. If it does matter, should I put the criteria that is likely to return the smallest result set first? TIA, John --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Order of criteria
Under Universe 9.6, I found that it was much faster to do two selects, the first one with just the index and then the second for the rest of the conditions. The new versions are supposed to optimize for indexing and also for tfile conversions. Roger Kevin King wrote: As I understand it, the query optimizer can shuffle things around as necessary, but the real issue is whether any of the fields are indexed. If the fields are indexed you might get better response by selecting those using an index first, and then subselecting the remaining ones after the initial select has completed. If you try to select an indexed field alongside a couple of nonindexed fields, the query optimizer may or may not choose to use the index; I'm still a bit unclear as to the line there. -Kevin [EMAIL PROTECTED] http://www.PrecisOnline.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Aherne, John Sent: Thursday, February 24, 2005 1:52 PM To: u2-users@listserver.u2ug.org Subject: [U2] Order of criteria Hi All, Does the order of criteria matter in a select or list statement, or is the execution order arbitrary? For example, if I wanted to select vendors who sell apples in Denver, is Select vendors with location='Denver' and products='Apples' the same as Select vendors with products='Apples' and location='Denver' Given that out of 300 vendors, 50 sell apples, and 100 are in denver, and 15 of those in Denver sell apples. If it does matter, should I put the criteria that is likely to return the smallest result set first? TIA, John --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Order of criteria
EXPLAIN is Universe-only, not Unidata. Sorry about that. It goes anywhere on the RetrieVe command line. If run from tcl, output is to the screen. -Original Message- From: Don Verhagen Charles, What is the syntax for the EXPLAIN keyword I don't see it documented in Unidata (6.X) I tried: SELECT MY.FILE WITH MY.FILE=SOMETHING EXPLAIN All it did was select the records, does it EXPLAIN in a log file or somewhere else? Thanks, --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
