I noticed if I execute the following SQL statement:

Update Table1 set Col1 = 2;

it will physically update the rows even for the rows that already have Col1=2. Why?
If the row column already has the new value (in this example 2), why bother updating the row?

I'd like to see EDB optimize the updates similar to:

Update Table1 set Col1 = 2 where Col1<>2;

but have it done automatically behind the scenes. I believe Interbase does this to speed up updates.

Barry

v 2.13B2

On 3/9/2015 5:22 PM, Barry wrote:
> I noticed if I execute the following SQL statement:
> Update Table1 set Col1 = 2;
> it will physically update the rows even for the rows that already have Col1=2. Why?
> If the row column already has the new value (in this example 2), why bother updating the row?

Because one might rely on the actual update action and updating with
same value is "correct" from business logic side.

In our our case we have number of tables where we rely on triggers for
audit trail as well as in some cases to identify rows (recently)
modified (trigger updating a timestamp column).


> I'd like to see EDB optimize the updates similar to:
> Update Table1 set Col1 = 2 where Col1<>2;
> but have it done automatically behind the scenes. I believe Interbase does this to speed up updates.

My vote is against - would royally screw up our customers. The other
reason is that now you have introduced an unintended side-effect.

You can already use the "Col1<>2" or other option i see is to use some
other keyword in the command if you desire such behaviour (for example
something like  "Update Table1 set Col1 = 2 OPTIMIZED" so it's clear
what's happening and can is controlled by developer).


Raul

Raul,

I see your point. Then I'd welcome an OPTIMIZE parameter because my Where clauses can be quite complex and I don't want to have to create a negative set of columns for it. The OPTIMIZE parameter would speed up updates dramatically in the cases where the row values do not change.

Barry

On 3/10/2015 11:38 AM, Barry wrote:
> I see your point. Then I'd welcome an OPTIMIZE parameter because my Where clauses can be quite complex and I don't want to have to create a negative set of columns for it. The OPTIMIZE parameter would speed up updates dramatically in the cases where the row values do not change.

Agreed.

It would be curious to know benefits with more complex types - like
string/varchar or blob/clob where the value compare operation is
non-trivial (compared to int compare).

One more reason for having it as part of sql command so one can
determine per command whether to use it.

Raul

Raul,


>It would be curious to know benefits with more complex types - like
string/varchar or blob/clob where the value compare operation is
non-trivial (compared to int compare).<

With an optimized string comparison rtn, it would of course stop comparing when the first mismatch occurs, or if the string length is different so it should be quite fast.

But like you said, it is up to the developer to add the OPTIMIZE keyword when necessary to his SQL Update statement (or TEDBTable.OptimizeUpdate := True). He would do this if he suspects many of the updates for the current SQL update statement (or TEDBTable) may be superfluous and he wants to cut down on disk access and index updating.

So yes, I'd love to see an OptimizeUdate option for the Update SQL command or even the TEDBTable component.

Barry