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
