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: [email protected]
[mailto:[email protected]] On Behalf Of Jason Gauthier
Sent: Tuesday, February 05, 2013 6:54 AM
To: [email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
