> When doing so: Any idea of what would be the worst case column select > strategy on the huge table to compare with?
Take some query from your typical usage where only last column of the huge table is used and all other columns either not used at all or just one-two of them used for other conditions (assuming you don't have index on this last column). I believe it would be the worst case for huge table usage. Pavel On Tue, Jan 12, 2010 at 4:44 PM, Stefan_E <se_m...@hotmail.com> wrote: > > Hi Pavel, > > thanks a lot; I will reconsider this option and run some test cases to > compare. > But that will be tomorrow I guess... > When doing so: Any idea of what would be the worst case column select > strategy on the huge table to compare with? > > Stefan > > > Pavel Ivanov-2 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 >> >> > > -- > View this message in context: > http://old.nabble.com/Limitation-on-Column-count-tp27117364p27135309.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
