On Tue, Feb 5, 2013 at 7:54 AM, Jason Gauthier <jgauth...@lastar.com> wrote: > 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? >
Put the delete in a transaction. John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
