Re: Performance Issue on Large Table

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

My thought is that a modification (making multiple tables, one per 
version) that drops significant functionality (now version is hard coded 
as part of the query, and can't be determined as part of a query) is 
something not to lightly do.  It also isn't that likely to improve 
things a lot unless it allows the massive delete cycle to be replaced 
with a drop table.

One big question is why the apparent wholesale deletion of a version, 
and if really needed perhaps a better schema that supports the 
operations better.

-- 
Richard Damon

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