> 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.
To me it sounds that it will just move the performance problem from the indexed column to another separated table which might as well need an index on "variable name". I also don't see why a lookup will be faster via attached databases: Doesn't this just move the lookup to sqlite's attach command ? The locking issue of a SELECT might be improved by using wal mode. Anyway, you didn't specify how these names look like - in case they are chars with, say, more than 8 bytes you might trying to add a CRC integer hash on the variable name and index that value. Your selects might then look like SELECT * FROM table WHERE VarName = 'abc' AND VarCRC=N; This approach avoids to do string comparisons on table lookup and indexing. It also reduces the size of the index. Marcus > > -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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
