One may be able to make "measurement type" a column, thus eliminating the need for a column for each type.
Some speed may be recoverable with indexing. regards, Adam On Tue, Jan 12, 2010 at 1:21 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > > so normalization would lead to a doubling > > of the storage space (add a measurement_id to each measurement). > > My strong belief is that when you try this normalization you'll see > that such doubling of storage is a good enough trade-off for the speed > you'll achieve. I don't think that speed of queries on the table with > 100+ columns would be any useful (of course unless you *always* select > all columns and *never* try to select only a few ones). > > > Second, the most common use case is to view the table in the currently > > foreseen format - so, I'd pay both in space and time... > > Most probably you view your table from your application which can > denormalize the table very quickly. Even if you view your table from > sqlite3 command line tool you still can write denormalizer even using > bash scripts and I believe it will still work fast enough and it will > be better than creating such huge table. > > > Pavel > > On Tue, Jan 12, 2010 at 1:09 PM, Stefan_E <se_m...@hotmail.com> wrote: > > > > Hi Adam, > > > > thanks for your suggestion. Unfortunately, it doesn't help in my case. > > Essentially, we are talking about a time series (rows) of n different > > measurements (columns) - so normalization would lead to a doubling > > of the storage space (add a measurement_id to each measurement). > > > > Second, the most common use case is to view the table in the currently > > foreseen format - so, I'd pay both in space and time... > > > > Anyway, thanks for the suggestion! > > > > Regards, Stefan > > > > > > Adam DeVita wrote: > >> > >> Good day, > >> > >> In general I try to work within the limits of any database engine that I > >> am > >> using. Often, the limits are there for good reasons (such as speed > >> problems). I would suggest seeing if there is a way to normalize the > big > >> tables such that infrequently used columns are split into tables that > >> aren't > >> joined in often. (The principal I'm using is borrowed from hardware > >> architecture "Make the common case fast, and ensure the uncommon case is > >> correct.") > >> > >> It may or may not be sensible given your data, but there may be an > >> opportunity to reduce the number of columns by making an encoded column > >> to > >> aggregate, such as lots of mutually exclusive binary flag fields. > >> > >> regards, > >> Adam > >> > >> On Mon, Jan 11, 2010 at 3:46 PM, Stefan_E <se_m...@hotmail.com> wrote: > >> > >>> > >>> Hello all, > >>> > >>> I'm considering using SQLite for a new application. The schema will > >>> contain > >>> a bunch of small tables with few columns (~10) plus one large table > with > >>> many columns and 1000...10000 rows. > >>> > >>> 'Many columns' typically fits into the default 2000 column limit, but > can > >>> exceed it at times (that is, on some of the foreseen databases). It > will > >>> never exceed the theoretical / compile time selectable limit of 32k > >>> columns. > >>> Queries on this big table will be rather straight-forward: either on > the > >>> table alone (SELECT * FROM table_large) or one join on one field to one > >>> of > >>> the smaller tables. > >>> > >>> The http://www.sqlite.org/limits.html Limits page warns: "There are > >>> places > >>> in the SQLite code generator that use algorithms that are O(N²) where N > >>> is > >>> the number of columns." which is kind of discouraging to increase max. > >>> column count at compile time, but is not very specific about when this > >>> happens... > >>> > >>> I now have two design options: > >>> - increase max. column count at compile time (possibly setting > >>> SQLITE_LIMIT_COLUMN on databases where I don't need more than (say) > 1000 > >>> columns) and accept the quoted performance degradation. > >>> - alternatively, in the client handle cases with more than 2000 > columns, > >>> splitting the storage up into two (or more) tables > >>> > >>> Any advise, experience - or more specifics on the "O(N²)" remark are > >>> highly > >>> welcome! > >>> > >>> Thanks for your help - Stefan > >>> -- > >>> View this message in context: > >>> > http://old.nabble.com/Limitation-on-Column-count-tp27117364p27117364.html > >>> Sent from the SQLite mailing list archive at Nabble.com. > >>> > >>> _______________________________________________ > >>> sqlite-users mailing list > >>> sqlite-users@sqlite.org > >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >>> > >> > >> > >> > >> -- > >> VerifEye Technologies Inc. > >> 905-948-0015x245 > >> 7100 Warden Ave, Unit 3 > >> Markham ON, L3R 8B5 > >> Canada > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > >> > > > > -- > > View this message in context: > http://old.nabble.com/Limitation-on-Column-count-tp27117364p27131144.html > > Sent from the SQLite mailing list archive at Nabble.com. > > > > _______________________________________________ > > 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 > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
