Yes. Looking up the trigger and preparing the VDBE code appears to the additional time spent.
--- 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 17:50 >To: SQLite mailing list >Subject: Re: [sqlite] Trigger Performance > >Trying again/ >Specifically, preparation of the constant "trigger part" of the >statement is >the overhead? Correct? > >On Tue, Jun 12, 2018 at 9:47 AM, David Burgess <dburges...@gmail.com> >wrote: >> Specifically, preparation of the "trigger part" of the statement is >> the overhead? Correct? >> >> >> On Mon, Jun 11, 2018 at 5:16 PM, Keith Medcalf ><kmedc...@dessus.com> wrote: >>> >>> 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 >_______________________________________________ >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
