Slow select from database

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Slow select from database

Георгий Жуйков
1 . We have a database of measurements: time DATETIME, name TEXT, value
NUMERIC
indexes:
'tags_name_index' ON 'TAGS' ('NAME' ASC)
'tags_name_itemtime_index' ON 'TAGS' ('NAME' ASC ', ITEMTIME' ASC)
In case of record auto_vacuum=INCREMENTAL flag is used

C#.NET of 4.0 application.
Used System.Data.SQLite.x86 - 1.0.90.0, is set through NuGet.
Windows 7 x64, i7-2600 @3.4 GHz, RAM 16 GB

2 . The average database contains:
- about 40 thousand records
- about 1100 unique names
- the minimum quantity of records addressed to - 1 in the hour file.
- the maximum number of records addressed to - 39 thousand in the hour file.
- average record count addressed to - 6 thousand in the hour file.

3 . databases divide measurements on hour base

4 . The request of data is made for time slot, i.e. from several databases.
For example:
SELECT COUNT (*) as COUNTER FROM Entries WHERE (TIMESTAMP BETWEEN @STARTTIME
AND @ENDTIME)
SELECT * from Entries WHERE (TIMESTAMP BETWEEN @STARTTIME AND @ENDTIME) of
ORDER BY TIMESTAMP DESC LIMIT 1000 OFFSET 0

5 . Initially all requests are expedited.
After a while (about 50 minutes) the same requests start being executed more
slowly, request to each database (from 1 to 30 minutes).
Repeated request of the same data - quickly.
The System.Data.SQLite.x86 updating to version 1.0.91.3 doesn't bring any
positive result.

What to do?

If necessary we can provide a database.

Best regards, George.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow select from database

Valentin Davydov-2
On Wed, Mar 12, 2014 at 02:38:15PM +0400, Георгий Жуйков wrote:
> 1 . We have a database of measurements: time DATETIME, name TEXT, value
> NUMERIC
> indexes:
> 'tags_name_index' ON 'TAGS' ('NAME' ASC)
> 'tags_name_itemtime_index' ON 'TAGS' ('NAME' ASC ', ITEMTIME' ASC)
> In case of record auto_vacuum=INCREMENTAL flag is used
[skip]
> 4 . The request of data is made for time slot, i.e. from several databases.
> For example:
> SELECT COUNT (*) as COUNTER FROM Entries WHERE (TIMESTAMP BETWEEN @STARTTIME
> AND @ENDTIME)
> SELECT * from Entries WHERE (TIMESTAMP BETWEEN @STARTTIME AND @ENDTIME) of
> ORDER BY TIMESTAMP DESC LIMIT 1000 OFFSET 0

I can't comletely understand your database schema, but why didn't you use
index on just DATETIME field when selecting data of given time period?

> 5 . Initially all requests are expedited.
> After a while (about 50 minutes) the same requests start being executed more
> slowly, request to each database (from 1 to 30 minutes).
> Repeated request of the same data - quickly.
> The System.Data.SQLite.x86 updating to version 1.0.91.3 doesn't bring any
> positive result.
>
> What to do?

First try to figure out where it spends so much time (I suspect random
disk reads: it seems to be no other slow physical phenomena in your
system, except probably database locking by some other process which you
didn't mention). Then either revise logic of your application (e.g. by
adding an abovementioned index, changing autovacuum to forced vacuum
sceduled at specific time of day etc.) or give it more resources (say,
by putting the database on an SSD drive).

Valentin Davydov.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow select from database

Niall O'Reilly
In reply to this post by Георгий Жуйков
At Wed, 12 Mar 2014 14:38:15 +0400,
Георгий Жуйков wrote:
>
> 1 . We have a database of measurements: time DATETIME, name TEXT, value
> NUMERIC
> indexes:
> 'tags_name_index' ON 'TAGS' ('NAME' ASC)
> 'tags_name_itemtime_index' ON 'TAGS' ('NAME' ASC ', ITEMTIME' ASC)
> In case of record auto_vacuum=INCREMENTAL flag is used

  [...]

> 4 . The request of data is made for time slot, i.e. from several databases.
> For example:
> SELECT COUNT (*) as COUNTER FROM Entries WHERE (TIMESTAMP BETWEEN @STARTTIME
> AND @ENDTIME)
> SELECT * from Entries WHERE (TIMESTAMP BETWEEN @STARTTIME AND @ENDTIME) of
> ORDER BY TIMESTAMP DESC LIMIT 1000 OFFSET 0

  You seem to be saying that your table has columns time, name, and value;
  that you index on NAME and ITEMTIME; and that you query on TIMESTAMP.

  Apart from name and NAME, none of this matches up.

  I expect you need an index on whatever TIMESTAMP is.  If you choose to
  use a compound key for the index, you'll need to take care to make
  TIMESTAMP the first component of this key.

  I hope this helps.

  Best regards,
  Niall O'Reilly
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow select from database

Simon Slavin-3
In reply to this post by Георгий Жуйков

On 12 Mar 2014, at 10:38am, Георгий Жуйков <[hidden email]> wrote:

> 1 . We have a database of measurements: time DATETIME, name TEXT, value
> NUMERIC
> indexes:
> 'tags_name_index' ON 'TAGS' ('NAME' ASC)
> 'tags_name_itemtime_index' ON 'TAGS' ('NAME' ASC ', ITEMTIME' ASC)
> In case of record auto_vacuum=INCREMENTAL flag is used

There is no such datatype as 'DATETIME'.  If you are storing a string, use a datatype of TEXT.  If you are storing an INTEGER, use a datatype of INTEGER.  If you are storing any other number, use a datatype of REAL.

Also, as a general hint, it's a bad idea to use column names like "time", "name" and "value".  The third of these is clearly a reserved word in SQL, and the other two are so common they're bound to be confused with other things.  Better column names are things like 'sampleTime' and 'sampleSource', and 'reading'.

> 4 . The request of data is made for time slot, i.e. from several databases.
> For example:
> SELECT COUNT (*) as COUNTER FROM Entries WHERE (TIMESTAMP BETWEEN @STARTTIME
> AND @ENDTIME)
> SELECT * from Entries WHERE (TIMESTAMP BETWEEN @STARTTIME AND @ENDTIME) of
> ORDER BY TIMESTAMP DESC LIMIT 1000 OFFSET 0
>
> 5 . Initially all requests are expedited.
> After a while (about 50 minutes) the same requests start being executed more
> slowly, request to each database (from 1 to 30 minutes).

You are searching on your timestamp field but have not provided any index on it.  This causes SQLite to have to search the entire table.  If you create an index like

CREATE INDEX i6 ON tags (time)

then SQLite will be able to find qualifying rows without having to scan the entire table.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users