On Wed, Oct 19, 2011 at 12:50 PM, Fabian <fabianpi...@gmail.com> wrote:
> 2011/10/19 Scott Hess <sh...@google.com>
>> To be clear, how it works is that new insertions are batched into a
>> new index tree, with index trees periodically aggregated to keep
>> selection efficient and to keep the size contained. So while the
>> speed per insert should remain pretty stable constant, periodically an
>> insert will require index maintenance, so that insert will be slower.
>> If you have a lot of documents (or a small page cache) these
>> maintenance events can get pretty expensive relative to the cost of a
>> non-maintenance insert. So it's not a clear-cut win, but it probably
>> would be interesting as an alternative sort of index for some tables.
>
> I always do inserts in batches of 100.000 rows, and after each batch I
> manually merge the b-trees using:
>
> INSERT INTO table(table) VALUES('optimize');
>
> Is there a possibility that it will do automatic maintenance half-way during
> a batch? Or will it always wait untill the transaction is finished?
It does it when it does it, in fact you're probably getting some small
merges during this process already. If you're doing your batch
inserts within a surrounding transaction, and are inserting documents
by ascending docid (or letting the system choose docid), it can buffer
up many updates in memory before flushing them to disk, which is
pretty efficient. Inserting 100,000 documents this way will probably
not hit any very large merges, unless your documents tend to have a
very large number of unique terms.
-scott
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
