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
