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