Re: Performance Issue on Large Table

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