Converting variable name to variable ID (with separate lookup table) was one of my first ideas, but turns out that the lookup itself was a bigger hit in performance than the indexing. I'll revisit that and see if I failed to tweak something properly.
-Ian On Wed, Apr 11, 2012 at 1:22 PM, Pavel Ivanov <paiva...@gmail.com> wrote: >> 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 -- 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
