----- 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). 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)? Nicolas
