If you don't put a COMMIT on your mysql example I don't think you're comparing apples-to-apples.
I'm guessing your Rasberry PI sdcard isn't exactly a high-speed performer http://jalada.co.uk/2012/05/20/raspberry-pi-sd-card-benchmark.html How long does it take you to import your database for example? -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jason Gauthier Sent: Tuesday, February 05, 2013 6:44 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Deletion slow? 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
