If you need to perform queries over the 100k rows, then normalization is the only practical way and reconstructing a row will similarly slow.
But you have stated that you use case is "retrieving complete rows". In this case, SQLite does not need to know the 100k details of the row. Keep your data in whatever format they come in - which is another requirement you have expressed. All you have to extract from the 100k details is the columns that you need to identify the row(s) you want back. Create table gene_data (sample_id integer primary key, name char, raw_data blob); Anything else you need to do with the row data goes into your application. -----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
