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
