Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.programming > #5989 > unrolled thread

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

Back to article view | Back to comp.programming

Contents

  Should I exclude non-changed fields from update in DB? Victor Porton <porton@narod.ru> - 2015-11-26 23:48 +0200
    Re: Should I exclude non-changed fields from update in DB? Richard Heathfield <rjh@cpax.org.uk> - 2015-11-26 22:13 +0000

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

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://portonvictor.org

[toc] | [next] | [standalone]

#5990

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.programming

csiph-web