Re: Update

Albert Wisse wrote: > > I have the following situation: > > I have seen when tracing an application the following update statement(s), > by example > > update table01 > set (a, b) = ('xxx', 'yyyy') > where a = 'xxx' > > On column a exists an unique index. > > I wondered why the application updates the column on which its selects. > > If I am not mistaken is updating the a column (very) bad for performance > there the unique constraint has to verified, locks live longer, etc. > My question is, is their more to tell about this kind of update, to help my > to convince the designer to alter the update statements such as a higher > chance on deadlocks, lock time-outs etc. You are correct. It is bad form and bad for performance, though VERY common, to update a column with it's current value. This should be changed. It usually results from general purpose code, written by lazy programmers, which can update any column the user modifies because it simply updates them all without determining which have changed. In general this is lazy but harmless, in the case of the table's keys it is just short of unforgivable since, except in special circumstances, the users are not going to be presented with the option of modifying the key columns anyway. And in those special circumstances, this almost invariably involves special coding to update parent and child records to match the keys. Get the programmer to fix it. Art S. Kagel