On 11 Apr 2012, at 6:11pm, Ian Katz <ifreeca...@gmail.com> wrote: > The database system that I'm designing is for an autonomous vehicle; > it collects a lot of (data which is currently getting stored as a flat > text file). So, it's going to write a LOT of data into many tables > independently, occasionally do single-table reads, and at the end of > the day create a report that joins all the tables together. So, my > main goal is to give maximum speed to writes by compartmentalizing the > locks that the incoming reads will cause. It sounds like this use > case won't hit any of the disadvantages mentioned above.
Had you left the 'occasionally do single-table reads' out a solution might be to DROP your INDEXes while putting the data in, then CREATE them when you're about to do your reading. > In the past, I've tried to do this in SQLite and MySQL by putting all > the data in a single table (timestamp, variable name, value). But, if > you index the variable name then writes become too slow (by the end of > a day's worth of data collection, the next round of data comes in > before the previous round is written); if you don't index then the > table is impossible to select from in any reasonable amount of time. > So, the solution seems to be splitting every variable into its own > table -- not very good normalization, but retaining good read > performance without having write performance degrade over time. The > join-all-tables-together query would be used for generating a logfile > in the old format, just in case we need it. Design guideline: If your batches of data all have the same columns, put them in the same table. If two batches of data have different columns, they belong in different tables. There are exceptions to this, of course, but it's a guideline. It would seem that your vehicle delivers a bunch of values at once, so they should probably be different columns of a table, and the primary key of the row should be the time they were delivered, using whatever coding for the time you find convenient. > Am I missing any features of SQLite that would solve this problem in a > different/better way? You haven't really identified a problem. A problem would be "I need X to work in 200ms and it takes 260ms.". We might be able to answer that one. On the other hand "I need it to work in 5ms and it takes 260ms." also lets us give you an answer: not gonna happen, don't waste your time trying. Telling us 'maximise speed' doesn't give us enough idea about what to suggest. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
