Re: [sqlite] Deletion slow?
Wondering how nobody suggested, did you try PRAGMA synchronous = OFF ?? For me it is always the default... I can imagine how slow such a combination can be. Just my 2c... Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deletion slow?
Ah, I did not understand this. I ran three tests after enabling this: root@raspberrypi:/opt/obdpi/sql# sqlite3 trip.db 'PRAGMA journal_mode=WAL' wal # time sqlite3 trip.db delete from trip where key=200 real0m0.642s [edited] Sqlite4 time /root/sqlite4/sqlite4 trip.db delete from trip where key=1358697579 real0m0.623s # time /root/sqlite4/sqlite4 trip.db delete from trip where key=1358697779 Thanks for reading, this was a long one! I just wanted to follow up on this. It sounds like from our discussion I am doing everything I can, and at this point this is probably a limitation of the hardware. I reduced the indexes from 4 to 1, and realized some performance increase. I also tested with sqlite4, and also had some more performance increase. Should I call this day, and leave it like this with sqlite3, or preferably, maybe compile against sqlite4, if this speed is a requirement? Thanks! Jason ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deletion slow?
On Tue, 5 Feb 2013 12:54:13 + Jason Gauthier jgauth...@lastar.com wrote: 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. Any thoughts on why this may be so slow, or what I can do to improve it? Don't know if the index is updated after each row delete or after the whole delete transaction is commited. For the first you can try: time sqlite3 trip.db PRAGMA automatic_index= FALSE; delete from trip where key=1400;PRAGMA automatic_index= TRUE; reindex trip If you delete a high percentage of the table rows, it's faster select the data to save in a new table, drop original table and alter table temp rename to trip. If you use a memory temp table and a powerloss happens, your data is lost, use a non-temp table. The trick of adding a new column for mark dirty rows will not work because you are using a sd-card, the cost of mark as delete/dirty those rows is greater than deleting them. Thanks, Jason ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- --- Eduardo Morras emorr...@yahoo.es ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deletion slow?
Don't know if the index is updated after each row delete or after the whole delete transaction is commited. For the first you can try: time sqlite3 trip.db PRAGMA automatic_index= FALSE; delete from trip where key=1400;PRAGMA automatic_index= TRUE; reindex trip PRAGMA automatic_index has nothing to do with updating index after deleting each row. And it won't have any impact on this delete statement because it can't use automatic index. Pavel On Thu, Feb 7, 2013 at 2:13 AM, Eduardo Morras emorr...@yahoo.es wrote: On Tue, 5 Feb 2013 12:54:13 + Jason Gauthier jgauth...@lastar.com wrote: 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. Any thoughts on why this may be so slow, or what I can do to improve it? Don't know if the index is updated after each row delete or after the whole delete transaction is commited. For the first you can try: time sqlite3 trip.db PRAGMA automatic_index= FALSE; delete from trip where key=1400;PRAGMA automatic_index= TRUE; reindex trip If you delete a high percentage of the table rows, it's faster select the data to save in a new table, drop original table and alter table temp rename to trip. If you use a memory temp table and a powerloss happens, your data is lost, use a non-temp table. The trick of adding a new column for mark dirty rows will not work because you are using a sd-card, the cost of mark as delete/dirty those rows is greater than deleting them. Thanks, Jason ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- --- Eduardo Morras emorr...@yahoo.es ___ 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
Re: [sqlite] Deletion slow?
On Tue, Feb 5, 2013 at 8:09 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Feb 5, 2013 at 7:43 PM, Jason Gauthier jgauth...@lastar.comwrote: I built the table on the same system with mysql. Have you tried using SQLite4 (http://www.sqlite.org/src4)? You should be able to download the latest check-in and type ./configure; make to build a shell in which to run the experiment. I think it might be a lot faster in your case. Would love to hear about your results from trying this experiment. Also, please try your speed measurement using SQLite3 again, but in WAL mode. Set WAL mode by running: PRAGMA journal_mode=WAL; on the database before running the speed trial. I look forward to seeing the results of these experiments. Jason ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deletion slow?
On Tue, Feb 5, 2013 at 7:43 PM, Jason Gauthier jgauth...@lastar.comwrote: I built the table on the same system with mysql. Have you tried using SQLite4 (http://www.sqlite.org/src4)? You should be able to download the latest check-in and type ./configure; make to build a shell in which to run the experiment. I think it might be a lot faster in your case. Would love to hear about your results from trying this experiment. Also, please try your speed measurement using SQLite3 again, but in WAL mode. Set WAL mode by running: PRAGMA journal_mode=WAL; on the database before running the speed trial. I look forward to seeing the results of these experiments. Richard, Thanks so much for your ideas. Trying the PRAMGA mode was easy, so I did that first: # time sqlite3 trip.db PRAGMA journal_mode=WAL; delete from trip where key=1400; real0m1.410s # time sqlite3 trip.db PRAGMA journal_mode=WAL; delete from trip where key=1600; real0m1.554s # time sqlite3 trip.db delete from trip where key=1800; real0m1.830s I am struggling with sqlite4, though. I've never used fossil, so I may have approached this wrong. I grabbed it with: fossil clone http://www.sqlite.org/src4 sqlite4.fossil and then 'fossil open sqlite4.fossil'. However, there isn't a 'configure' inside this, so I am attempting to 'make'. Unfortunately, the build environment is having a issues with this. Did I grab the latest check-in the way you assumed I would? Thanks, Jason ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deletion slow?
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' real0m0.911s user0m0.020s sys 0m0.020s # time sqlite3 trip.db 'delete from trip where key=800' real0m0.952s user0m0.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
Re: [sqlite] Deletion slow?
On Wed, Feb 6, 2013 at 9:10 AM, Jason Gauthier jgauth...@lastar.com wrote: Thanks so much for your ideas. Trying the PRAMGA mode was easy, so I did that first: # time sqlite3 trip.db PRAGMA journal_mode=WAL; delete from trip where key=1400; real0m1.410s # time sqlite3 trip.db PRAGMA journal_mode=WAL; delete from trip where key=1600; real0m1.554s # time sqlite3 trip.db delete from trip where key=1800; real0m1.830s I'm not sure that theses tests are meaningful. First off, the conversion to WAL mode takes time, and that time is being included in the result. Secondly, WAL mode is persistent. Once it is set it stays set until it is changed. So you cannot turn around and run another test after setting WAL mode and expect the subsequent test to be in rollback mode. What I would like to see is something like this: cp trip-original.db trip.db sqlite3 trip.db 'PRAGMA journal_mode=WAL' time sqlite3 trip.db 'DELETE FROM trip WHERE key=1400' cp trip-original.db trip.db sqlite3 trip.db 'PRAGMA journal_mode=DELETE' time sqlite3 trip.db 'DELETE FROM trip WHERE key=1400' I am struggling with sqlite4, though. I've never used fossil, so I may have approached this wrong. I grabbed it with: fossil clone http://www.sqlite.org/src4 sqlite4.fossil and then 'fossil open sqlite4.fossil'. You probably have fossil going, then. Which is good. But you could have just clicked on the Download: Tarball link or the Download: ZIP Archive link at http://www.sqlite.org/src4/info/7cc153f523 in order to get a copy of the latest code. Now that you have a fossil clone, you can always get the latest code by running: fossil update trunk However, there isn't a 'configure' inside this, so I am attempting to 'make'. Unfortunately, the build environment is having a issues with this. Did I grab the latest check-in the way you assumed I would? Yeah - I guess we don't have a configure script yet. You'll need to edit the makefile to make it work on your system. My guess is that you probably just need to go to http://www.sqlite.org/src4/artifact/72944b28eb3a?ln=161-167 and select some compiler other than clang. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deletion slow?
I'm not sure that theses tests are meaningful. First off, the conversion to WAL mode takes time, and that time is being included in the result. Secondly, WAL mode is persistent. Once it is set it stays set until it is changed. So you cannot turn around and run another test after setting WAL mode and expect the subsequent test to be in rollback mode. What I would like to see is something like this: cp trip-original.db trip.db sqlite3 trip.db 'PRAGMA journal_mode=WAL' time sqlite3 trip.db 'DELETE FROM trip WHERE key=1400' cp trip-original.db trip.db sqlite3 trip.db 'PRAGMA journal_mode=DELETE' time sqlite3 trip.db 'DELETE FROM trip WHERE key=1400' Ah, I did not understand this. I ran three tests after enabling this: root@raspberrypi:/opt/obdpi/sql# sqlite3 trip.db 'PRAGMA journal_mode=WAL' wal # time sqlite3 trip.db delete from trip where key=200 real0m0.642s # time sqlite3 trip.db delete from trip where key=400 real0m1.262s # time sqlite3 trip.db delete from trip where key=600 real0m1.322s I went ahead and ran the same tests without it, for reference (basically the same as the original email -- all 4 indexes) # cp trip-original.db trip.db # time sqlite3 trip.db delete from trip where key=200 real0m0.686s # time sqlite3 trip.db delete from trip where key=400 real0m1.545s # time sqlite3 trip.db delete from trip where key=600 real0m1.539s It looks marginal. You probably have fossil going, then. Which is good. But you could have just clicked on the Download: Tarball link or the Download: ZIP Archive link at http://www.sqlite.org/src4/info/7cc153f523 in order to get a copy of the latest code. Now that you have a fossil clone, you can always get the latest code by running: fossil update trunk Yeah - I guess we don't have a configure script yet. You'll need to edit the makefile to make it work on your system. My guess is that you probably just need to go to http://www.sqlite.org/src4/artifact/72944b28eb3a?ln=161-167 and select some compiler other than clang. Looks like I had to actually change this to gcc as well: http://www.sqlite.org/src4/artifact/72944b28eb3a?ln=22-22 Unfortunately, I ran into a few issues compiling with 'make'. rtree.c looks to have had sqlite3_malloc /free changed to sqlite4, but not the sqlite4_env parameter passed. I went ahead and set it to NULL, as mentioned in http://www.sqlite.org/src4/doc/trunk/www/porting.wiki I had to change a few instances of sqlite_int64 to sqlite4_int64 in the same file. I had some trouble linking functions in that same file as well. Anyway, I got through it.. I fixed some things and commented out others.. so this might not be functional =) I reimported the database, but the primary key being a real primary key in 4 did change the query. So, the numbers are much higher now. Results: # time /root/sqlite4/sqlite4 trip.db delete from trip where key=1358697579 real0m0.623s # time /root/sqlite4/sqlite4 trip.db delete from trip where key=1358697779 real0m0.430s # time /root/sqlite4/sqlite4 trip.db delete from trip where key=1358697979 real0m0.611s There is some definite improvement there. 2-3x! In case you're interested: # sqlite4 trip.db 'PRAGMA journal_mode=WAL' # sqlite4 trip.db delete from trip where key=1358698379 real0m0.748s # time sqlite4 trip.db delete from trip where key=1358698579 real0m0.818s # time sqlite4 trip.db delete from trip where key=1358698779 real0m0.816s Thanks for reading, this was a long one! -- D. Richard Hipp d...@sqlite.org ___ 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
[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 real0m0.026s user0m0.020s sys 0m0.000s --- Notice there are only 200 rows that match this query! --- time sqlite3 trip.db 'select * from trip where key=1400' real0m0.205s user0m0.030s sys 0m0.070s --- 200ms is not bad. --- time sqlite3 trip.db 'delete from trip where key=1400' real0m1.532s user0m0.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
Re: [sqlite] Deletion slow?
On Tue, Feb 5, 2013 at 7:54 AM, Jason Gauthier jgauth...@lastar.com wrote: 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 real0m0.026s user0m0.020s sys 0m0.000s --- Notice there are only 200 rows that match this query! --- time sqlite3 trip.db 'select * from trip where key=1400' real0m0.205s user0m0.030s sys 0m0.070s --- 200ms is not bad. --- time sqlite3 trip.db 'delete from trip where key=1400' real0m1.532s user0m0.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? Put the delete in a transaction. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deletion slow?
On 2/5/2013 8:35 AM, John Drescher wrote: Put the delete in a transaction. It's a single statement, it runs in a single implicit transaction. An explicit transaction shouldn't make any difference. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deletion slow?
On 2/5/2013 7:54 AM, Jason Gauthier wrote: time sqlite3 trip.db 'delete from trip where key=1400' real0m1.532s user0m0.050s sys 0m0.020s --- The deletion takes 1.5 seconds. This is actually fast, it usually takes closer 2 seconds. 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. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deletion slow?
On Tue, Feb 5, 2013 at 1:54 PM, Jason Gauthier jgauth...@lastar.com wrote: 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. Load the same data into another RDBMS you're familiar with, and see how it compares perf-wise. time sqlite3 trip.db 'select count(*) from trip where key=1400' real0m0.026s time sqlite3 trip.db 'select * from trip where key=1400' real0m0.205s time sqlite3 trip.db 'delete from trip where key=1400' real0m1.532s Any thoughts on why this may be so slow, or what I can do to improve it? 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deletion slow?
I made a test database using your table and this
main()
{
int i;
for(i=0;i1;++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'
real0m0.004s
user0m0.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: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jason Gauthier
Sent: Tuesday, February 05, 2013 6:54 AM
To: sqlite-users@sqlite.org
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
real0m0.026s
user0m0.020s
sys 0m0.000s
---
Notice there are only 200 rows that match this query!
---
time sqlite3 trip.db 'select * from trip where key=1400'
real0m0.205s
user0m0.030s
sys 0m0.070s
---
200ms is not bad.
---
time sqlite3 trip.db 'delete from trip where key=1400'
real0m1.532s
user0m0.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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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' real0m0.911s user0m0.020s sys 0m0.020s # time sqlite3 trip.db 'delete from trip where key=800' real0m0.952s user0m0.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
Re: [sqlite] Deletion slow?
On 6 Feb 2013, at 12:43am, Jason Gauthier jgauth...@lastar.com wrote: Not sure how to retrieve DB page size. http://www.sqlite.org/pragma.html#pragma_page_size You might also be interested in http://www.sqlite.org/pragma.html#pragma_page_count Note that this does, of course, include pages which no longer have any useful data in because you've just done a DELETE command. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
