On 2014/12/09 22:41, Rene Zaumseil wrote:
Hi there,
I have to store and retrieve up to 2000 parameters.
The parameters can have real and integer values.
The max. change rate is 100ms and the max. duration is up to some hours.
The simple solution would be to use plain binary files. It's fast but not
flexible.
So I came to sqlite. Before starting I would like to ask if someone could give
me some advice which way to go. Here are my current versions.
Version 1: One big table with time stamp and one column for each parameter
- I can have up to SQLITE_MAX_COLUMNS=2000 but how can I insert all values
with only SQLITE_MAX_VARIABLE_NUMBER=999?
- What about space consumption if NULL values are given?
Version 2: One table for each parameter with time stamp and value
- Does this scale for up to 2000 parameters?
- Can sqlite handle so much tables?
Version 3: One table with time stamp, parameter id and parameter value
- Is it working when all values change?
- Is retrieving values for one parameter fast?
Definitely No. 3 always. SQLite will retrieve a value from among 2000 items on an indexed column in under 1ms (as will any other
localized DB). Even I/O lag will be masked by the cache at that size.
Assuming the parameter names do not change all the time and the time-stamp column is not indexed, writing will be similarly fast
using a suitable journal mode. On this point, you might simply use an in-memory DB since you do not care about data loss on power
failure (use ":memory:" as the file-name) which will be even faster but with an on-disk DB you will have a snapshot remain of the
current parameter set if your application fails - which might assist debugging.
Also, making that DB, be sure to use "WITHOUT ROWID" and specifying the parameter-name as the primary key. Use Numeric affinity for
time-stamp and value columns.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
