On 08-Apr-2015 10:57 -0500, broehmer@xxxxxxxxxxxxxxx wrote:
On 08-Apr-2015 09:51 -0500, CRPence wrote:
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 )
<<SNIP>>
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.
<<SNIP>>
If the original WHERE clause runs that quickly against a
multi-million row table, thn perhaps the DATE(...) expression is coded
into an existing access path and the SQL engine appropriately chose that
derived-key INDEX? Or possibly a table-scan query can be that quick on
the noted hardware?
Yet composing a query that forces a table-scan implementation is
unnecessarily resource-intensive. Running such a query will have a
negative impact on other work across the system. Thus a revision to
avoid the table-scan is valuable, irrespective of any desire to achieve
better performance of just that specific query.
FWiW: I do not see how the SQL engine could be smart enough to know
to effect the appropriate conversion of the [special registers as]
effective literal values, into a data type that matches an existing
access path on the RHDATE data, so that surely can not explain that
seemingly quick result over so many rows.
If the WHERE clause I offered is "harder to read" than the original
[a claim I find disingenuous given a casting function inside a doubly
nested INSERT scalar inside of a DATE casting is actually one function
_more_ than a casting function inside a REPLACE scalar inside a DECIMAL
casting function], then a simplification for either of the two BETWEEN
predicates is quite trivial:
The original coding might be rewritten as [using a scalar UDF named
S8toDATE that accepts a NUMERIC(8) as the only argument and RETURNS
DATE]; however this approach eliminates the possibility to use a
derived-key index, because a UDF can be part of the key expression, and
unless the function is /inlined/ by the query will necessarily perform
worse than the written-out conversion due to the function being invoked
separately for at least each distinct value of RHDATE:
WHERE S8toDATE(RHDATE)
BETWEEN CURRENT DATE - 120 DAY AND CURRENT DATE
My suggested coding might be rewritten as [using a scalar UDF named
DATEtoS8 that accepts a DATE as the only argument and RETURNS
NUMERIC(8); this approach as zero impact on the ability to use a key,
and in fact practically ensure any matching keyed access path would be
found irrespective the use in implementation:
WHERE RHDATE BETWEEN DATEtoS8( CURRENT_DATE - 120 DAYS )
AND DATEtoS8( CURRENT_DATE )
The source for creating both UDFs is quite trivial [I did not review
for the best clauses, just explicitly DETERMINISTIC]:
create function s8toDate
( num8 numeric(8)
) returns date
language sql deterministic
return /* v5r3 required date(timestamp) but should not */
/*timestamp(digits(num8) concat '000000') */
date(timestamp(digits(num8) concat '000000'))
create function dateToS8
( dt date
) returns numeric(8)
language sql deterministic
return
replace(char(dt, ISO), '-', '')
midrange.com
