[Lazarus] data matrix with thousands of columns

[Reinier Olislagers]

On 27-3-2013 10:49, Mark Morgan Lloyd wrote:
> Andrea Mauri wrote:
>> Il 27/03/2013 09:27, Michael Schnell ha scritto:
>>> On 03/26/2013 03:44 PM, Andrea Mauri wrote:
>> Ok I will explain better.
>>
>> I have a GUI app.
>>
>> The user loads samples (the rows), my app performs calculations on
>> samples and for every sample give as output thousands of values (the
>> columns). Samples could be from tens to hundreds of thousands.
>> Columns can be tens to thousands.
>> Every column is an attribute of the sample defined by a unique name.
>> After calculations the app/user should be able to search for one/more
>> samples (or for one/more columns) getting all/some values for the
>> sample/column. Briefly I need to be able to rapidly get some values
>> from this huge data matrix.
> 
> I think that there's a risk that any solution that relies on having a
> large number of columns in a database could suddenly stop working if the
> data exceeds some server-specific limit. Granted that this limit has
> expanded over the years but in a pathological case where a very large
> input table (i.e. lots of rows) containing worst-case data was in effect
> rotated by 90 degrees (i.e. lots of columns) it could still be significant.
> 
> I think my choice would be to generate an intermediate table, if
> necessary with extra explicit indexes, that allowed the final query to
> extract and process only rows that matched certain criteria, even if
> they were then presented as columns.

If using a relational database, I would also propose that solution.
Messing with columns each time is just not what they're designed for.

Additionally, if you ever need to scale it up or store data in a
database server: different dbs have different limits on max number of
columns, performance may suffer when you have a huge amount of columns etc.