Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)
Try using the sqlite shell program and the explain feature. You will see that each column fetched requires an additional opcode to do the fetching and an additional register to hold the result, all of this on top of requiring SQLite to decode all of these fields without you ever intending to use the results of all this CPU labor. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jens Alfke Gesendet: Samstag, 15. Oktober 2016 20:01 An: SQLite mailing list Betreff: [sqlite] Any performance penalty for SELECTing more columns? (C API) In a simple SELECT query of a single table, using the C API, is there any difference in performance for requesting more or fewer columns of the table in the result? Or is the performance penalty only incurred when actually reading the column values? For example, lets say a table has 26 columns, some of which can be large strings/blobs. I prepare two statements: SELECT a FROM mytable WHERE … SELECT a,b,c,d,…,z FROM mytable WHERE … If I run both of those queries, but in my loop I only read the value of ‘a’, by calling sqlite3_column_text(stmt, 0), is there any difference in speed? I am guessing that there isn’t a difference. (I could run tests, but I don’t want to be dependent on a detail that might change.) If so, this will make it easier to create my queries, since I won’t have to fine-tune their column sets based on what columns I need in each different use case. —Jens ___ 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 FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)
On 15 Oct 2016, at 7:34pm, Jens Alfke wrote: > I do, actually, which is why I asked. One of the columns is a blob holding a > JSON document that can be arbitrarily large. It sounds like including this > column in the SELECT clause will cause the entire blob to be read from disk? > I definitely don’t want that. When reading a row of the data asked for SQLite reads through the first column of the row to the end of the column which is last in the table. In other words CREATE TABLE MyTable (a, b, c, d, e, f, g, h); SELECT c, f FROM MyTable; means that SQLite has to read through columns from a to f. It won't fetch the data from long TEXT or BLOB values but it needs to know how long they are to figure out where the following column's value starts. So if you don't need column f don't ask for it. And definitely don't use SELECT * FROM MyTable; unless that's what you need. The additional twist that Dominique mentioned is that in SQLite an index entry contains the values it's indexing. So suppose you have the above table and CREATE INDEX i1 on MyTable (c, d, e); SELECT c, d, e, f FROM MyTable ORDER BY c, d; then if your SELECT ends up using that index the only value SQLite actually needs to fetch from the table is the one for column f. Because it already has the values for c, d and e because it just read them from the index. And if SQLite doesn't need to read any values from the table it doesn't bother reading the table at all. Which speeds things up a lot. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)
Jens Alfke wrote: > In a simple SELECT query of a single table, using the C API, > is there any difference in performance for requesting more or > fewer columns of the table in the result? Or is the performance > penalty only incurred when actually reading the column values? There can sometimes be a big difference. The less columns you select, the more chance you have to be able to use a covering index. If the query can use a covering index, it's likely to have a significant speed up. According to section 8.0 at https://www.sqlite.org/optoverview.html being able to use a covering index makes the query about twice as fast. Regards Dominique ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)
Jens Alfke wrote: > What if I’ve enabled memory-mapping? In that case will the register > merely point to where the blob data is mapped into memory This would not work because the data might not be in consecutive pages. (The database file format was not designed for memory mapping.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)
> On Oct 15, 2016, at 11:10 AM, Clemens Ladisch wrote: > > In practice, this does not matter unless you have large strings/blobs > that must be read from overflow pages. I do, actually, which is why I asked. One of the columns is a blob holding a JSON document that can be arbitrarily large. It sounds like including this column in the SELECT clause will cause the entire blob to be read from disk? I definitely don’t want that. What if I’ve enabled memory-mapping? In that case will the register merely point to where the blob data is mapped into memory, without incurring any I/O until I access that memory? (Maybe that’s naïve; I know SQLite isn’t LMDB. I don’t have any insight into how it makes use of memory mapping.) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)
Jens Alfke wrote: > is there any difference in performance for requesting more or fewer > columns of the table in the result? Or is the performance penalty only > incurred when actually reading the column values? During the sqlite3_step() call, all values in the SELECT clause are copied into temporary registers. In practice, this does not matter unless you have large strings/blobs that must be read from overflow pages. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users