RE: UDT SELECT optimization
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
BartlettSent: Wednesday, February 04, 2004 8:46 AMTo:
'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
TIMEis
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 /
R83background.
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Mark JohnsonSent: 03 February 2004
02:34To: U2 Users Discussion ListSubject: 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 byissuing 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
MongioviSent: 02 February 2004 16:16To: U2 Users
Discussion ListSubject: 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?
-ChuckThis
email was checked by MessageLabs SkyScan before entering
Microgen.This
email was checked on leaving Microgen for viruses, similarmalicious
code and inappropriate content by MessageLabs
SkyScan.DISCLAIMERThis email and any attachments are
confidential and may also beprivileged.If you are not the
named recipient, please notify the senderimmediately and do not
disclose the contents to any otherperson, use it for any purpose, or
store or copy the information.In the event of any technical
difficulty with this email, pleasecontact the sender or
[EMAIL PROTECTED]Microgen Information Management
Solutionshttp://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
Re: UDT SELECT optimization
Title: Message
My example was multiple WITH clauses that are
ANDed. Breaking them up may be more efficient. Your example begs the question of
what is faster:
SELECT ORDERS WITH DATE = "01.01.04" BY
TIME
vs
SELECT ORDERS BY TIME WITH DATE =
"01.01.04"
I tend to put the WITH's first if that
matters.
- Original Message -
From:
Dennis Bartlett
To: 'U2 Users Discussion List'
Sent: Wednesday, February 04, 2004 8:45
AM
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
TIMEis
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 /
R83background.
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Mark JohnsonSent: 03 February 2004
02:34To: U2 Users Discussion ListSubject: 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 byissuing 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
MongioviSent: 02 February 2004 16:16To: U2 Users
Discussion ListSubject: 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?
-ChuckThis
email was checked by MessageLabs SkyScan before entering
Microgen.This
email was checked on leaving Microgen for viruses, similarmalicious
code and inappropriate content by MessageLabs
SkyScan.DISCLAIMERThis email and any attachments are
confidential and may also beprivileged.If you are not the
named recipient, please notify the senderimmediately and do not
disclose the contents to any otherperson, use it for any purpose, or
store or copy the information.In the event of any technical
difficulty with this email, pleasecontact the sender or
[EMAIL PROTECTED]Microgen Information Management
Solutionshttp://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
--
u2-users mailing list
[EMAIL PROTECTED]
http://www.oliver.com/mailman/listinfo/u2-users
UDT SELECT optimization
Title: RE: [UD]LOGTO 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 ___ u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
Re: UDT SELECT optimization
Title: RE: [UD]LOGTO 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 byissuing 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 MongioviSent: 02 February 2004 16:16To: U2 Users Discussion ListSubject: 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? -ChuckThis email was checked by MessageLabs SkyScan before entering Microgen.This email was checked on leaving Microgen for viruses, similarmalicious code and inappropriate content by MessageLabs SkyScan.DISCLAIMERThis email and any attachments are confidential and may also beprivileged.If you are not the named recipient, please notify the senderimmediately and do not disclose the contents to any otherperson, use it for any purpose, or store or copy the information.In the event of any technical difficulty with this email, pleasecontact the sender or [EMAIL PROTECTED]Microgen Information Management Solutionshttp://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
