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
