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
