On 12 Mar 2014, at 10:38am, Георгий Жуйков <g.zhui...@intecom-energo.ru> 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
