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 char, f2 char, f3 char, f4 char); asql> .explain asql> explain select * from 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
