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

Reply via email to