Since your data is at least mostly opaque in the sense that SQLite is not expected to interpret the contents, why not split your data into "stuff you want to query ins SQLite" and "stuff you want to just store"? The former means individual columns, whereas the latter could be stored in a single BLOB field, which only your application knows how to extract data from.
This allows SQLite to efficiently process the fields it needs to know about, and return BLOB data efficiently as one single field instead of having to pick it apart into 100k bits. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mitar Gesendet: Donnerstag, 17. Oktober 2019 10:56 An: Richard Hipp <d...@sqlite.org> Cc: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] Limit on number of columns in SQLite table Hi! I can see how this is a reasonable limit when SQLite is used for querying power it provides. In our case we are really focusing on it as a standard long-term storage format. So in the "Appropriate Uses For SQLite" document [1] you have a section called "File archive and/or data container" and this is why we started considering SQLite as a dataset archive format. We would not like to store files directly, but contents of those files (like contents of CSV). But try to not modify them more than necessary. So we got interested especially in the "SQLite is a good solution for any situation that requires bundling diverse content into a self-contained and self-describing package for shipment across a network." statement. So 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, and have it at runtime by default limited to 2000. And then using PRAGMA one could increase this if needed to 2 billion? PRAGMA already can decrease the limit, so we can keep the existing 2000 limit, but to support it without having to recompile, people could increase it all the way to 2 billion. Is there any significant performance downside to this? [1] https://www.sqlite.org/whentouse.html Mitar On Wed, Oct 16, 2019 at 8:21 PM Richard Hipp <d...@sqlite.org> wrote: > > SQLite could, in theory, be enhanced (with just a few minor tweaks) to > support up to 2 billion columns. But having a relation with a large > number of columns seems like a very bad idea stylistically. That's > not how relational databases are intended to be used. Normally when a > table acquires more than a couple dozen columns, that is a good > indication that you need normalize and/or refactor your schema. Schema > designers almost unanimously follow that design principle. And so > SQLite is optimized for the overwhelmingly common case of a small > number of columns per table. > > Hence, adding the ability to have a table with a huge number of > columns is not something that I am interested in supporting in SQLite > at this time. > > -- > D. Richard Hipp > d...@sqlite.org -- http://mitar.tnode.com/ https://twitter.com/mitar_m _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
