On Wed, 8 Mar 2006 [EMAIL PROTECTED] wrote:
>----- Original Message ----
>From: [EMAIL PROTECTED]
>To: sqlite-users@sqlite.org
>
>> SQLite inserts in records in primary key order. (That is not
>> strictly true - but it is close enough to being true for the
>> purposes of what follows.) So when you insert records that
>> are already in fname order, they can be appended to the file
>> and the insert goes relatively quickly. This is because the
>> file does not need to be reorganized to make space for the
>> new row (it is simply appended) and because nearby information
>> is already in cache and can be fetched quickly. But if you
>> insert with randomly ordered fnames, then records are constantly
>> being inserted into different places in the middle of the file.
>> This takes more time because the file has to be reorganized
>> to make space for the new record and because each record is
>> in a different spot you are have no locality of reference
>> and the cache is much less effective.
>
>OK, now I understand better why there is such a difference in
>performance. It seems that sqlite maybe is not an option for me as I am
>updating records pretty often (50% of the time roughly: access some data,
>perform some task with it and later write back the updated version).
Check your test on another DB. My guess is that this will be as fast as
you will get. A database such as PGSQL or MySQL might mitigate the
non-localised updates by using a linear redo log, thus cutting synchronous
seeks, but they have other overheads that might negate that advantage.
If you can batch updates into a small number of transactions, SQLite is
probably the best tool for the job from your description. You sound like
you're doing read/modify/write cycles, which might well be batchable.
>
>One question though: are the file access "sorted", so that seeks are
>minimised when performing a transaction (making the assumption that the
>file is not fragmented on disk)?
The OS will sort IO into whatever order it sees fit. Reads will be done in
order, and writes will be likely be written to the disk in ascending disk
block order. SQLite can do little to affect this policy.
>
>Nicolas
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
