Hi! In that case we would have to define a standard BLOB storage format, slightly defeating the idea of using SQLite to define such standard future-proof format. :-)
Mitar On Thu, Oct 17, 2019 at 11:19 AM Hick Gunter <h...@scigames.at> wrote: > > 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 -- 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
