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
