Re: [sqlite] Trigger for incrementing a column is slow

[Richard Hipp]

On Fri, Feb 4, 2011 at 7:26 AM, Simon Slavin  wrote:

>
> On 4 Feb 2011, at 9:42am, Dan Kennedy wrote:
>
> > The problem is that the trigger version is creating a statement journal
> > for each INSERT statement. It doesn't *really* need to, as there is no
> > way that this statement/trigger can hit a constraint after modifying
> > any rows. However at the moment I think SQLite opens a statement
> > transaction for any statement that will fire one or more triggers.
>
> I'm not quite up with the terminology but now I'm interested.
>
> First, do you feel that this behaviour is a bug and can be addressed in
> future versions ?
>


I strive to be more precise and reserve the word "bug" for cases where the
software gets the wrong answer.  When the correct answer is obtained, just
more slowly than one hopes, that is not a "bug" but a "performance
enhancement opportunity."

We will look into taking advantage of this performance enhancement
opportunity to make SQLite run faster in a future release.  But we have to
proceed carefully here.  Performance enhancements are our #1 source of
bugs, since performance enhancements typically result in more complex code,
and complication tends to lead to bugs.  Our first priority is to avoid
bugs.  It is seldom helpful to get the wrong answer quickly.


> Second, is this the same as the automatic creation of transactions if the
> programmer doesn't create a transaction ?  In other words, if the programmer
> doesn't do a BEGIN, does SQLite make one transaction for the INSERT, and
> another for each action inside anything that triggers ?  Or do all the
> actions triggered by the INSERT get included within the same automatic
> transaction ?
>

When a statement is one of several statements that occur in the middle of a
larger BEGIN...END and that statement might fail, we have to open a separate
journal that can be used to rollback partial results of that one statement
without having to rollback the entire transaction.  The statement journal is
used for that purpose.


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



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

Re: [sqlite] Trigger for incrementing a column is slow

[Simon Slavin]

On 4 Feb 2011, at 9:42am, Dan Kennedy wrote:

> The problem is that the trigger version is creating a statement journal
> for each INSERT statement. It doesn't *really* need to, as there is no
> way that this statement/trigger can hit a constraint after modifying
> any rows. However at the moment I think SQLite opens a statement
> transaction for any statement that will fire one or more triggers.

I'm not quite up with the terminology but now I'm interested.

First, do you feel that this behaviour is a bug and can be addressed in future 
versions ?

Second, is this the same as the automatic creation of transactions if the 
programmer doesn't create a transaction ?  In other words, if the programmer 
doesn't do a BEGIN, does SQLite make one transaction for the INSERT, and 
another for each action inside anything that triggers ?  Or do all the actions 
triggered by the INSERT get included within the same automatic transaction ?

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

Re: [sqlite] Trigger for incrementing a column is slow

[Dan Kennedy]

On 02/04/2011 03:24 PM, Kevin Wojniak wrote:
>
> On Feb 3, 2011, at 10:35 PM, Dan Kennedy wrote:
>
>> Do you have a test program that we can use to reproduce this phenomenon?
>>
>> Dan.
>
> Here is a complete program:
> http://pastie.org/pastes/1527560
>
> Set USE_TRIGGER to see the trigger version.

Thanks for this.

The problem is that the trigger version is creating a statement journal
for each INSERT statement. It doesn't *really* need to, as there is no
way that this statement/trigger can hit a constraint after modifying
any rows. However at the moment I think SQLite opens a statement
transaction for any statement that will fire one or more triggers.

Statement journals:

   http://www.sqlite.org/tempfiles.html#stmtjrnl

The effect is that when using the no-trigger version, all that most
of your INSERT and UPDATE statements have to do is modify the database
within the cache. However the trigger version has to copy the original
page data into the statement journal before it can modify them. If the
statement journal is stored in a temporary file, this means many calls
to write().

You can improve the situation some by using an in-memory statement
journal:

   PRAGMA temp_store = memory;

But the trigger version is still slower. Because of the statement
transaction SQLite is opening. Sub-optimal, that.

Dan.

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

Re: [sqlite] Trigger for incrementing a column is slow

[Kevin Wojniak]

On Feb 3, 2011, at 10:35 PM, Dan Kennedy wrote:

> Do you have a test program that we can use to reproduce this phenomenon?
> 
> Dan.

Here is a complete program:
http://pastie.org/pastes/1527560

Set USE_TRIGGER to see the trigger version.

Thanks,
Kevin

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

Re: [sqlite] Trigger for incrementing a column is slow

[Dan Kennedy]

On 02/04/2011 06:01 AM, Kevin Wojniak wrote:
> On Feb 3, 2011, at 2:27 PM, Jim Wilcoxson wrote:
>
>> On Thu, Feb 3, 2011 at 5:07 PM, Kevin Wojniak  wrote:
>>
>>>
>>> On Feb 3, 2011, at 11:41 AM, Petite Abeille wrote:
>>>
 On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote:

> The trigger is ran once via sqlite3_exec();

 Hmm... you mean the trigger is run every single time you perform an
>>> insert, no?
>>>
>>> Yes. I should say the trigger is created once via sqlite3_exec().
>>>
> Any insight as to why the trigger is significantly slower?
>>>
>>
>> Perhaps SQLite is having to recompile the trigger SQL on every use, whereas
>> your update stmt is prepared.  I tried triggers once, and they were slow for
>> me too.
>>
>> Jim
>> --
>> HashBackup: easy onsite and offsite Unix backup
>> http://www.hashbackup.com
>
> If that were the case that'd seem like a major design flaw.
>
>
> I created a timing profile using the trigger and without. On the version 
> without, most of the time is spent all in sqlite, as expected. The one with 
> triggers had a ton more time spent in the various kernel file system 
> functions (hfs, I'm on Mac OS 10.6.6). So it seems like the triggers are 
> creating significant more file access. I have all the inserts surrounded by 
> BEGIN/END TRANSACTION, so I don't see why this would be doing any more 
> necessary work.

Do you have a test program that we can use to reproduce this phenomenon?

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

Re: [sqlite] Trigger for incrementing a column is slow

[Jim Wilcoxson]

On Thu, Feb 3, 2011 at 5:07 PM, Kevin Wojniak  wrote:

>
> On Feb 3, 2011, at 11:41 AM, Petite Abeille wrote:
>
> > On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote:
> >
> >> The trigger is ran once via sqlite3_exec();
> >
> > Hmm... you mean the trigger is run every single time you perform an
> insert, no?
>
> Yes. I should say the trigger is created once via sqlite3_exec().
>
> >> Any insight as to why the trigger is significantly slower?
>

Perhaps SQLite is having to recompile the trigger SQL on every use, whereas
your update stmt is prepared.  I tried triggers once, and they were slow for
me too.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger for incrementing a column is slow

[Simon Slavin]

On 3 Feb 2011, at 7:41pm, Petite Abeille wrote:

> On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote:
> 
>> The trigger is ran once via sqlite3_exec();
> 
> Hmm... you mean the trigger is run every single time you perform an insert, 
> no?
> 
>> Any insight as to why the trigger is significantly slower?
> 
> It adds significant overhead for each and every insert.

But his alternative to doing INSERT with a TRIGGER is to do an INSERT and then 
a manual UPDATE.  Are triggers really so inefficient that it's that much faster 
to do it manually ?

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

Re: [sqlite] Trigger for incrementing a column is slow

[Petite Abeille]

On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote:

> The trigger is ran once via sqlite3_exec();

Hmm... you mean the trigger is run every single time you perform an insert, no?

> Any insight as to why the trigger is significantly slower?

It adds significant overhead for each and every insert.

> I hope I'm missing something basic.

Yes! Don't use triggers :)

Do you really need to store that value? As oppose to have it computed? After 
all, you should always be able to query for it, no?

Alternatively, update it in bulk, e.g: update root set num_children = ( select 
count( * ) from root as parent where parent.parent_rowid = root.rowid ) where 
rowid in ( select distinct parent_rowid from root ) or something
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trigger for incrementing a column is slow

[Kevin Wojniak]

I've got a tree structure where whenever I insert a new node, I want its parent 
entry's number of children to increment.

I figured a trigger would be great for this, however it is very slow compared 
to just a standard UPDATE manually ran after the INSERT.

Here is the table:
CREATE TABLE root (rowid INTEGER PRIMARY KEY, parent_rowid INTEGER, name TEXT, 
num_children INTEGER);

and trigger:
CREATE TRIGGER update_num_children AFTER INSERT ON root
BEGIN
UPDATE root SET num_children = num_children + 1 WHERE rowid = NEW.parent_rowid;
END;

The trigger is ran once via sqlite3_exec();

I am testing with inserting 200,000 entries. With the trigger enabled, it takes 
about 15.5 seconds. When I disable the trigger, and run the UPDATE via a cached 
statement, it takes about 2.5 seconds:
UPDATE root SET num_children = num_children + 1 WHERE rowid = ?;

Any insight as to why the trigger is significantly slower? I hope I'm missing 
something basic.

Thanks,
Kevin

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