I guess is this slowness is because of triggers (most probably) amplified by extra indexes.
Lets discuss indexes first: if most of queries uses BibleID as SARG along with Book & Chapter then INX_Verses_BID & INX_Verses_BID_B_C are not required. These are unnecessary adding slowness to write activities. Keep minimal indexes. *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`) Now lest's discuss triggers, these I think are the prime bottleneck for your write activity. In fact for such bulk activity Sybase-ASE <http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/X33484.htm> has solely separate command to "Disable Trigger". However this is not present in Sqlite. 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 Thanks, Vikas On Mon, 24 Feb 2020 at 23:38, Olaf Schmidt <n...@vbrichclient.com> wrote: > Am 23.02.2020 um 20:23 schrieb Richard Damon: > > >> An amount of 140 tables in such a "BibleVersions.db" is not > >> uncommon and can be managed by SQLite in a good performance. > > > > I'm not sure that form of division would be good. One basic rule of > > database normalization is that you don't break-up data based on the > > value of one of the fields ... > > Sure, but FTS-"Tables" are in a "special category" (IMO)... > > At least, I'd separate them "by language", because it does > not really make sense to me, to stuff e.g. the 31102 verses > of a japanese Bible-version into the same FTS-index, > where already one (or more) english versions reside. > > Between japanese and english that's obvious already at the > "Unicode(Point)-level" - but even among "Latin-versions" > (e.g. english and german ones) there might be different > stemmer-algos to consider, to give optimal search-results. > > Olaf > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
