Wow.
We tend to find a solution and use it over and over
until it breaks. That snippit was copied from one
of our existing queries that runs over a 6 million record file
on a Power 7 and it usually runs in about 5 to 10 seconds.
I showed this to the guy that wrote it and his comment was
"It appears to do that same thing, but it's harder to read".
I'll take performance any day.
Thanks Chuck. Always a pleasure to read your replies!
Bill Roehmer
"CONFIDENTIALITY NOTICE: This e-mail transmission (and/or the attachments
accompanying it) contain confidential information belonging to the sender.
The information is intended only for the use of the intended recipient.
If you are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution or the taking of any action in reliance
on the contents of the information is strictly prohibited. Any
unauthorized interception of this transmission is illegal under the law.
If you have received this transmission in error, please promptly notify
the sender by reply e-mail, and then destroy all copies of the
transmission."
From: CRPence <CRPbottle@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Date: 04/08/2015 09:51 AM
Subject: Re: RDI CLP Reformat
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
On 08-Apr-2015 08:45 -0500, broehmer@xxxxxxxxxxxxxxx wrote:
<<SNIP>>
WHERE DATE(INSERT(INSERT(CHAR(RHDATE),5,0,'-'),8,0,'-')) +
BETWEEN CURRENT DATE - 120 DAY AND CURRENT DATE +
<<SNIP>>
FWiW, a few comments about the above snippet of the SQL:
For better performance, if no derived index matching that DATE(...)
expression exists, and especially if an index exists on the column
RHDATE [conspicuously defined as a numeric data type of eight digits
with those digits represented by the date format of YYYYMMDD], convert
the special register values [as effective literal\constant values] into
the matching numeric data type; a conversion happens only once per
effective literal rather than the complex expression performed once per
row:
WHERE RHDATE BETWEEN
DEC(REPLACE(CHAR(CURRENT_DATE - 120 DAYS
, ISO), '-', ''), 8 )
AND DEC(REPLACE(CHAR(CURRENT_DATE
, ISO), '-', ''), 8 )
Note: In place of the DECIMAL [aka DEC] casting scalar, there are
also the INTEGER [aka INT] and ZONED casting scalars or the CAST scalar
available; the chosen casting would be to match the data type\length of
the column RHDATE or a\the keyed access path on that data [which does
not necessarily match the data type of the column of the physical file].
So the expression will be accepted in an environment other than one
established with period as decimal separator [aka Decimal Point (DECPNT)
or (DECMPT)], adding a blank after each comma is desirable [and
mandatory if the expression might be used in a DECPNT(*COMMA)
environment; as a favor to non-USA people referencing the archives for
an apparently functional example, the author having done that already is
a bonus for them]:
WHERE DATE(INSERT(INSERT(CHAR(RHDATE), 5, 0, '-'), 8, 0, '-'))
BETWEEN CURRENT DATE - 120 DAY AND CURRENT DATE
If the dates might include values prior to year 1000, [e.g. year 1],
then use DIGITS scalar in instead of CHAR casting; note, the explicit
specification of CHAR is optional, per implicit cast that would be
applied for the first argument of the INSERT scalar. If changing to use
DIGITS, because the expression would be more dependent on the data
typing of the column named RHDATE, explicit casting to an eight-byte
representation might be required; explicit casting avoids impact to
changes to the data type or length, though changes to numeric-as-date
columns is less typical than other numerics.
midrange.com
