Re: [sqlite] Slow SELECT query

[Kees Nuyt]

On Mon, 14 Sep 2009 19:58:31 +0200, Kees Nuyt
 wrote:

Oops, that should read

> 100 events per second during 15 years 
>   = 4730400
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow SELECT query

[Kees Nuyt]

On Mon, 14 Sep 2009 06:09:45 -0700 (PDT), wlof
 wrote:

>
>Thanks Kees. My app still needs some optimizations, your suggestions look
>good.
>
>One question though: what will happen when id reaches 2^32 - 1 ? (This app
>is going to log a lot of events, 24/7 for a long time, so it's not a purely
>theoretical question.)

SQLite uses 64 bit signed integers, so you get a max value
in the order of 2^63
= 9223372036854775810
10 events per second during 15 years 
= 4730400

See the difference? No issue.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow SELECT query

[wlof]

Thanks Kees. My app still needs some optimizations, your suggestions look
good.

One question though: what will happen when id reaches 2^32 - 1 ? (This app
is going to log a lot of events, 24/7 for a long time, so it's not a purely
theoretical question.)


Kees Nuyt wrote:
> 
> On Fri, 4 Sep 2009 06:43:05 -0700 (PDT), wlof
>  wrote:
> 
>>
>>Hi everyone,
>>
>>I'm using SQLite to maintain a log of events in my application.
>>
>>There is only one table:
>>CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY AUTOINCREMENT,
>>timestamp REAL, event TEXT)
>>
>>I don't want the log to grow indefinitely, so I periodically call a method
>>which makes the following query:
>>SELECT id FROM log ORDER BY timestamp DESC LIMIT 1,999
>>
>>A DELETE query is executed on every result.
>>
>>The problem is that, on a 1.3MB table (1 entries), the SELECT query
>>takes 12 seconds to be executed! This is way too long.
>>
>>I've added an index on the timestamp column:
>>CREATE INDEX IF NOT EXISTS log_idx ON log (id, timestamp)
>>
>>But there is little to no performance improvement.
>>
>>What am I doing wrong?
> 
> Apparently you want to delete all log entries except the
> last 1, without caring about the value of the timestamp.
> 
> Your table already has a monotonically increasing primary
> key (historical order). Maintaining an index on timestamp is
> redundant and slows down inserts.
> 
> I would suggest:
> 
> DELETE FROM log 
>  WHERE id < (
>   SELECT max(id) - 1
>  FROM log
>);
> 
> And if that isn't fast enough, you can even try:
> DELETE FROM log 
>  WHERE id < (
>   SELECT seq - 1
>  FROM sqlite_sequence
> WHERE name = 'log'
>);
> 
> (untested)
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Slow-SELECT-query-tp25294597p25435503.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow SELECT query

[aba . elhaddi]

Let's see if we can make the 7pm one.

Eh
Sent via BlackBerry from T-Mobile

-Original Message-
From: wlof <wlo...@gmail.com>

Date: Fri, 4 Sep 2009 07:52:43 
To: <sqlite-users@sqlite.org>
Subject: Re: [sqlite] Slow SELECT query




Sebastian Bermudez wrote:
> 
> 1)
> Ok, change the index to:
> CREATE INDEX IF NOT EXISTS log_idx ON log (timestamp)
> 
> column Id is not used for select...
> the Id column just is needed for delete operation.
> 
This seems to have done the trick. The SELECT query now takes ~15 ms. Much
better :)

I didn't think adding the id in the index would have such consequences! I
take it adding an index on id AND timestamp is definitely not the same thing
than adding an index on id AND adding an index on timestamp.

Thanks a lot for your help! It is much appreciated.


-- 
View this message in context: 
http://www.nabble.com/Slow-SELECT-query-tp25294597p25295851.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Slow SELECT query

[Kees Nuyt]

On Fri, 4 Sep 2009 06:43:05 -0700 (PDT), wlof
 wrote:

>
>Hi everyone,
>
>I'm using SQLite to maintain a log of events in my application.
>
>There is only one table:
>CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY AUTOINCREMENT,
>timestamp REAL, event TEXT)
>
>I don't want the log to grow indefinitely, so I periodically call a method
>which makes the following query:
>SELECT id FROM log ORDER BY timestamp DESC LIMIT 1,999
>
>A DELETE query is executed on every result.
>
>The problem is that, on a 1.3MB table (1 entries), the SELECT query
>takes 12 seconds to be executed! This is way too long.
>
>I've added an index on the timestamp column:
>CREATE INDEX IF NOT EXISTS log_idx ON log (id, timestamp)
>
>But there is little to no performance improvement.
>
>What am I doing wrong?

Apparently you want to delete all log entries except the
last 1, without caring about the value of the timestamp.

Your table already has a monotonically increasing primary
key (historical order). Maintaining an index on timestamp is
redundant and slows down inserts.

I would suggest:

DELETE FROM log 
 WHERE id < (
SELECT max(id) - 1
 FROM log
   );

And if that isn't fast enough, you can even try:
DELETE FROM log 
 WHERE id < (
SELECT seq - 1
 FROM sqlite_sequence
WHERE name = 'log'
   );

(untested)
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow SELECT query

[wlof]

Swithun Crowe wrote:
> 
> How about something like:
> 
> DELETE FROM log 
>   WHERE id IN (SELECT id 
>  FROM log 
>  ORDER BY timestamp DESC 
> LIMIT 1,999);
> 
> Which will only be one delete statement, instead of thousands.
I will try that, it sounds much better. I'm definitely no SQL expert so I
didn't think of nested queries. Thanks!

-- 
View this message in context: 
http://www.nabble.com/Slow-SELECT-query-tp25294597p25295879.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow SELECT query

[wlof]

Sebastian Bermudez wrote:
> 
> 1)
> Ok, change the index to:
> CREATE INDEX IF NOT EXISTS log_idx ON log (timestamp)
> 
> column Id is not used for select...
> the Id column just is needed for delete operation.
> 
This seems to have done the trick. The SELECT query now takes ~15 ms. Much
better :)

I didn't think adding the id in the index would have such consequences! I
take it adding an index on id AND timestamp is definitely not the same thing
than adding an index on id AND adding an index on timestamp.

Thanks a lot for your help! It is much appreciated.


-- 
View this message in context: 
http://www.nabble.com/Slow-SELECT-query-tp25294597p25295851.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow SELECT query

[Sebastian Bermudez]

1)
Ok, change the index to:
CREATE INDEX IF NOT EXISTS log_idx ON log (timestamp)

column Id is not used for select...
the Id column just is needed for delete operation.

2) what's log period your app.have ?
  Need Year ? Month ? Day ?
  can you change the REAL data type for an smaller one ? (integer /1/2/4/6 
bytes )??  REAL take 8 bytes...

3) login app logic changes:
  to log:
a. select max(timestamp), id from log_table
b. id=id+1
c. id=(id modulo 1)
d. instead of INSERT use replace with PK Id






--- El vie 4-sep-09, wlof  escribió:

> De: wlof 
> Asunto: [sqlite]  Slow SELECT query
> Para: sqlite-users@sqlite.org
> Fecha: viernes, 4 de septiembre de 2009, 10:43 am
> 
> Hi everyone,
> 
> I'm using SQLite to maintain a log of events in my
> application.
> 
> There is only one table:
> CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY
> AUTOINCREMENT,
> timestamp REAL, event TEXT)
> 
> I don't want the log to grow indefinitely, so I
> periodically call a method
> which makes the following query:
> SELECT id FROM log ORDER BY timestamp DESC LIMIT
> 1,999
> 
> A DELETE query is executed on every result.
> 
> The problem is that, on a 1.3MB table (1 entries), the
> SELECT query
> takes 12 seconds to be executed! This is way too long.
> 
> I've added an index on the timestamp column:
> CREATE INDEX IF NOT EXISTS log_idx ON log (id, timestamp)
> 
> But there is little to no performance improvement.
> 
> What am I doing wrong?
> 
> -- 
> View this message in context: 
> http://www.nabble.com/Slow-SELECT-query-tp25294597p25294597.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


  Yahoo! Cocina

Encontra las mejores recetas con Yahoo! Cocina.


http://ar.mujer.yahoo.com/cocina/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow SELECT query

[Swithun Crowe]

Hello

w I don't want the log to grow indefinitely, so I periodically call a method
w which makes the following query:
w SELECT id FROM log ORDER BY timestamp DESC LIMIT 1,999
w A DELETE query is executed on every result.

How about something like:

DELETE FROM log 
  WHERE id IN (SELECT id 
 FROM log 
 ORDER BY timestamp DESC 
LIMIT 1,999);

Which will only be one delete statement, instead of thousands.

w I've added an index on the timestamp column:
w CREATE INDEX IF NOT EXISTS log_idx ON log (id, timestamp)

You should only need an index on the timestamp column for the SELECT 
statement to work. The id column is already indexed as it is the primary 
key.

I hope this helps.

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users