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

[Boris Kolpackov]

Hi Daniel,

Daniel James <danielpeterjames at gmail.com> writes:

> I store about 6 million of these in a table. Then I try and update a
> subset of about 40 000.
> 
> What is the quickest way to do this? At the moment it is proceeding at
> a rate of about one update per second with the following SQL being
> generated at each update:
> 
> [...]
> 
> Do I need to:
> 
> rethink the database design?
> somehow use an index on the Record table to speed things up?
> other strategy?

There are two areas I would explore first:

1. How do you find records that you need to update? Do you know their
   object ids and just load them? Or do you do a query? If it is the
   latter, then it would be good to understand which part of that
   time is spent querying and which -- updating. Also, in this case,
   an index can definitely help.

2. How many updates do you perform per transaction? If you do each
   update in a separate transaction, then that would explain the
   times you are seeing. In this case, the database has to wait
   until (some part of) the data is physically written to the disk
   before it can return from the call to commit(). Similarly, if
   you do all 40,000 updates in a single transaction, things can
   get slow because of all the housekeeping data that the database
   has to keep around until the transaction is committed or rolled
   back.

   So, generally, there is an optimum number of updates that you need
   to perform per transaction to achieve the best performance. And the
   way to find this number is through some experimentation.

Boris