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
