On 17 Oct 2019, at 9:56am, Mitar <mmi...@gmail.com> wrote: > I can understand how supporting a large number of columns might be > inappropriate when you want to run complicated SQL queries on data, > but to just store data and then extract all rows to do some data > processing, Or as the most complicated query it would be to extract > just a subsample of rows. But not really do to any JOIN queries or > something like that. it looks like except for artificial limit in > SQLite, because it is not useful for general case, there is no other > reason why it could not be supported. > > So why not increase the limit to 2 billion
1) Almost no piece of software can handle a grid 2 billion cells wide. Excel maxes out at 16,384 columns. Matlab can store and retrieve a cell of data directly from a file, but it has a max array size of 10000. R maxes out at 2147483647, which is more than 2 billion. But R has to hold all the data from a matrix in memory at once and it can't assign enough memory to one object to hold that many cells. 2) Object names are not data. They're descriptions in your favourite human language. They're not meant to have weird sequences of characters in. 3) Lots of CSV import filters ignore a column header row, or can only create fieldnames with certain limits (max length, no punctuation characters, etc.). So you should expect to lose fieldnames if you try to import your data into some new piece of software. And here't the dealbreaker: (4) SQLite stores all the data for a row is together, in a sequence. If you ask for the data in the 3756th column of a row, SQLite has to read and parse the data for the first 3755 columns of that row, just to read a single value from storage. As you can imagine, this is slow and involves a lot of I/O. And while it happens the row up to that point must all be held in memory. Consequently, nobody who uses SQLite for its intended purpose actually does this. I dread to think how slow random access over 2 billion columns would be in SQLite. So rereading the text from our message I quoted at the top. No. Nobody would ever use SQLite to store a table 2 billion columns wide even if they didn't intend to do anything complicated to it. Because it would take a ridiculous amount of time and memory just to insert data in to a table that wide. Your gene expressions are data. They are not the names of table entities. They should be stored in a table as other posts suggested. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
