database design and performance

Question

Hi all,

I have some question regarding database performance in general. I'm using Sqlite but I assume that the performance remarks are applicable to all relational databases?

I have a database that contains a table that stores data of about 200 variables. I write about 50 variables per second to the table. A writen variable contains the id of the variable, a value and a timestamp. Readig is done very rarely but needs to be as fast as possible to get the data per variable in chronological order. When I do a query I always just need to get the data of 1 variable.

How do I design the database so the reading is as fast as possible: 1. I make 1 tabel that contains all the variables. The variable is stored as an id. I index the table on the id and timestamp. The bad part is that the index makes the write slowe(r). 2. I make 200 tables for each variable and index the timestamp.

I think the second solution is the most performant but creaying a table for each variable doesn't seem right. Someone can give me some advice?

Thanks

Answer 1

Are you sure you need a database? By the sounds of it, a flat-file will work well enough for you, and you don't sound like you actually need any of the trappings of a database. Just create a flat-file for each variable and keep handles to each open. Write to them through your standard buffered IO as often as you need. To read, just open one file and deserialize.

Answer 2

If you are using a relational database, I am guessing those variables are all related? If they are just values, for instance, settings, then maybe a file or something similar may be better.

If you only ever have to query values for ONE variable, then, if you insist on using a database (which may not be a bad thing!), then you should create one table per variable: id (unsigned int, auto-increment, primary key) timestamp (datetime) variable (whatever it is supposed to be)

Do not skimp on data just because "it might take more room on the hard drive" - that only leads to trouble.

Answer 3

If you really want to use a database, use the first approach, but make sure you are inserting your data in a single transaction; benchmarks show it makes writing much faster. Are your searches performed on variable name/id AND timestamp, or variable name only. Indexing on timestamp may not be necessary...