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

[Daniel James]

On 17 September 2012 18:43, Daniel James <danielpeterjames at gmail.com> wrote:
> 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.

Actually, I think an even better fix for my problem would be to be
able to update individual columns. 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?

Daniel

>
>
> Daniel