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

Groups > comp.programming > #5990

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

Show all headers | View raw

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

Back to comp.programming | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

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

csiph-web