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

Daniel James danielpeterjames at gmail.com
Mon Sep 17 13:43:56 EDT 2012


On 12 September 2012 12:39, Boris Kolpackov <boris at codesynthesis.com> wrote:
> 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:
>>
>> [...]
>>
[...]
>    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.

Many thanks for the advice. I made a simpler model and found that I
was able to do 40 000 row updates to a table with 6 000 000 rows in
about 2-3 seconds. This seems quite fast actually! I found about 8 000
updates per transaction was effective.

Unfortunately, batching gave little improvement in the unsimplified
model. I think the reason things are going so slowly with the full
model, (about 1 update per second), is down to indices and/or foreign
keys pointing in to the large table. I will make further investigation
into this.

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.


Daniel



More information about the odb-users mailing list