[sqlite] fts table insert performance

[Wang Yun]

Hi

I insert rfc txt files into a full text search table, 4119 txt files are
188MB totally. After insert, database file is 443MB.
Logic is below, it's not the real code.

============================
BEGIN_TRANSACTION

CREATE TABLE Notes ( rowid INTEGER PRIMARY KEY, docid, CreateTime,
LastModifyTime )
CREATE VIRTUAL TABLE NotesFTS using fts2( Title, Content )

for( 4119 txt file )
{
open and read file content into a string

INSERT INTO NotesFTS ( Title, Content ) VALUES ( ..., ... )
INSERT INTO Notes ( docid, CreateTime, LastModifyTime ) VALUES (
last_insert_id, DATETIME('NOW'), DATETIME('NOW') )
}

COMMIT
============================

This cost 154 seconds, I use fts2 and my PC is Intel 2.33GHz, 2 CPUs.
If I don't use fts, just insert into normal table, will cost 11 seconds.

I don't know when sqlite will update the full text index, after each insert?
How can I improve the performance?

Thanks,
Wang Yun




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------