Re: [sqlite] Slow select from database

[Simon Slavin]

On 12 Mar 2014, at 10:38am, Георгий Жуйков  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow select from database

[Niall O'Reilly]

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

Re: [sqlite] Slow select from database

[Valentin Davydov]

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

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