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

Reply via email to