> 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

Reply via email to