Re: Performance Issue on Large Table

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

It is far less of a worse design than having an AFTER DELETE trigger 
doing trivial processing when you expect to delete 30K records at a time 
due to a "well-designed" 1NF schema.

Firstly, good design guides are only guides.
Secondly, having said that, either embrace the full idealism or none of 
it, but doing some things the good way and others not, hampers the 
"goodness" of the overall system.
Lastly, we usually use ON DELETE CASCADE Foreign Key relations to do 
what you are doing with the trigger, but of course you are using FTS5 
and I do not know if it can be done the FK way, or indeed how much the 
FK way will be better considering the same Indexes remain.


Note - I don't have a good solution here, if it was me I would 
immediately kill those triggers, see if it can be done smarter (or is 
needed at all), or even if it can be done in code - OR - get rid of the 
1NF schema and go with the suggested Bible-per-Table method and keep the 
triggers (This will help nothing if the deletes are going to require 
deleting 1 record (and its trigger) each through 30K tables. It's only 
useful if it brings down the delete count).

If you cannot live without the triggers, nor can bring down the 
delete-count, maybe do it in code, or one quick way I can think of is 
using the ON DELETE to add those IDs to a "to_be_deleted" table and 
simply delete everything fropm the second table with "WHERE ID IN 
(SELECT ID FROM to_be_deleted)" before comitting the transaction, 
perhaps combined with dropping and re-making the Indexes - but of course 
this needs testing and might not be suitable for reasons I am unaware of 
in your use case.


Either way, you are probably in for some lengthy processing.

Best of luck!
Ryan

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