Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)

[Hick Gunter]

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 <sqlite-users@mailinglists.sqlite.org>
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)

[Simon Slavin]

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)

[Dominique Pellé]

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)

[Clemens Ladisch]

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)

[Jens Alfke]

> 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)

[Clemens Ladisch]

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

[sqlite] Any performance penalty for SELECTing more columns? (C API)

[Jens Alfke]

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