> The trigger is adding a mere 10% overhead on a million rows ... 3.24 on a real data DB (16 columns), the inserts were generated from .dump in the shell, about 45MB of input data so
BEGIN; lots of inserts COMMIT; Run on a laptop i7 with SSD (not that should make any difference to the relative performance) If I could get your relative performance, I would be happy. On Mon, Jun 11, 2018 at 3:30 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > Interesting. That is adding 30% or so to process the trigger. When I do > (this is to a "memory" database): > > SQLite version 3.25.0 2018-06-11 01:30:03 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> create table crap(uuid text not null collate nocase unique); > sqlite> .timer on > sqlite> insert into crap select uuidStringCreateV4() from generate_series > where start=1 and stop=1000000; > Run Time: real 1.625 user 1.625000 sys 0.000000 > sqlite> create trigger crap_trigger before insert on crap when 0 == 1 > ...> begin > ...> select raise(ABORT, 'abort'); > ...> end; > Run Time: real 0.000 user 0.000000 sys 0.000000 > sqlite> .schema > CREATE TABLE crap(uuid text not null collate nocase unique); > CREATE TRIGGER crap_trigger before insert on crap when 0 == 1 > begin > select raise(ABORT, 'abort'); > end; > sqlite> delete from crap; > Run Time: real 0.031 user 0.031250 sys 0.000000 > sqlite> insert into crap select uuidStringCreateV4() from generate_series > where start=1 and stop=1000000; > Run Time: real 1.796 user 1.781250 sys 0.015625 > sqlite> select (1.796-1.625)/1.625; > 0.105230769230769 > > The trigger is adding a mere 10% overhead on a million rows ... > > I also got a result where the overhead added by the trigger was half that > (when using an actual disk db rather than a memory db). > >>sqlite test.db > SQLite version 3.25.0 2018-06-11 01:30:03 > Enter ".help" for usage hints. > sqlite> create table crap(uuid text not null collate nocase unique); > sqlite> .timer on > sqlite> insert into crap select uuidStringCreateV4() from generate_series > where start=1 and stop=1000000; > Run Time: real 1.891 user 1.609375 sys 0.171875 > sqlite> delete from crap; > Run Time: real 0.062 user 0.015625 sys 0.015625 > sqlite> create trigger crap_trigger before insert on crap when 0 == 1 > ...> begin > ...> select raise(ABORT, 'abort'); > ...> end; > Run Time: real 0.031 user 0.000000 sys 0.000000 > sqlite> insert into crap select uuidStringCreateV4() from generate_series > where start=1 and stop=1000000; > Run Time: real 1.984 user 1.812500 sys 0.109375 > sqlite> select (1.984-1.891)/1.891; > 0.0491803278688524 > Run Time: real 0.000 user 0.000000 sys 0.000000 > sqlite> .exit > > > What version of SQLite are you using? > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-----Original Message----- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of David Burgess >>Sent: Sunday, 10 June, 2018 22:25 >>To: sqlite-users@mailinglists.sqlite.org >>Subject: [sqlite] Trigger Performance >> >>I have a table where I drop, create and insert 284,000 rows >> >>time taken 3.39 seconds. >> >>I add the following trigger >> >> >>CREATE TRIGGER x_trigger BEFORE INSERT ON x >>WHEN >> 0 = 1 >> BEGIN SELECT RAISE ( ABORT, 'raise' ); >>END; >> >>time taken 4.49 seconds. >>_______________________________________________ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
