I did not get any answers at all, so what are my next steps? Should I submit a bug report somewhere?
David -----Original Message----- From: David Jud Sent: Friday, June 19, 2009 12:08 PM To: '[email protected]' Subject: very bad performance with triggers and indexes Hi I have a very strange performance problems involving indexes and triggers. A somewhat simplified example of what I want to do (but which allows to reproduce the behavior) is the following: CREATE TABLE MY_FC (ID integer, DESCRIPTION text); I then proceed to insert 50000 rows from my program (using the System.Data.SQLite API; I do 5000 inserts each in a transaction), which runs in about 0.4 seconds. I add a logging table and an after insert trigger which logs into the logging table: CREATE TABLE MY_FC_LOG (ID integer, TIMESTAMP text); CREATE TRIGGER my_fc_aid after insert on my_fc for each row begin insert into my_fc_log (id, timestamp) values (new.id, current_timestamp); end; Inserting 50000 rows takes about 0.6 seconds, which is still very good :). Then I add an index: CREATE UNIQUE INDEX my_fc_pk ON my_fc (id); Now inserting 50000 rows takes about 12 seconds, or a factor 20 longer! When I drop the trigger to get rid of the logging: DROP TRIGGER my_fc_aid; Then the 50000 rows are back at ~0.6 seconds. So the performance is good both with the trigger and with the index individually, but if I combine both things get very slow. The funny thing is that the trigger doesn't even use the index; the two should not relate at all. Is this a known problem, a bug, or am I just doing something wrong? best Regards, David _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
