[sqlite] Does column order matter for performance?

[Hick Gunter]

Actually it does. Sort of.

 The beginning of the record is the "manifest" (actual type of data list) area. 
This contains tokens representing the actual type/contents of the fields. 
Sticking with the example this will be 99 bytes containing the tokens "null", 
"zero", "one" and "integer" followed by one variable sized token "blob".

 The length of the manifest is stored in it's first field, giving the offset of 
the "payload" (data item list) which contains the 0-99 variable sized integers 
that are not NULL, 0 or 1, followed by the actual blob data. So the amount of 
work required to locate the blob data varies considerably.

-Urspr?ngliche Nachricht-
Von: Simon Slavin [mailto:slavins at bigfraud.org]
Gesendet: Freitag, 01. Mai 2015 11:32
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Does column order matter for performance?


On 1 May 2015, at 6:49am, Pol-Online  wrote:

> I?m curious: how much work are we talking about here? If I have 99 INTEGER 
> fields followed by a BLOB one, to access the BLOB field, does SQLite need to 
> compute the size of each previous INTEGER field first?

Given the way that SQLite uses different sized fields for different integers, 
yes.  Also, given that SQLite columns have affinity and not type, it has to 
calculate these sizes separately for each row retrieved.  It can't use the 
table definition and calculate one set of positions for all rows.

Fortunately, the lengths of all columns in a row can be calculated by looking 
at the serial types of all the columns, which are stored in a block at the 
beginning of the row data.  So the format is not

Type of column 1, data 1, type of column 2, data 2, type of column 3, data 3 ...

it is

Type of column 1, type of column 2, type of column 3 ..., data 1, data 2, data 
3 ...

so SQLite doesn't have to read through variable-sized data to work out where 
later columns are.

More info in places around section 2.1 of

<http://www.sqlite.org/fileformat.html>

Simon.
___
sqlite-users mailing list
sqlite-users at 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: hick at 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] Does column order matter for performance?

[Simon Slavin]

On 1 May 2015, at 6:49am, Pol-Online  wrote:

> I?m curious: how much work are we talking about here? If I have 99 INTEGER 
> fields followed by a BLOB one, to access the BLOB field, does SQLite need to 
> compute the size of each previous INTEGER field first?

Given the way that SQLite uses different sized fields for different integers, 
yes.  Also, given that SQLite columns have affinity and not type, it has to 
calculate these sizes separately for each row retrieved.  It can't use the 
table definition and calculate one set of positions for all rows.

Fortunately, the lengths of all columns in a row can be calculated by looking 
at the serial types of all the columns, which are stored in a block at the 
beginning of the row data.  So the format is not

Type of column 1, data 1, type of column 2, data 2, type of column 3, data 3 ...

it is

Type of column 1, type of column 2, type of column 3 ..., data 1, data 2, data 
3 ...

so SQLite doesn't have to read through variable-sized data to work out where 
later columns are.

More info in places around section 2.1 of



Simon.

[sqlite] Does column order matter for performance?

[Pol-Online]

> (2) Even if the row is all on one page, SQLite has to compute sizes of
> all prior columns and add those sizes together in order to find the
> offset to a particular column in that row. The fewer prior columns
> there are, the less work is involved for this step.

I?m curious: how much work are we talking about here? If I have 99 INTEGER 
fields followed by a BLOB one, to access the BLOB field, does SQLite need to 
compute the size of each previous INTEGER field first?




-Pol


Pol-Online
info at pol-online.net (mailto:info at pol-online.net)

[sqlite] Does column order matter for performance?

[Simon Slavin]

On 30 Apr 2015, at 6:16pm, Drago, William @ CSG - NARDA-MITEQ  wrote:

> Read Mr. Hipp's reply to me when I asked a similar question:

To rephrase (1) slightly, when reading a row from a table, SQLite reads up to 
the last column asked for and no further.  So if you have columns you rarely 
need to read, put them at the end of the list.

Simon.

[sqlite] Does column order matter for performance?

[Drago, William @ CSG - NARDA-MITEQ]

Read Mr. Hipp's reply to me when I asked a similar question:


"Two things to be aware of:

(1) When reading a row, SQLite reads from beginning to end.  So if you have
some small integer or boolean fields, it is better to put them first in the
table. Otherwise, SQLite has to read past the big BLOBs in order to get to
the smaller fields, even if the BLOBs themselves are not used.

(2) When changing any column of a row, the entire row is rewritten,
including the unchanged columns.  So if you have some smaller fields
(integers and booleans) that change frequently and also some large BLOBs
that change infrequently, you might consider factoring the BLOBs out into a
separate table just so they don't have to be rewritten every time a boolean
in the same row changes.

Both points above a purely performance considerations.  You should always
get the correct answer either way."


Here's the entire discussion:
http://sqlite.1065341.n5.nabble.com/BLOBs-and-NULLs-td75201.html

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Pol-Online
> Sent: Thursday, April 30, 2015 12:45 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Does column order matter for performance?
>
> Hi,
>
> I wasn?t able to find the answer to this question online: does the
> column order matter for SQLite performance? E.g. should you put fixed
> width columns like INTEGER before TEXT or BLOB?
>
>
> -Pol
>
> 
> Pol-Online
> info at pol-online.net (mailto:info at pol-online.net)
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Does column order matter for performance?

[Richard Hipp]

On 4/30/15, James K. Lowden  wrote:
>
> SQLite keeps its rows in pages and reads whole pages.  Within a page it
> references rows and columns by offset.  Every location within a page is
> accessed in constant time.  So column order doesn't matter because
> page-offset doesn't matter.
>

It's more complicated than that.

(1)  A single row might span multiple pages.  SQLite will only read
pages from disk for the prefix of the row it actually needs.

(2) Even if the row is all on one page, SQLite has to compute sizes of
all prior columns and add those sizes together in order to find the
offset to a particular column in that row.  The fewer prior columns
there are, the less work is involved for this step.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Does column order matter for performance?

[James K. Lowden]

On Thu, 30 Apr 2015 09:45:14 -0700
Pol-Online  wrote:

> I wasn?t able to find the answer to this question online: does the
> column order matter for SQLite performance? E.g. should you put fixed
> width columns like INTEGER before TEXT or BLOB?  

I'm going to check that box as No.  

SQLite keeps its rows in pages and reads whole pages.  Within a page it
references rows and columns by offset.  Every location within a page is
accessed in constant time.  So column order doesn't matter because
page-offset doesn't matter.  

Most database performance problems are design problems.  If you pay
attention to cardinality and index definition, the rest will usually
take care of itself.   

--jkl

[sqlite] Does column order matter for performance?

[Pol-Online]

Hi,

I wasn?t able to find the answer to this question online: does the column order 
matter for SQLite performance? E.g. should you put fixed width columns like 
INTEGER before TEXT or BLOB?  


-Pol


Pol-Online
info at pol-online.net (mailto:info at pol-online.net)