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
