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
