Patterns for saving objects to the DB.

[lister]

Hi,

Currently each of the members within my objects have a field in a
table. The objects have a Save() method which writes out all the
members at once in a single SQL statement.

Although this works fine I have been pondering that it's not
particularly efficient to update every field if just one boolean has
changed for example. I have been considering flagging dirty fields
within my object and only writing those out when a Save() is called. I
am, however, wondering whether it's worth the effort to implement
this, and have a few questions:

1) Is MySQL smart enough to realise that certain fields haven't
changed anyway and so to not bother updating any indexes on that
field?

2) Is it really worth worrying about the redundancy for 10-15 field
rows?

3) In researching this a bit, I have found that some people just
serialise the object to a BLOB field. Is this a common method? It
seems a bit nasty to me since it prevents much ability to query the
table, and ties the data to a particular language.

4) How do you guys normally do it?

Many thanks

[ZeldorBlat]

On Mar 19, 9:36 am, lister <[e-mail]:

Hi,

Currently each of the members within my objects have a field in a
table. The objects have a Save() method which writes out all the
members at once in a single SQL statement.

Although this works fine I have been pondering that it's not
particularly efficient to update every field if just one boolean has
changed for example. I have been considering flagging dirty fields
within my object and only writing those out when a Save() is called. I
am, however, wondering whether it's worth the effort to implement
this, and have a few questions:

1) Is MySQL smart enough to realise that certain fields haven't
changed anyway and so to not bother updating any indexes on that
field?

2) Is it really worth worrying about the redundancy for 10-15 field
rows?

You're prematurely optimizing. Do it the straightforward, easy,
maintainable way first, then optimize if you run into specific
performance problems. At this point you should have a much better
idea of what is causing the problem -- and you may be surprised to
find that it isn't what you thought it was.

>
3) In researching this a bit, I have found that some people just
serialise the object to a BLOB field. Is this a common method? It
seems a bit nasty to me since it prevents much ability to query the
table, and ties the data to a particular language.

You seem to already know why this is a bad idea.

>
4) How do you guys normally do it?

Use the ActiveRecord pattern.

[Captain Paralytic]

On 19 Mar, 13:36, lister <[e-mail]:

>
1) Is MySQL smart enough to realise that certain fields haven't
changed anyway and so to not bother updating any indexes on that
field?

Try running an update statement where no data has changed and MySQL
will tell you that no rows were affected.