Updating columns w/ same value - will DB2 optimize for me?

[allenj]

DB2 UDB 7.2 WSE
Fixpak 9
Linux Red Hat 7.3

I have some library code (written in Java, if that matters) that
processes maintenance screens that are presented to the end-users
as forms in a browser.

Because the code is generic, working against any table, I am
dynamically generating UPDATE statements that update
every column in a row, regardless of whether the value in
a column has actually changed. ie - I might be updating columns
w/ the same value that they already have.

I have though about optimizing this - checking to see what the value
is before I update, and generating a shorter, smarter UPDATE statement
that references only columns whose values have actually changed.

But do I need to be overly concerned about this? Will the DB2 engine
notice that I am updating columns w/ the same value they already have,
and optimize things for me?

Any thoughts appreciated...

aj

[Mark A]

<al****@ndr.com> wrote in message
news:40*****************@news.supernews.com...

DB2 UDB 7.2 WSE
Fixpak 9
Linux Red Hat 7.3

I have some library code (written in Java, if that matters) that
processes maintenance screens that are presented to the end-users
as forms in a browser.

Because the code is generic, working against any table, I am
dynamically generating UPDATE statements that update
every column in a row, regardless of whether the value in
a column has actually changed. ie - I might be updating columns
w/ the same value that they already have.

I have though about optimizing this - checking to see what the value
is before I update, and generating a shorter, smarter UPDATE statement
that references only columns whose values have actually changed.

But do I need to be overly concerned about this? Will the DB2 engine
notice that I am updating columns w/ the same value they already have,
and optimize things for me?

Any thoughts appreciated...

aj

I don't think DB2 will optimize it for you. If you use one update statement
per row, the CPU usage will be a bit more, but physical I/O for the table
should be the same if you update columns that haven't changed. But, there
will be extra I/O (I assume) to update the index columns. Also, DB2 will
need to log the whole row, instead of maybe just logging part of the row.

I think it depends on the volume of the transaction as to whether it is
worth optimizing the SQL dynamically in the program.

[Serge Rielau]

Actually DB2 does detect this at runtime by default.
DB2 logs everything between the first columns changed and the last
column changed.
I can't confirm off hand whether this optimization includes the indexes
or not.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[allenj]

Hi Serge. Thanks for replying.

So you are saying that optimizing my updates will not get me anything?
DB2 is already doing it?
Does this apply to v7? v8? Both?

thanks

aj

On Thu, 05 Feb 2004 13:13:23 -0500, Serge Rielau
<sr*****@ca.eye-be-em.com> wrote:

Actually DB2 does detect this at runtime by default.
DB2 logs everything between the first columns changed and the last
column changed.
I can't confirm off hand whether this optimization includes the indexes
or not.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

For sure for V8. I don't have the impression it's new either.
Again I don't know of hands whether that optimization includes index
updates or only the logs.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:bv**********@hanover.torolab.ibm.com...

For sure for V8. I don't have the impression it's new either.
Again I don't know of hands whether that optimization includes index
updates or only the logs.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

So you are saying that DB2 does not log columns which are not actually
changed even if they are in the "set" clause of the update statement? I am
not talking about columns which are in between the first and last column
changed, which get logged anyway.

[Serge Rielau]

Found it.. google my newest favorite toy :-)
http://www.db2.jp/db2manual/en_US/in...e/c0011223.htm
DB2ASSUMEUPDATE Default=OFF
Values: ON, OFF

When enabled, allows DB2 to assume that all fixed length columns
provided in an UPDATE statement are in fact being changed. This
eliminates the need for DB2 to compare the existing column values to the
new values provided to determine if the column is actually changing.
Using this registry variable when columns are provided for update (for
example, in a SET clause) but are not actually being modified can result
in additional logging and index maintenance.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:bv**********@hanover.torolab.ibm.com...

Found it.. google my newest favorite toy :-)
http://www.db2.jp/db2manual/en_US/in...e/c0011223.htm
DB2ASSUMEUPDATE Default=OFF
Values: ON, OFF

When enabled, allows DB2 to assume that all fixed length columns
provided in an UPDATE statement are in fact being changed. This
eliminates the need for DB2 to compare the existing column values to the
new values provided to determine if the column is actually changing.
Using this registry variable when columns are provided for update (for
example, in a SET clause) but are not actually being modified can result
in additional logging and index maintenance.
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

What about varchar? Are they always logged if in the set statement?

[Serge Rielau]

The way I disect this text is that by default will always verify for ALL
columns whether they have in fact changed. This is the default and what
OP hopes for.
In V8.1.4 the dial was added to assume a well written app and save that
codepath. Presumably there is only a savings for fixed columns (since
they are at fixed locations in the row (?)).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Jonathan Leffler]

al****@ndr.com wrote:

So you are saying that optimizing my updates will not get me anything?

One aspect to consider is how many update statements you are
preparing. If the answer is 1 and you're using placeholders for all
the values, then it is easier to just use that one statement. If,
instead, you write a new statement for each operation, then things are
dramatically slower. If you create one statement for each combination
of N updated columns out of M columns in the table, you end up with a
lot of statements.

And if everything is pre-bound - which DB2 tends to do, but Informix
(my main area of expertise) does not - then the issue may be the size
of your package.

I don't know where the trade-offs occur, nor the relative costings.
However, I'd guess that you're better off using one or a few prepared
statements and living with identity updates on some columns than with
managing hundreds of statements (and C(N,M) gets quite big quite
quickly, if you remember your Pascal's Triangle from maths classes).
--
Jonathan Leffler #include <disclaimer.h>
Email: jl******@earthlink.net, jl******@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/