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? > > It adds significant overhead for each and every insert. But it seems way more than what's necessary since the manual UPDATE is ~6x faster. > 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? The database is written once, and then read multiple times, so writes don't need to be as efficient as reads. Every time I pull out a node, if the node can contain child nodes I need to also obtain the number of children, so my initial thinking is it'd be faster to index it instead of querying each time. > 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 This definitely seems the better way to go, once all the inserts are complete. My SQL isn't very good so thanks for this! On Feb 3, 2011, at 1:26 PM, Simon Slavin wrote: > On 3 Feb 2011, at 7:41pm, Petite Abeille wrote: > >>> 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. Exactly. If anything the trigger should be faster, I would imagine. Kevin _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
