On Wednesday, 27 March, 2019 12:04, Thomas Kurz <sqlite.2...@t-net.ruhr> wrote:
>> I wonder whether SQLite is treating each DELETE as a single >transaction. Could you try wrapping the main delete in BEGIN ... END >and see whether that speeds up the cascaded DELETE ? Would you be >able to find timings (either in your code or in the command-line >tool) and tell us whether it's the DELETE or the END which takes the >time ? >Ok, well.... very interesting and I'd never have had this idea, but >indeed it works: within a transaction, it takes only a few seconds. >This is very surprising as to me, a single DELETE statement is >nothing more than that: a single atomic operation which should >automatically be treated as a transaction (auto-commit-mode). It is. In auto-commit mode the transaction is started automatically when the VDBE program commences execution, and terminated when the VDBE program completes. There is therefore NO difference between BEGIN; <statement>; COMMIT; and just running the bare <statement> ... You do have the necessary indexes do you not? >*confused* sqlite> create table p(id integer primary key); sqlite> create table c(p integer references p on delete cascade, q); sqlite> create index c_p on c(p); sqlite> insert into p values (1), (2), (3); sqlite> insert into c select 1, value from generate_series where start=1 and stop=10000000; sqlite> insert into c select 2, value from generate_series where start=1 and stop=10000000; sqlite> insert into c select 3, value from generate_series where start=1 and stop=10000000; sqlite> .timer on sqlite> delete from p where id == 2; Run Time: real 12.022 user 11.140625 sys 0.734375 sqlite> begin; Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> delete from p where id == 3; Run Time: real 12.073 user 10.218750 sys 0.843750 sqlite> commit; Run Time: real 0.114 user 0.031250 sys 0.031250 sqlite> select distinct p from c; 1 Run Time: real 0.545 user 0.546875 sys 0.000000 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
