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
