Okay, the difference is the "lots of inserts" -vs- the "one insert".
When I do the same thing (dump the contents of the table to a file and then reload the dump), the overhead of the trigger is about 33% (same as you). There is not only the time to "insert the data" but also the overhead of preparing the statements. In the case where the statement is not prepared each time but only the single prepare with multiple insertions, the time to run the VDBE code which includes the trigger is only 5-10% more than to run the insertions without the trigger. However, the overhead of preparing the statement for execution is what is taking up the rest of the observed difference. --- 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: Monday, 11 June, 2018 00:40 >To: SQLite mailing list >Subject: Re: [sqlite] Trigger Performance > >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
