RE: UDT SELECT optimization

[Chuck Mongiovi]

Title: Message

i seem to remember this way of thinking from the "old" days too, but i thought that all of the newer sql based command parser / optimizers didn't care what order you put things in .. they made sure to do the filter part first, and then the sort part .. i was hoping that this way of thinking naturally made it's way into u2 .. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Dennis Bartlett Sent: Wednesday, February 04, 2004 8:46 AM To: 'U2 Users Discussion List' Subject: RE: UDT SELECT optimization I've always thought that a SELECT that reduces the results first then SORTS would be quicker, eg          SELECT ORDERS WITH DATE = "01/01/04" BY TIME where          WITH DATE = "01/01/04"    is what reduces the results and          BY TIME                           is the selection criteria   based on the logic that  the reduced list would be quicker to sort... Dunno where I picked up this thought process, but I am from a Prime / R83 background. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Johnson Sent: 03 February 2004 02:34 To: U2 Users Discussion List Subject: Re: UDT SELECT optimization Cached in memory is correct as well as the second statement will only have to process fewer records if the WITH was done in the first. Breaking up a SELECT statement into 2 parts may be advantageous, regardless of caching, if there are multiple ANDed WITH clauses and one or more of them involve translates. For example:   SSELECT HUGE.ARCHIVE.FILE WITH CUST.ZIP = "12345" AND WITH PERIOD = "199910"   would be faster as SELECT HUGE.ARCHIVE.FILE WITH PERIOD = "199910" then SSELECT HUGE.ARCHIVE.FILE WITH CUST.ZIP = "12345"   assuming that CUST.ZIP is translated and the PERIOD is a local field. Note the SSELECT in the second statement as well.   I've often wondered if, when using consecutive ANDs the filter processor skips the record if the first WITH is false. Why bother testing the second field if both are ANDed.   my 1 cent. ----- Original Message ----- From: Brian Leach To: 'U2 Users Discussion List' Sent: Monday, February 02, 2004 11:24 AM Subject: RE: UDT SELECT optimization Chuck,   The order should be immaterial ( it is on every other database I can think of ).   Just be aware that if you test it by issuing the commands one after the other, the second may may complete quicker, simply because the file may be cached in memory.   Brian Leach From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chuck Mongiovi Sent: 02 February 2004 16:16 To: U2 Users Discussion List Subject: UDT SELECT optimization Does anyone know whether one of these two statements is faster?   SELECT FILE BY SOME.DATE WITH SOME.FIELD = "XXX" -or- SELECT FILE WITH SOME.FIELD = "XXX" BY SOME.DATE   Does the parser / optimizer (if there is one) do the filter portion of the statement first regardless of the order that you build it in or does it do things EXACTLY in the order that you enter them on the command line?   -Chuck ________________________________________________________________________ This email was checked by MessageLabs SkyScan before entering Microgen. ________________________________________________________________________ This email was checked on leaving Microgen for viruses, similar malicious code and inappropriate content by MessageLabs SkyScan. DISCLAIMER This email and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and do not disclose the contents to any other person, use it for any purpose, or store or copy the information. In the event of any technical difficulty with this email, please contact the sender or [EMAIL PROTECTED] Microgen Information Management Solutions http://www.microgen.co.uk _______________________________________________ u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users

-- 
u2-users mailing list
[EMAIL PROTECTED]
http://www.oliver.com/mailman/listinfo/u2-users