There were a few responses to this, so I will put them all below:

Igor:
>The difference between select and delete is that the former only reads from 
>the file, while the latter also writes to it. What kind of hardware does your 
>>system have? Is there any reason why it may be unusually slow writing to disk 
>(or whatever device you are storing files on)? 1.5 sec to delete 200 records 
>>does sound excessive (for a regular PC with database file stored on a hard 
>drive), even considering that three indexes need to be updated.

The system is a raspberry pi.  ARM processor running around 700Mhz. 256MB of 
memory, and an sdcard filesystem.

Dominique:
>Well, you're paying for the maintenance of the indexes, 4 of them. Try the 
>delete with fewer indexes, and you'll see the delete time improve.
>There's not much you can do about it I'm afraid.

>BTW, tell us the total count(*) and .db file size, and perhaps your DB page 
>size as well. --DD

I dropped and recreated the table leaving only 2 indexes. The primary, and 
icur_time.
I'm down to ~1s.

# time sqlite3 trip.db 'delete from trip where key<=600'
real    0m0.911s
user    0m0.020s
sys     0m0.020s
# time sqlite3 trip.db 'delete from trip where key<=800'
real    0m0.952s
user    0m0.000s
sys     0m0.040s

Total count of the table is about 40k records.   Not sure how to retrieve DB 
page size.

> Load the same data into another RDBMS you're familiar with, and see how it 
> compares perf-wise.

I built the table on the same system with mysql. I loaded the same 40k records 
and ran the same deletion.
Here are my results:
mysql> delete from trip where id<=84540;
Query OK, 201 rows affected (0.09 sec)
mysql> delete from trip where id<=84740;
Query OK, 200 rows affected (0.15 sec)

It definitely performs better, but I really do not want to use such a large 
piece of software for this.

Thanks for help so far.  I really appreciate all the responses.

Jason



_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to