Re: [sqlite] Deletion slow?

2013-02-18 Thread Gabriel Corneanu
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

Re: [sqlite] Deletion slow?

2013-02-10 Thread Jason Gauthier
>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

Re: [sqlite] Deletion slow?

2013-02-07 Thread Pavel Ivanov
> 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

Re: [sqlite] Deletion slow?

2013-02-07 Thread Eduardo Morras
On Tue, 5 Feb 2013 12:54:13 + Jason Gauthier 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

Re: [sqlite] Deletion slow?

2013-02-06 Thread Jason Gauthier
>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

Re: [sqlite] Deletion slow?

2013-02-06 Thread Richard Hipp
On Wed, Feb 6, 2013 at 9:10 AM, Jason Gauthier 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

Re: [sqlite] Deletion slow?

2013-02-06 Thread Michael Black
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

Re: [sqlite] Deletion slow?

2013-02-06 Thread Jason Gauthier
> On Tue, Feb 5, 2013 at 7:43 PM, Jason Gauthier wrote: > >> 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

Re: [sqlite] Deletion slow?

2013-02-06 Thread Richard Hipp
On Tue, Feb 5, 2013 at 8:09 PM, Richard Hipp wrote: > > > On Tue, Feb 5, 2013 at 7:43 PM, Jason Gauthier wrote: > >> 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

Re: [sqlite] Deletion slow?

2013-02-05 Thread Simon Slavin
On 6 Feb 2013, at 12:43am, Jason Gauthier wrote: > Not sure how to retrieve DB page size. You might also be interested in Note that this does, of course, include

Re: [sqlite] Deletion slow?

2013-02-05 Thread Jason Gauthier
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

Re: [sqlite] Deletion slow?

2013-02-05 Thread Michael Black
rison. -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 parti

Re: [sqlite] Deletion slow?

2013-02-05 Thread Dominique Devienne
On Tue, Feb 5, 2013 at 1:54 PM, Jason Gauthier 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

Re: [sqlite] Deletion slow?

2013-02-05 Thread Igor Tandetnik
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

Re: [sqlite] Deletion slow?

2013-02-05 Thread Igor Tandetnik
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

Re: [sqlite] Deletion slow?

2013-02-05 Thread John Drescher
On Tue, Feb 5, 2013 at 7:54 AM, Jason Gauthier 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

[sqlite] Deletion slow?

2013-02-05 Thread Jason Gauthier
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