I have a table with 4.5 million records with full text indexing. Reads are very
fast, but deleting / inserting / updating takes on average about 50 seconds
per record. I often do batches of 30,000 deletes / inserts at a time. The last
batch took 10 hours to complete.
Here are the details:
Table Schema:
CREATE TABLE `Verses` (
`ID` integer PRIMARY KEY AUTOINCREMENT,
`BibleID` integer NOT NULL,
`Book` integer NOT NULL,
`Chapter` integer NOT NULL,
`VerseNumber` integer NOT NULL,
`Scripture` text,
`Comments` text,
FOREIGN KEY(`BibleID`) REFERENCES `Bibles`(`BibleID`) ON DELETE CASCADE,
FOREIGN KEY(`Book`) REFERENCES `Books`(`ID`)
)
Indexes:
CREATE INDEX `INX_Verses_BID` ON `Verses` (`BibleID`)
CREATE INDEX `INX_Verses_BID_B_C` ON `Verses` (`BibleID`,`Book`,`Chapter`)
CREATE INDEX `INX_Verses_BI_B_C_V` ON `Verses`
(`BibleID`,`Book`,`Chapter`,`VerseNumber`)
CREATE INDEX `INX_Verses_B_C_V` ON `Verses` (`Book`,`Chapter`,`VerseNumber`)
Triggers on table:
CREATE TRIGGER after_verses_delete AFTER DELETE ON Verses
BEGIN
—the scripture table is the FTS5 table
DELETE FROM scriptures WHERE ID = old.ID; —the scripture table is the
FTS5 table
END
CREATE TRIGGER after_verses_insert AFTER INSERT ON Verses
BEGIN
—the scripture table is the FTS5 table
INSERT INTO scriptures (ID,Scripture) VALUES(new.ID, new.Scripture);
END
CREATE TRIGGER after_verses_update UPDATE OF Scripture ON Verses
BEGIN
—the scripture table is the FTS5 table
UPDATE scriptures SET Scripture = new.Scripture WHERE ID = old.ID;
END
I run the set of transactions from the command line on a mac using the .read
command. A common scenario deletes 30,000 records, then inserts a new set of
30,000 records. Here are the parameters I set up for the transaction in the
.read file. To be honest, I’ve not seen much of an improvement with these and
since the batch takes so long to complete, I was concerned tweaking other
pragma statements that might introduce risk of corruption on crashes or power
failure.
select time('now','-5 hours');
pragma temp_store=2; --memory
pragma jouurnal_mode=TRUNCATE; --default is DELETE
pragma locking_mode=EXCLUSIVE; --default is NORMAL
BEGIN TRANSACTION;
DELETE FROM Verses WHERE BibleID=38;
INSERT INTO VERSES (BibleID, Book, Chapter, VerseNumber, Scripture)
VALUES(38,1,1,1,'<p>·In the beginning God created the heavens and the earth.');
… repeat 30,000 times with other records
COMMIT;
select time('now','-5 hours’);
I also recently vacuumed the file. The issue really showed up after the FTS5
was setup on the table. I suspect it’s got something to do with the triggers
more than the indexes. I am definitely open to suggestions. I’ve not been able
to find much info on the internet to optimize updates to tables with FTS.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
