Re: [sqlite] Trigger Performance

[Keith Medcalf]

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 ...
>
> 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
>
>
> 

Re: [sqlite] Trigger Performance

[David Burgess]

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 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 

Re: [sqlite] Trigger Performance

[David Burgess]

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> .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 

Re: [sqlite] 3.24.0 is 20% larger than 3.23.1

[Richard Rousselot]

If FTS5 is enabled by default in the amalgamation, the documentation needs
to be updated.

https://sqlite.org/fts5.html Section 2.1


On Mon, Jun 11, 2018 at 9:57 AM Richard Hipp  wrote:

> On 6/11/18, Lonnie Abelbeck  wrote:
> >
> >> On Jun 11, 2018, at 7:23 AM, Richard Hipp  wrote:
> >>
> >> On 6/11/18, Lonnie Abelbeck  wrote:
> >>>
> >>> What changed for 3.23.1 -> 3.24.0 to cause such a large increase in
> >>> library
> >>> file size?
> >>>
> >>
> >> See Dan's follow-up.  Beginning with 3.24.0, the FTS5 and JSON1
> >> extensions are enabled by default.
> >>
> >> Manually edit the Makefile generated by ./configure to turn off those
> >> two features.  You ought to be able to do "./configure --disable-fts5
> >> --disable-json1" but those two --disable options appear to be broken
> >> in the ./configure script.
> >
> > Quick note, I added --disable-fts5 --disable-json1 to configure ...
> >
> > Then 3.24.0 when from 814112 bytes to 683648 bytes, also removed -lm
> > dependancy.
> >
> > Ref: For the amalgamation-tarball, enable FTS5 and JSON1 by default
> > https://www.sqlite.org/src/vinfo/03edecaf9dcfc927?diff=1
> >
> > Is there a reason we should not disable FTS5 and JSON1 going forward for
> > embedded use ?
>
> There are two configure scripts
>
> (1) The main configure script at the root of the source tree, used to
> build from canonical sources.
>
> (2) The configure script at autoconf/configure.ac that generates the
> configure script that is part of the prepackaged amalgamation
> deliverables.
>
> Dan was talking about (2) and he is correct that FTS5 and JSON1 went
> from default-off to default-on in version 3.24.0.  I was talking about
> (1) which did not change its default settings, but which does have the
> bug in which the --disable-fts5 and --disable-json1 flags seem to be
> ignored.
>
> There is no reason to include FTS5 and JSON1 in embedded builds if you
> do not need them.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Performance of writing blobs

[Dominique Devienne]

On Mon, Jun 11, 2018 at 4:27 PM Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > My assumption
> > was that after the zeroblob(N), there was enough room in the main DBs
> > pages, such that the subsequent blob open+write+close did not need to
> > generate any "page churn" (i.e. journal activity) and could write
> directly
> > to the pages created on initial insert.
>
> It does write to the same pages, but those pages must be copied to the
> rollback journal so that they can be restored if the transaction is
> rolled back.  (Or are the two passes inside the same transaction?)
>

They are part of the same transactions. So the no-rewrite pages should
still apply, no?


> Don't insert the zero blobs in the first pass; this still rewrites all
> rows, but there is less data that might need to be rolled back.
>
> Consider moving the blobs to a separate table.
>

 Is that really/still relevant, considering the above? Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.24.0 is 20% larger than 3.23.1

[Richard Hipp]

On 6/11/18, Lonnie Abelbeck  wrote:
>
>> On Jun 11, 2018, at 7:23 AM, Richard Hipp  wrote:
>>
>> On 6/11/18, Lonnie Abelbeck  wrote:
>>>
>>> What changed for 3.23.1 -> 3.24.0 to cause such a large increase in
>>> library
>>> file size?
>>>
>>
>> See Dan's follow-up.  Beginning with 3.24.0, the FTS5 and JSON1
>> extensions are enabled by default.
>>
>> Manually edit the Makefile generated by ./configure to turn off those
>> two features.  You ought to be able to do "./configure --disable-fts5
>> --disable-json1" but those two --disable options appear to be broken
>> in the ./configure script.
>
> Quick note, I added --disable-fts5 --disable-json1 to configure ...
>
> Then 3.24.0 when from 814112 bytes to 683648 bytes, also removed -lm
> dependancy.
>
> Ref: For the amalgamation-tarball, enable FTS5 and JSON1 by default
> https://www.sqlite.org/src/vinfo/03edecaf9dcfc927?diff=1
>
> Is there a reason we should not disable FTS5 and JSON1 going forward for
> embedded use ?

There are two configure scripts

(1) The main configure script at the root of the source tree, used to
build from canonical sources.

(2) The configure script at autoconf/configure.ac that generates the
configure script that is part of the prepackaged amalgamation
deliverables.

Dan was talking about (2) and he is correct that FTS5 and JSON1 went
from default-off to default-on in version 3.24.0.  I was talking about
(1) which did not change its default settings, but which does have the
bug in which the --disable-fts5 and --disable-json1 flags seem to be
ignored.

There is no reason to include FTS5 and JSON1 in embedded builds if you
do not need them.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance of writing blobs

[Clemens Ladisch]

Dominique Devienne wrote:
> My assumption
> was that after the zeroblob(N), there was enough room in the main DBs
> pages, such that the subsequent blob open+write+close did not need to
> generate any "page churn" (i.e. journal activity) and could write directly
> to the pages created on initial insert.

It does write to the same pages, but those pages must be copied to the
rollback journal so that they can be restored if the transaction is
rolled back.  (Or are the two passes inside the same transaction?)

Don't insert the zero blobs in the first pass; this still rewrites all
rows, but there is less data that might need to be rolled back.

Consider moving the blobs to a separate table.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance of writing blobs

[Dominique Devienne]

I'm surprised about the commit time of SQLite, when writing blobs is
involved.
Can anybody shed light on this subject? Below's a description of what I do,
with the results.

I've exporting data into SQLite, spread in several tables.
I process only about 240,000 rows, and write around 1GB in 20,000 blobs.

I process the data in two passes:
1) write the rows, record rowid when a row has a blob,
zeroblob(N) during insert, to leave room for Incremental Blob I/O later
in 2nd pass.
2) process blobs only

Then I compare this with writing the blobs in HDF5 instead.
Instead of writing the blob itself in-row, I just insert its size in SQLite.

(HDF5 is a "filesystem in a file", non-transactional, with "files" being
N-dimensional typed arrays, thus a 1D array of bytes approximates a "file")

Results writing both rows and blobs in SQLite:
Write ROWs   =   6.968s (18.7%) (includes the zeroblob(N))
Write BLOBs  =   2.764s ( 7.4%)
Commit TX=  10.566s (28.4%)

Results is a single 1,110,253,568 bytes SQLite DB file.

Now writing only rows to SQLite and blobs to HDF5:
Write ROWs   =   4.265s (16.6%)
Write BLOBs  =   4.010s (15.6%)
Commit TX=   0.195s ( 0.8%) (includes flushing the HDF5 file too)

Results in two files, 64,475,136 bytes SQLite DB, and 1,039,652,416 bytes
HDF5 file.

SQLite (version 3.19.3) is used in journal mode, and DB written "from
scratch", so there's not much to put in the journal, AFAIK. My assumption
was that after the zeroblob(N), there was enough room in the main DBs
pages, such that the subsequent blob open+write+close did not need to
generate any "page churn" (i.e. journal activity) and could write directly
to the pages created on initial insert. But what surprised me was the
commit time jumping to 10s, i.e. 50x more that when not writing the blobs.

After the initial zeroblob(N) + sqlite3_last_insert_rowid(), part of "Write
ROWs",
the code writing the blobs is below (simplified, error handling removed).

sqlite3_blob* p_blob = nullptr;
sqlite3_blob_open(db, "main", tab, col, rowid, /*read-write*/1, _blob);
assert(sqlite3_blob_bytes(p_blob) == length, ;);
sqlite3_blob_write(p_blob, buffer, static_cast(length), 0);
sqlite3_blob_close(p_blob);

So if the code above "the write way" to write blobs?

I don't yet have the buffers to write the blobs directly at-row-insert-time,
for reasons beyond this particular problem. Please assume this is by-design.

I can understand that 64MB I/O vs 1GB I/O takes more time, and that's
reflected
in the 4.2s writing just the rows, versus the 7s + 2.7s = 9.7s when writing
both rows and blobs,
but why the commit time jumps from 0.2s to 10.5s ??? Thanks for any
insights in this.

Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.24.0 is 20% larger than 3.23.1

[Richard Hipp]

On 6/11/18, Richard Hipp  wrote:
> On 6/11/18, Lonnie Abelbeck  wrote:
>>
>> What changed for 3.23.1 -> 3.24.0 to cause such a large increase in
>> library
>> file size?
>>
>
> See Dan's follow-up.  Beginning with 3.24.0, the FTS5 and JSON1
> extensions are enabled by default.

Checking further, I don't think this is correct.  I can't find any
changes in the autoconf for version 3.24.0.   So, I have no idea why
your binaries are such much larger.

Using "gcc -Os -c sqlite3.c" on both 3.23.1 and 3.24.0, I get 493632
and 499724 bytes respectively.   3.24.0 is a little larger due to the
addition of UPSERT.  But not 20% larger. It is 1.2% larger.

>
> Manually edit the Makefile generated by ./configure to turn off those
> two features.  You ought to be able to do "./configure --disable-fts5
> --disable-json1" but those two --disable options appear to be broken
> in the ./configure script.

On the other hand, the configure script really is broken.  But it
appears to have been broken for a long time and nobody has complained,
so I'll wait until the next maintenance release for a fix.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.24.0 is 20% larger than 3.23.1

[Richard Hipp]

On 6/11/18, Lonnie Abelbeck  wrote:
>
> What changed for 3.23.1 -> 3.24.0 to cause such a large increase in library
> file size?
>

See Dan's follow-up.  Beginning with 3.24.0, the FTS5 and JSON1
extensions are enabled by default.

Manually edit the Makefile generated by ./configure to turn off those
two features.  You ought to be able to do "./configure --disable-fts5
--disable-json1" but those two --disable options appear to be broken
in the ./configure script.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking for errors in sqlite3_column_*

[Simon Slavin]

On 11 Jun 2018, at 8:29am, Christopher Head  wrote:

> It seemed potentially odd that many other
> threads got replies but this didn’t.

Your question was so good nobody had an answer to it.  I hope you see that DRH 
has now posted a reply, even if that reply doesn't include a solution.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query on SQLite - VxWorks

[Simon Slavin]

On 9 Jun 2018, at 6:36pm, Guna Sekar  wrote:

> Is SQLite supports outfile query and dumps all data into specified file 
> format ? 

I don't understand your question, but you might want to use the command-line 
shell program to dump a database to a text file -- either as SQL commands or a 
CSV file.  Please see this page:



and download the command-line shell program from this page:



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[John McKown]

Very good point. I think that everyone should do it that way. It is a bit
more work, but is vastly superior.

On Mon, Jun 11, 2018, 03:23 Olivier Mascia  wrote:

> > Le 11 juin 2018 à 10:07, Peter Nacken  a écrit :
> >
> > I try to insert email addresses into a table and get an error with
> addresses they have a single quotation mark ( na'm...@domain.ltd ).
>
> > Sorry I forgot I‘m using C#
>
> (Assuming: "create table T(E text);" for the following.)
>
> If you're building the text of your insert statement before executing it,
> you will have either to build the statement string as:
>
> insert into T values('na''m...@domain.tld');
> or
> insert into T values("na'm...@domain.ltd");
>
> Both of which you can test with the command-line sqlite3.exe.
>
> But it would be far more appropriate to use prepare:
>
> insert into T values(?);
>
> and then bind the parameter before executing. You won't have to alter your
> data for inserting and it will be much better for SQL code injection
> protection, depending where the email address comes from.
>
> I'm sure there is plenty of documentation with your language binding for
> SQLite on how to prepare, bind, execute. Instead of building a final
> statement as a complete string and then execute it.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> 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] Insert with an '

[Richard Damon]

On 6/11/18 4:23 AM, Olivier Mascia wrote:
>> Le 11 juin 2018 à 10:07, Peter Nacken  a écrit :
>>
>> I try to insert email addresses into a table and get an error with addresses 
>> they have a single quotation mark ( na'm...@domain.ltd ).
>> Sorry I forgot I‘m using C#
> (Assuming: "create table T(E text);" for the following.)
>
> If you're building the text of your insert statement before executing it, you 
> will have either to build the statement string as:
>
> insert into T values('na''m...@domain.tld');
> or
> insert into T values("na'm...@domain.ltd");
>
> Both of which you can test with the command-line sqlite3.exe.
>
> But it would be far more appropriate to use prepare:
>
> insert into T values(?);
>
> and then bind the parameter before executing. You won't have to alter your 
> data for inserting and it will be much better for SQL code injection 
> protection, depending where the email address comes from.
>
> I'm sure there is plenty of documentation with your language binding for 
> SQLite on how to prepare, bind, execute. Instead of building a final 
> statement as a complete string and then execute it.
>
And for a simple example of why you want to prepare (or be very careful
how you sanitize your data) see https://xkcd.com/327/ (Bobby Tables).
You should NEVER just build an SQL statement (or any other sort of
executable statement) by just inserting data from some source into a SQL
string. Using prepare is the best, at a minimum you need to run it
through a quoting function appropriate for that language.

-- 
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[Peter Nacken]

Dear all,

Thanks for your help. It works !

Peter

> Am 11.06.2018 um 11:14 schrieb Tim Streater :
> 
>> On 11 Jun 2018, at 09:07, Peter Nacken  wrote:
>> 
>> I try to insert email addresses into a table and get an error with addresses
>> they have a single quotation mark ( na'm...@domain.ltd ). 
>> 
>> Sorry I'm facing this problem for weeks, I can't find a solution.
>> 
>> Is there a known workaround for it ?
> 
> See:
> 
> 
> 
> and look at question 14 and its answer.
> 
> 
> 
> -- 
> Cheers  --  Tim
> ___
> 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] 3.24.0 is 20% larger than 3.23.1

[Dan Kennedy]

On 06/11/2018 05:35 AM, Lonnie Abelbeck wrote:

Hi,

Our project did a SQLite version bump from 3.23.1 to 3.24.0 (identical build 
options), the /usr/lib/libsqlite3.so.0.8.6 lib increased by 20% !



For version 3.24.0, the fts5 and json1 extensions are enabled by default 
in the amalgamation/autoconf package:


  https://www.sqlite.org/src/info/03edecaf9dcfc927

It's fts5 that has the libm dependency. To build without these two 
extensions, use:


  configure --enable-json1=no --enable-fts5=no

Dan.





3.24.0
-rwxr-xr-x1 root root814112 Jun 10 15:31 
/usr/lib/libsqlite3.so.0.8.6  (uses libm)

3.23.1
-rwxr-xr-x1 root root674800 Jun  6 20:35 
/usr/lib/libsqlite3.so.0.8.6  (no libm reference)

Also note the math library -lm was dynamically linked in 3.24.0 but not in 
3.23.1 (via ldd).

The only compile time option is -DSQLITE_ENABLE_COLUMN_METADATA

Ref:
https://github.com/astlinux-project/astlinux/blob/master/package/sqlite/sqlite.mk

I have looked as Richard's thread "[sqlite] Size of the SQLite library Richard 
Hipp", but this seems much more than that.

Has the default set of compile time options changed in 3.24.0 ?

Any insight is appreciated.

Lonnie

___
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] Checking for errors in sqlite3_column_*

[Richard Hipp]

On 6/11/18, Christopher Head  wrote:
> Hello! I noticed that there was some discussion about mailing list
> messages getting sent to spam boxes. So, not that I expect an immediate
> answer, but just in case people didn’t see this message for that
> reason, here it is again. It seemed potentially odd that many other
> threads got replies but this didn’t.

I clearly need to update the documentation.  It is on my to-do list.
That will be a big project.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.24.0 is 20% larger than 3.23.1

[Richard Hipp]

On 6/10/18, Lonnie Abelbeck  wrote:
>
> Our project did a SQLite version bump from 3.23.1 to 3.24.0 (identical build
> options), the /usr/lib/libsqlite3.so.0.8.6 lib increased by 20% !
>
> 3.24.0
> -rwxr-xr-x1 root root814112 Jun 10 15:31
> /usr/lib/libsqlite3.so.0.8.6  (uses libm)
>

The "size" command shows the size of the code within a shared library
- which is close to the size of the shared library after it has been
stripped of symbolic debugging information.  This is a more accurate
measurement that "ls".

What do you get when you use the "size" command?

> 3.23.1
> -rwxr-xr-x1 root root674800 Jun  6 20:35
> /usr/lib/libsqlite3.so.0.8.6  (no libm reference)
>
> Also note the math library -lm was dynamically linked in 3.24.0 but not in
> 3.23.1 (via ldd).
>
> The only compile time option is -DSQLITE_ENABLE_COLUMN_METADATA
>
> Ref:
> https://github.com/astlinux-project/astlinux/blob/master/package/sqlite/sqlite.mk
>
> I have looked as Richard's thread "[sqlite] Size of the SQLite library
> Richard Hipp", but this seems much more than that.
>
> Has the default set of compile time options changed in 3.24.0 ?
>
> Any insight is appreciated.
>
> Lonnie
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trigger performance

[David Burgess]

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] Checking for errors in sqlite3_column_*

[Christopher Head]

Hello! I noticed that there was some discussion about mailing list
messages getting sent to spam boxes. So, not that I expect an immediate
answer, but just in case people didn’t see this message for that
reason, here it is again. It seemed potentially odd that many other
threads got replies but this didn’t.

On Thu, 7 Jun 2018 20:14:35 -0700
Christopher Head  wrote:

> Hello,
> I have a question regarding checking for errors that occur when
> calling sqlite3_column_*.
> 
> From this manual page:
> 
> https://sqlite.org/c3ref/column_blob.html
> 
> it seems that the only error that could occur (other than programmer
> error, like out-of-bounds column index or calling it after something
> other than SQLITE_ROW) is memory allocation error. That page says
> that, in the event of memory allocation error, an appropriate default
> value is returned and then sqlite3_errcode will return SQLITE_NOMEM.
> 
> From this manual page:
> 
> https://sqlite.org/c3ref/errcode.html
> 
> it states explicitly that, if the most recent call failed, then
> sqlite3_errcode returns the error code, but if the most recent call
> succeeded, then the return value is undefined.
> 
> So how do I check if sqlite3_column_int failed due to memory
> allocation error? If sqlite3_column_int returns zero, I don’t know
> whether it succeeded or failed. In the event that it succeeded, then
> the return value from sqlite3_errcode is undefined. So even if
> sqlite3_column_int returns zero and sqlite3_errcode returns
> SQLITE_NOMEM, it could be that an allocation failed, but it could
> just as well be that the actual column value was zero and
> sqlite3_errcode randomly decided to return SQLITE_NOMEM because the
> return value is undefined because sqlite3_column_int succeeded.
> 
> I discovered from inspecting the source code (and then confirmed by
> testing) that if an sqlite3_column_* function fails, then the next
> sqlite3_step call will *also* fail with SQLITE_NOMEM (and that’s
> detectable because sqlite3_step returns a result code as its return
> value), but that doesn’t seem to be documented anywhere in the manual
> at all.
> 
> So what’s the proper way to check for errors in these functions?
> 
> Thanks!
-- 
Christopher Head


pgpC81VZwXE36.pgp
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 3.24.0 is 20% larger than 3.23.1

[Lonnie Abelbeck]

Hi,

Our project did a SQLite version bump from 3.23.1 to 3.24.0 (identical build 
options), the /usr/lib/libsqlite3.so.0.8.6 lib increased by 20% !

3.24.0
-rwxr-xr-x1 root root814112 Jun 10 15:31 
/usr/lib/libsqlite3.so.0.8.6  (uses libm)

3.23.1
-rwxr-xr-x1 root root674800 Jun  6 20:35 
/usr/lib/libsqlite3.so.0.8.6  (no libm reference)

Also note the math library -lm was dynamically linked in 3.24.0 but not in 
3.23.1 (via ldd).

The only compile time option is -DSQLITE_ENABLE_COLUMN_METADATA

Ref:
https://github.com/astlinux-project/astlinux/blob/master/package/sqlite/sqlite.mk

I have looked as Richard's thread "[sqlite] Size of the SQLite library Richard 
Hipp", but this seems much more than that.

Has the default set of compile time options changed in 3.24.0 ?

Any insight is appreciated.

Lonnie

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea: defining table-valued functions directly in SQL

[sqlite]

I have some of my own ideas about this.

* Perhaps move PARAMETERS before AS, which may make the syntax easier.

* You don't need computed columns in tables; use views instead. You can index 
computed values though.

* I do agree that defining table-valued functions in these way can be useful 
though; I have wanted to define views that take parameters before, and was 
unable to.

* Another (separate) idea can be "CREATE FUNCTION name(args) AS select_stmt;" 
to define your own function. If you write "CREATE AGGREGATE FUNCTION" then the 
function name can be used as a table name within the select_stmt. Both of these 
are separate from table-valued functions (parameterized views) though.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query on SQLite - VxWorks

[Guna Sekar]

Hi Team,

 Is SQLite supports outfile query and dumps all data into specified file format 
? 

I have tried to extract data from table using outfile query but query failed 
status returned. If supports , Can you please share the syntax or example that 
will really help lot to me.

Awaiting for your valuable response.


Regards,
Gunasekar K

Sent from my iPhone
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[Tim Streater]

On 11 Jun 2018, at 09:07, Peter Nacken  wrote:

> I try to insert email addresses into a table and get an error with addresses
> they have a single quotation mark ( na'm...@domain.ltd ). 
>
> Sorry I'm facing this problem for weeks, I can't find a solution.
>
> Is there a known workaround for it ?

See:



and look at question 14 and its answer.



-- 
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[Olivier Mascia]

> Le 11 juin 2018 à 10:07, Peter Nacken  a écrit :
> 
> I try to insert email addresses into a table and get an error with addresses 
> they have a single quotation mark ( na'm...@domain.ltd ).

> Sorry I forgot I‘m using C#

(Assuming: "create table T(E text);" for the following.)

If you're building the text of your insert statement before executing it, you 
will have either to build the statement string as:

insert into T values('na''m...@domain.tld');
or
insert into T values("na'm...@domain.ltd");

Both of which you can test with the command-line sqlite3.exe.

But it would be far more appropriate to use prepare:

insert into T values(?);

and then bind the parameter before executing. You won't have to alter your data 
for inserting and it will be much better for SQL code injection protection, 
depending where the email address comes from.

I'm sure there is plenty of documentation with your language binding for SQLite 
on how to prepare, bind, execute. Instead of building a final statement as a 
complete string and then execute it.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[Simon Slavin]

On 11 Jun 2018, at 9:07am, Peter Nacken  wrote:

> I try to insert email addresses into a table and get an error with addresses 
> they have a single quotation mark ( na'm...@domain.ltd ). 
> 
> Sorry I'm facing this problem for weeks, I can't find a solution.

Every single ' character in a string needs to become two ' characters.  You can 
write a function which accepts one string and produces another with this change 
made.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert with an '

[Peter Nacken]

Sorry I forgot I‘m using C#

> Am 11.06.2018 um 10:07 schrieb Peter Nacken :
> 
> Hi,
> 
> I'm new in SQLite. 
> 
> I try to insert email addresses into a table and get an error with addresses 
> they have a single quotation mark ( na'm...@domain.ltd ). 
> 
> Sorry I'm facing this problem for weeks, I can't find a solution.
> 
> Is there a known workaround for it ?
> 
> Thanks for help
> 
> Peter
> ___
> 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] Insert with an '

[Peter Nacken]

Hi,

I'm new in SQLite. 

I try to insert email addresses into a table and get an error with addresses 
they have a single quotation mark ( na'm...@domain.ltd ). 

Sorry I'm facing this problem for weeks, I can't find a solution.

Is there a known workaround for it ?

Thanks for help

Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger Performance

[Keith Medcalf]

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
>>>
>>>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
>> 

Re: [sqlite] Trigger Performance

[David Burgess]

> 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