OK. I think you guys have answered my question: I'm not asking the right question. I need to get better data on my performance requirements if I'm going to start asking the right sort of questions.
For now, I'll see what I can learn about using WAL mode and go from there. Thanks for the insightful responses! -Ian On Wed, Apr 11, 2012 at 3:30 PM, Marcus Grimm <mgr...@medcom-online.de> wrote: >> 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 -- 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
