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