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

[Daniel James]

On 18 September 2012 11:19, Boris Kolpackov <boris at codesynthesis.com> wrote:
> 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.
>

It appears that updating a row with a foreign key reference is
activating a trigger, even if the child key is unchanged. I have been
able to work around this with:

PRAGMA foreign_keys=OFF;

I've only been able to make this pragma work if used directly after
constructing the db, like this:

AppDelHelper(std::string dbName) : dbp_(new odb::sqlite::database(dbName))
    {
        odb::connection_ptr c(dbp_->connection());
        c->execute("PRAGMA foreign_keys=OFF;");
    }

In other, later, parts of the program the pragma has no effect. I
think this is to do with the following from the sqlite docs:

"It is not possible to enable or disable foreign key constraints in
the middle of a multi-statement transaction (when SQLite is not in
autocommit mode). Attempting to do so does not return an error; it
simply has no effect."

How can I ensure the db _is_ in autocommit mode? Simply being outside
a transaction does not seem to be enough. I'm only accessing the db
with a single thread, is it possible that ODB is keeping the
transaction open beyond than the transaction::commit() call?

Thanks again,

Daniel