On Jun 23, 2009, at 1:56 AM, David Jud wrote: > I did not get any answers at all, so what are my next steps? Should > I submit a bug report somewhere?
Try omitting the index and making ID an INTEGER PRIMARY KEY. > > 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 D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
