Re: Performance Issue on Large Table

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 / inserts at a time. The last batch took 10 hours to 
>> complete.
>
> These 30,000 deletes+inserts indicates that these were the
> verse-records of a complete Bible-Content, right?
>
> And your 4.5Mio records total indicate, that you have about
> 140 different Bible-versions in your DB?
>
>> I suspect it’s got something to do with the triggers more than the 
>> indexes.
> Could be - but my guess is, that your primary DB-changes are caused
> by "deleting or inserting verses of whole bible-contents".
>
> So why not handle (hold) each bible in a separate table?
>
> 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 because you can't make the table to lookup 
data from be taken from a field you get in a query.

-- 
Richard Damon

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