I made a test database using your table and this main() { int i; for(i=0;i<10000;++i) { printf("insert into trip(key) values(%d);\n",i); } }
The deleted all keys < 200. time sqlite3 trip.db 'delete from trip where key < 200' real 0m0.004s user 0m0.001s sys 0m0.003s I assume you're running on your "not quite" machine? Are you disk-based? What's the speed of that? Can you generate that same database like this and we can then actually compare speed? Otherwise you're in a 1-off situation which is not very useful for comparison. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jason Gauthier Sent: Tuesday, February 05, 2013 6:54 AM To: sqlite-users@sqlite.org Subject: [sqlite] Deletion slow? Hey Everyone, I am a fairly new user of sqlite, but not particularly new to SQL principles. I am developing an application that will run on a low end system. Not quite embedded, but not quite a PC. In my application, I do frequent table deletes. My results have been poor, and I am looking for someone to tell me "I'm doing it wrong", or maybe "that's the best you're going to get", etc. Anyway, my table is create as such: create table trip ( key integer primary key, vin varchar(17), ts int, cur_time int, caps varchar(20), cmdid int, value real, longitude real, latitude real); create index ivin on trip (vin); create index icaps on trip (caps); create index icur_time on trip (cur_time); sqlite> .indices icaps icur_time ivin I understand that a primary key index is created automatically, so it won't be listed here. Now, I can execute a queries very quickly: ----------------------------------- time sqlite3 trip.db 'select count(*) from trip where key<=1400' 200 real 0m0.026s user 0m0.020s sys 0m0.000s ----------------------------------- Notice there are only 200 rows that match this query! ----------------------------------- time sqlite3 trip.db 'select * from trip where key<=1400' real 0m0.205s user 0m0.030s sys 0m0.070s ----------------------------------- 200ms is not bad. ----------------------------------- time sqlite3 trip.db 'delete from trip where key<=1400' real 0m1.532s user 0m0.050s sys 0m0.020s ----------------------------------- The deletion takes 1.5 seconds. This is actually fast, it usually takes closer 2 seconds. Any thoughts on why this may be so slow, or what I can do to improve it? Thanks, Jason _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users