Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

[Joe Mistachkin]

Assuming no blocking issues, the release should be out by the first week of 
March at the latest.

The changes have been made to permit the limits to be changed and the default 
attached database limit has been increased to 30.

Sent from my iPhone

> On Feb 24, 2020, at 9:34 AM, Keith Bertram  wrote:
> 
> Hi Joe,
> 
> Do you have an ETA on the new build?
> 
> Also I wanted to confirm the change to the variable that would allow me to 
> set the number of attachments to a number higher than 10. I would like to set 
> it to 25.
> 
> Keith
> 
> -Original Message-
> From: sqlite-users  On Behalf 
> Of Joe Mistachkin
> Sent: Saturday, January 18, 2020 6:29 PM
> To: 'SQLite mailing list' 
> Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
> 
> 
> Keith Bertram wrote:
>> 
>> Do you have an estimated time for this release?
>> 
> 
> If everything goes as planned, right around the third week of February.
> 
> --
> Joe Mistachkin
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc=R-68Cs5Fb8R4NqTy8Z_KN392QyIGPkeK5wUAbjuM2nE=Xr8F673ynyjKjjWcb0AImOWopT_ajZZTSmFqcw6DAdg=
>  
> ___
> 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

Re: [sqlite] Performance Issue on Large Table

[Vikas Gupta]

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

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  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

Re: [sqlite] Performance Issue on Large Table

[Olaf Schmidt]

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

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

[Keith Bertram]

Hi Joe,

Do you have an ETA on the new build?

Also I wanted to confirm the change to the variable that would allow me to set 
the number of attachments to a number higher than 10. I would like to set it to 
25.

Keith

-Original Message-
From: sqlite-users  On Behalf Of 
Joe Mistachkin
Sent: Saturday, January 18, 2020 6:29 PM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll


Keith Bertram wrote:
>
> Do you have an estimated time for this release?
>

If everything goes as planned, right around the third week of February.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc=R-68Cs5Fb8R4NqTy8Z_KN392QyIGPkeK5wUAbjuM2nE=Xr8F673ynyjKjjWcb0AImOWopT_ajZZTSmFqcw6DAdg=
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users