Re: Improving SQL Update performance

Casting is going to occur, whether it be implicit or explicit. The DB2 for i5/OS implicit cast is typically /mapping/ that is performed *after* the calculations, rather than as part of the calculation. So I would guess that the longer time is likely due to these calculations being _completed_ prior to casting to the final result. In the explicit cast, the rule was applied to the expression [the calculations], and could be performed as _part of the evaluation_ of the expression, rather than the casting rule being applied after. Knowing and applying a rule to the intermediate or final result, may effect faster results.

Those with an understanding of what in university might be called /numerical computational methods/ will be able to see that although the expression "ROUND(original / 1000, 2)" would be effectively the same as the expression "original * .001 + .005", they are very different; even if only for the internal rules for evaluation. The former might easily generate a result with greater precision; it does. With that larger precision, the /semi-final/ result has all of its /insignificant digits/ set to zero, which of course would /waste/ time. The _final_ result is in fact the cast, implicit in the /mapping/ to the attributes of the field. Note: zeroing insignificant digits might not seem relevant since the expression being discussed is divide by 1000, but imagine instead divide by 9999, then it should be obvious.

Examine the following table as effective approximations of the result of the /evaluation of the expression/ that is to its right. See how the division takes up many more digits. See how the round of the division is P(31,14) has had to zero all of those extra digits; as compared to the P(23,5) result of the other two.

_result: evaluation of expression_ _expression_
....+....1....+....2....+....3..
518709.00 original
00000000000000518.71000000000000 round(original / 1000, 2)
00000000000000518.71000 round(original * .001, 2)
00000000000000518.71400 original * .001 + .005

If the expressions were cast to eliminate large intermediate results, the process could be improved. Similarly as was given, the semi-final result was cast to reduce the precision of the results. That is why the division is slower than the multiplication, because the rules for the intermediate results have larger amounts of precision, and all beyond the two digits that are kept, must be set to zero. When the CAST() of the full expression is part of the expression, the evaluation includes that rule in its logic, and thus the extra digits in the intermediate result never even need to be calculated. Thus for example, instead of calculating the value 519.22822822822822 for an (ORIGINAL/999) and then the ROUND() changing that intermediate value to 519.230000000000000, the earlier calculations could limit the intermediate result to 519.228 for the (ORIGINAL/999) and then ROUND() changing that to 519.23 in its final DECIMAL(16,2) form.

Regards, Chuck