Re: [sqlite] Deletion slow?

[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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deletion slow?

[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 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?

[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 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  wrote:
> 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 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 
> ___
> 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?

[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 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 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deletion slow?

[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 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

Re: [sqlite] Deletion slow?

[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 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?

[Michael Black]

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?

[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 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?

[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 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?

[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 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

Re: [sqlite] Deletion slow?

[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 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?

[Michael Black]

I made a test database using your table and this
main()
{
int i;
for(i=0;i<1;++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?

[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
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?

[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 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?

[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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deletion slow?

[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 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

[sqlite] Deletion slow?

[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 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