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
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
On 2/23/20 3:06 PM, R.Smith wrote:
On 2020/02/23 21:23, Richard Damon wrote:
On 2/23/20 8:31 AM, Olaf Schmidt wrote:
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.
On 23 Feb 2020, at 7:23pm, Richard Damon wrote:
> 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 because you can't make the table to lookup data from be taken from
> a field
On 2020/02/23 21:23, Richard Damon wrote:
On 2/23/20 8:31 AM, Olaf Schmidt wrote:
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
Chip Beaulieu wrote:
> 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
On 2/23/20 8:31 AM, Olaf Schmidt wrote:
Am 21.02.2020 um 02:24 schrieb Chip Beaulieu:
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
Am 21.02.2020 um 02:24 schrieb Chip Beaulieu:
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
> On Feb 20, 2020, at 5:24 PM, Chip Beaulieu wrote:
>
> 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`)
I believe
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
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:
11 matches
Mail list logo
