[odb-users] performance of updates in table with many rows for sqlite db

[Boris Kolpackov]

Hi Daniel,

Daniel James <danielpeterjames at gmail.com> writes:

> Actually, I think an even better fix for my problem would be to be
> able to update individual columns.

This is not yet supported directly by ODB.


> I understand ODB includes all columns in the update statement for
> any column. Is it correct that index updates could be avoided if
> 'dumb' columns could be updated in isolation?

I am pretty sure most databases are able to detect that the value
being updated has not changed and thus avoid any expensive operations
(e.g., write the new value to disk or rebuild indexes). However, in
the case of SQLite, anything is possible. I've been surprised by this
database multiple times already. So you may want to write to the SQLite
mailing list and confirm if this is the case.


> I can't remember reading about an ODB interface to drop and rebuild
> indices before and after large updates. Would this be a useful or
> doable functionality do you think? Otherwise I'll try the native SQL
> functionality.

The just-released ODB 2.1.0 provides support for defining database
indexes on data members. However, this is purely for schema generation.
If you need anything fancy like dropping the index before a bunch of
updates and then re-building them after, then you will need to do it
yourself using native SQL.

Boris