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
