Performance Issue on Large Table

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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users