[lnkForumImage]
TotalShareware - Download Free Software

Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.programming

Should I exclude non-changed fields from update in DB?

Victor Porton

11/26/2015 9:48:00 PM

I am writing a lightweight ORM for SQL in Perl.

I am now developing a kind of an active record.

This active record uses lazy-loading to facilitate performance.

As a consequence from lazy-loading, at update() statement it may be
sometimes known or sometimes unknown the old value of a field which is going
to be updated.

My question: If the old value of a field is known and moreover the update()
is going to change it's value to the same as the old one (that is not going
to change it at all), then should I exclude this field from UPDATE SQL
statement?

I have the following thoughts on this:

To exclude it from UPDATE statement may improve performance of the server a
little (I assume Perl comparison of two variables is faster than writing to
the server.)

To exclude it from UPDATE statement my in principle break relational
validity in the case of a race condition. (Consider toy example when a table
t has INT fields a and b and it always must be b=a+1. In this case when we
run `UPDATE t SET a=?` instead of `UPDATE t SET a=?, b=a+1` under assumption
that b is already one above a, this in principle could break relational
validity.) Note that our company uses MyISAM, not a transactional DB. (I
know that this is wrong.)

I have no other thoughts on this topic. So I am interested in your advice.

--
Victor Porton - http://porton...
1 Answer

Richard Heathfield

11/26/2015 10:14:00 PM

0

On 26/11/15 21:48, Victor Porton wrote:
> I am writing a lightweight ORM for SQL in Perl.
>
> I am now developing a kind of an active record.
>
> This active record uses lazy-loading to facilitate performance.
>
> As a consequence from lazy-loading, at update() statement it may be
> sometimes known or sometimes unknown the old value of a field which is going
> to be updated.
>
> My question: If the old value of a field is known and moreover the update()
> is going to change it's value to the same as the old one (that is not going
> to change it at all), then should I exclude this field from UPDATE SQL
> statement?
>
> I have the following thoughts on this:
>
> To exclude it from UPDATE statement may improve performance of the server a
> little (I assume Perl comparison of two variables is faster than writing to
> the server.)
>
> To exclude it from UPDATE statement my in principle break relational
> validity in the case of a race condition. (Consider toy example when a table
> t has INT fields a and b and it always must be b=a+1. In this case when we
> run `UPDATE t SET a=?` instead of `UPDATE t SET a=?, b=a+1` under assumption
> that b is already one above a, this in principle could break relational
> validity.) Note that our company uses MyISAM, not a transactional DB. (I
> know that this is wrong.)
>
> I have no other thoughts on this topic. So I am interested in your advice.

Premature optimisation is the root of all evil, so my instinct would be
to go with updating the entire record, because it makes the code simpler
and clearer. It may well be that the server optimises writing by not
bothering to update fields that have not changed. (Or, of course, it may
well not be.)

So I would suggest the simple route. Then *measure*. If and only if it's
too slow (and you should define in advance what you mean by "too slow",
before measuring) should you think about possible optimisations.

--
Richard Heathfield
Email: rjh at cpax dot org dot uk
"Usenet is a strange place" - dmr 29 July 1999
Sig line 4 vacant - apply within