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

Reply via email to