If you should happen to have the need for storing columns that each have only a small set of possible values, maybe you would be better off looking into FastBit, which has coulmn-oriented storage
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mitar Gesendet: Donnerstag, 17. Oktober 2019 15:46 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table Hi! Thanks for this input. So you are saying that sqlite3_column 100k times per row is slow, but retrieving 100k rows to construct one "original" row will be faster? So not sure if I understand why reading and decoding cells in over multiple columns is so much slower than reading and decoding cells in over multiple rows? Mitar On Thu, Oct 17, 2019 at 3:38 PM Hick Gunter <h...@scigames.at> wrote: > > I have the impression that you still do not grasp the folly of a 100k column > schema. > > See the example below, which only has 6 fields. As you can see, each field > requires a Column opcode and arguments (about 10 bytes) and a "register" to > hold the value (48 bytes), which for 100k columns uses about 5.5Megabytes to > retrieve a row from the database. It ill also involve SQLite decoding 100k > field values and your application calling sqlite3_column interface 100k times > for each and every row, which yield an expected performance of about 2 rows > per second. Can you afford to use that much memory and time? > > asql> create temp table genes (id integer primary key, name char, f1 > asql> char, f2 char, f3 char, f4 char); .explain explain select * from > asql> genes; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 13 0 00 Start at 13 > 1 OpenRead 0 2 1 6 00 root=2 iDb=1; genes > 2 Explain 2 0 0 SCAN TABLE genes 00 > 3 Rewind 0 12 0 00 > 4 Rowid 0 1 0 00 r[1]=rowid > 5 Column 0 1 2 00 r[2]=genes.name > 6 Column 0 2 3 00 r[3]=genes.f1 > 7 Column 0 3 4 00 r[4]=genes.f2 > 8 Column 0 4 5 00 r[5]=genes.f3 > 9 Column 0 5 6 00 r[6]=genes.f4 > 10 ResultRow 1 6 0 00 output=r[1..6] > 11 Next 0 4 0 01 > 12 Halt 0 0 0 00 > 13 Transaction 1 0 1 0 01 usesStmtJournal=0 > 14 Goto 0 1 0 00 > > -----Ursprüngliche Nachricht----- > Von: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von > Mitar > Gesendet: Donnerstag, 17. Oktober 2019 15:11 > An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Betreff: [EXTERNAL] Re: [sqlite] Limit on number of columns in SQLite > table > > Hi! > > On Thu, Oct 17, 2019 at 3:04 PM Eric Grange <zar...@gmail.com> wrote: > > my suggestion would be to store them as JSON in a blob, and use the > > JSON functions of SQLite to extract the data > > JSON has some crazy limitations like by standard it does not support full > floating point spec, so NaN and infinity cannot be represented there. So JSON > is really no a great format when you want to preserve as much of the input as > possible (like, integers, floats, text, and binary). SQLite seems to be spot > on in this regard. > > But yes, if there would be some other standard to SQLite and supported format > to embed, that approach would be useful. Like composite value types. > > > Mitar > > -- > 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 ___________________________________________ 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
