> Am I missing any features of SQLite that would solve this problem in a > different/better way?
If you didn't try it I would suggest to try a single table (timestamp, variable id, value). Index on integer variable id will work faster than text variable name. Other than that I'd say your design of one table per database is well justified. Especially if you won't attach all databases together while inserting data, but will connect to necessary database directly. Pavel On Wed, Apr 11, 2012 at 1:11 PM, Ian Katz <ifreeca...@gmail.com> wrote: > These are all good points, and introduce some features of sqlite that > I didn't know existed! > > 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. > > 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. > > Am I missing any features of SQLite that would solve this problem in a > different/better way? > > -Ian > > On Wed, Apr 11, 2012 at 12:20 PM, Pavel Ivanov <paiva...@gmail.com> wrote: >> On Wed, Apr 11, 2012 at 12:01 PM, Ian Katz <ifreeca...@gmail.com> wrote: >>> The Sqlite3 manual says that any locking operations affect the entire >>> database, not individual tables. >>> http://www.sqlite.org/lockingv3.html >>> >>> I was wondering if this effect could be compensated for by splitting >>> tables into separate databases and using the "attach database" option >>> outlined here: >>> http://stackoverflow.com/questions/6671678/objective-c-sqlite-join-tables-from-multiple-database >>> >>> I would assume that the databases will not become locked until the >>> statement is executed (i.e., preparing the statement won't lock it). >>> Is that correct? >> >> Yes, that's correct, although I don't see a link between this >> statement and "attache database" discussion above. >> >>> If so, is there a significant disadvantage or >>> performance hit to using this workaround? >> >> The first performance hit that comes to mind is either you won't be >> able to use WAL mode (which is a significant performance hit) or you >> lose overall atomicity of transactions (see disadvantage point 3 here >> http://www.sqlite.org/wal.html). >> >> So I wouldn't do that if I were you. >> >> >> Pavel >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > Ian Katz > Research Software Engineer, MIT LAMSS > i...@mit.edu > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
