Interesting. That is adding 30% or so to process the trigger. When I do (this is to a "memory" database):
Advertising
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
