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 <kmedc...@dessus.com> 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 <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 > On Jun 12, 2018 5:37 AM, "Keith Medcalf" <kmedc...@dessus.com> 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 <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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
