Re: Performance Issue on Large Table

On 21-2-2020 02:24, Chip Beaulieu wrote:
> 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,

I would recommend to not store `Comments` in the same table as 
`Scripture`, or are you trying to re-write the Bible? 😉

This will avoid the deletion of unchanged data, which is inserted later 
on.... (only because `Comments` did change?)


-- 
Luuk

_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users