Re: Improving SQL Update performance

Chuck,

Very interesting. I would have supposed that casting added
more time to the process... The villain here seems to be
using the / operator, with ROUND() in a distant second
(other than using things like SETOBJACC, of course).


Regards,

Luis Rodriguez.



--- midrange-l-request@xxxxxxxxxxxx wrote:

------------------------------

message: 5
date: Fri, 30 Nov 2007 15:57:32 -0600
from: CRPence <...>
subject: Re: Improving SQL Update performance

Informal testing only, on an in-use system; i.e. clock
times
especially, may appear suspect. The full table was
loaded into memory
with SETOBJACC with plenty spare, just before each test.

The SQL for each test, the file layout, and the
validation that
rounding was as expected:

update qtemp.updtst set _outcome =_ <see result
matrix>

Field Type Len Dec
ORIGINAL PACKED 19 2
EXPECTED PACKED 16 2
OUTCOME PACKED 16 2

select count(*) from qtemp.updtst
where outcome <> expected

A test for 1.25M rows, five immediate successive
updates:
_clock_ _CPU_ _outcome = _
135.44 108.79 round(original / 1000, 2)
124.74 100.32 round(original * .001, 2)
127.82 98.91 original * .001 + .005 /* target P(x,
2) */

The following as above, but cast to target type
explicitly:
_clock_ _CPU_ _outcome = _
118.83 96.02 decimal( round(original / 1000, 2),
16, 2)
111.38 88.84 decimal( round(original * .001, 2),
16, 2)
109.74 89.11 decimal( original * .001 + .005, 16,
2)

Regards, Chuck
--
All comments provided "as is" with no warranties of any
kind
whatsoever and may not represent positions, strategies,
nor views of my
employer

Luis Rodriguez wrote:

The times I posted are the average of about four runs.

I

admit the 7% is not so significant. Nevertheless, the
difference is a lot more that I could have guessed.

The fields are either Numeric(15, 2) or Packed(15, 2).
Regarding your last suggestion, in this case the order

DOES

matter:

Round( 8089.64 * 0.001, 2) = 8.09000
Round( 8089.64, -2) * 0.001 = 8.10000

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions


____________________________________________________________________________________
Get easy, one-click access to your favorites.
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs