On Sun, 3 Jun 2018 22:59:05 -0700 (MST) ayagmur <ayagmu...@gmail.com> escribió:
> I have been using ext2 file system. I have a database which has 4 GB size. > Database consist of 1 parent table and 1 child table. Parent table has 10 > rows and child table has 4000 rows. 1 row of child table has 1 MB size. when > I delete a row in parent table, deletion cascades 1MB-sized child records. > (pragma foreign_keys is on) When I try to delete 100 MB data by cascade (1 > parent record - 100 child records) it takes too long time (almost 10 minute) > to complete, and the duration increase/decrease by size of data (100 Mb: 10 > minute, 300 MB: 30 minute,etc).I tried some pragma commands (synchronous, > temp_store, journal_mode) suggested by others posts and i also tried to add > index on foreign key, but those does not help solve my problem.(Actually, > after adding index on foreign key, 1 MB data deletion became faster/st, but > 100 MB data deletion duration did not change) Can you give me please any > suggestion to increase deletion performance? Don't hard-delete. You have 2 options to soft-delete (I use both on my big fs over sqlite): a) Add a new column to child, put it before the data blob column and set it to 0 if it is active or 1 when delete. From time to time select those actives and move them to a new fresh table. b) Parent has the foreign key to the childs, just set those foreign keys to 0 or to the new content. From time to time select all foreign keys to child on parent table and move them to a new fresh table. Disable foreign_key pragma. Now I use b) with parent (fs metadata) on one db file and child (file content) on another, it's faster and don't require an additional column on child. Clean child table/db when you want, I never did it on mines for now. -- Eduardo <eduardo.mor...@mobelservices.com> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
