Re: [sqlite] Trigger Performance
I'll try to do whatever I can. On Tue, Jun 12, 2018, 8:10 PM Simon Slavin wrote: > On 12 Jun 2018, at 10:49am, Space Pixel wrote: > > > the console > > shows a warning, about an inappropriate loader to some C# file. > > There is no C# code in SQLite. It's all plain C. Can you show us the > error message and tell us how it relates to you using SQLite commands ? > > Simon. > ___ > 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
Re: [sqlite] Trigger Performance
On 12 Jun 2018, at 10:49am, Space Pixel wrote: > the console > shows a warning, about an inappropriate loader to some C# file. There is no C# code in SQLite. It's all plain C. Can you show us the error message and tell us how it relates to you using SQLite commands ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger Performance
Hello SQLite, I am having serious truble with SQLite3 in Node.js (the NPM package). The code uses deprecated packages (such as crypto, http, https, aws-sdk) and when I try to fix some of the (mostly) fixable problems, the console shows a warning, about an inappropriate loader to some C# file. If possible, please help! I am stuck with this problem for a whole month and still no solution. On Tue, Jun 12, 2018, 5:37 AM Keith Medcalf wrote: > > 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 > >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 > > 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 > > > >>>>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=100; > >>>>> Run Time: real 1.625 user 1.625000 sys 0.00 > >>>>> sqlite> create trigger crap_trigger before insert on crap when 0 > >== > >>>>1 > >>>>>...> begin > >>>>>...> select raise(ABORT, 'abort'); > >>>>>...> end; > >>>>>
Re: [sqlite] Trigger Performance
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 >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 > 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 > >>>>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=100; >>>>> Run Time: real 1.625 user 1.625000 sys 0.00 >>>>> 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.00 sys 0.00 >>>>> 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.00 >>>>> sqlite> insert into crap select uuidStringCreateV4() from >>>>generate_series where start=1 and stop=100; >>>>> 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 ... >>>>> &
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 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 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 >>>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=100; >>>> Run Time: real 1.625 user 1.625000 sys 0.00 >>>> 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.00 sys 0.00 >>>> 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.00 >>>> sqlite> insert into crap select uuidStringCreateV4() from >>>generate_series where start=1 and stop=100; >>>> 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=100; >>>> 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
Re: [sqlite] Trigger Performance
Specifically, preparation of the "trigger part" of the statement is the overhead? Correct? On Mon, Jun 11, 2018 at 5:16 PM, Keith Medcalf 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 >>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=100; >>> Run Time: real 1.625 user 1.625000 sys 0.00 >>> 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.00 sys 0.00 >>> 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.00 >>> sqlite> insert into crap select uuidStringCreateV4() from >>generate_series where start=1 and stop=100; >>> 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=100; >>> 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.00 sys 0.00 >>> sqlite> insert into crap select uuidStringCreateV4() from >>generate_series where start=1 and stop=100; >>> 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.00 sys 0.00 >
[sqlite] Trigger performance
I have a table where I 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
Re: [sqlite] Trigger Performance
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 >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=100; >> Run Time: real 1.625 user 1.625000 sys 0.00 >> 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.00 sys 0.00 >> 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.00 >> sqlite> insert into crap select uuidStringCreateV4() from >generate_series where start=1 and stop=100; >> 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=100; >> 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.00 sys 0.00 >> sqlite> insert into crap select uuidStringCreateV4() from >generate_series where start=1 and stop=100; >> 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.00 sys 0.00 >> 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 >>&g
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 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=100; > Run Time: real 1.625 user 1.625000 sys 0.00 > 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.00 sys 0.00 > 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.00 > sqlite> insert into crap select uuidStringCreateV4() from generate_series > where start=1 and stop=100; > 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=100; > 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.00 sys 0.00 > sqlite> insert into crap select uuidStringCreateV4() from generate_series > where start=1 and stop=100; > 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.00 sys 0.00 > 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
Re: [sqlite] Trigger Performance
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=100; Run Time: real 1.625 user 1.625000 sys 0.00 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.00 sys 0.00 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.00 sqlite> insert into crap select uuidStringCreateV4() from generate_series where start=1 and stop=100; 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=100; 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.00 sys 0.00 sqlite> insert into crap select uuidStringCreateV4() from generate_series where start=1 and stop=100; 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.00 sys 0.00 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] 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
