Re: [sqlite] Order of fields for insert

[Jay Kreibich]

All I/O is done via page sized blocks.  So the minimum amount of data to be
fetched will always be a page.  The bigger issue is, as you said, when you
need to follow a chain of pages to get a small value at the end.

 -j

On Thu, Oct 6, 2016 at 9:53 AM, Paul Sanderson  wrote:

> > Long columns, especially TEXT or BLOBs which may have lots of data in,
> should go at the end.  Because you don't want SQLite to have to fetch all
> that data from storage just to get at the column after it.
>
> To be pedantic SQLite does not need to "fetch" all of the data from
> strorage before a needed column, it just needs to be able to skip it -
> unless the data oveflows in to one or more overflow pages then it will
> need to fetch each page until it reaches the one with the data in it.
> If the complete row is held in one page and your query just needs the
> last column - SQLite just needs to know the size of all of the data
> that preceedes the column you want. There is still the overhead of
> decoding every serial type before the column you require.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Jay Kreibich]

On Thu, Oct 6, 2016 at 9:25 AM, Hick Gunter  wrote:

> SQLite compresses rows before storing and decompresses rows before
> returning fields. BLOB fields are the most time consuming to process and so
> should be placed at the end of the row. Often used fields - i.e. (foreign)
> key fields - should be placed at the front of the row. This will help most
> if your select field list is limited to the fields you actually need
> instead of "*".
>
>
Sorta, kinda, but not really.  SQLite does not use a traditional data
compression algorithm in storing row data, but it does "pack" rows into a
compact format (including variable size integers).  As such, a row's worth
of data, as stored in the raw database, acts very similar to a compressed
block of data... you have to read it from the start and can't directly jump
to a field in a middle of it.

This is the issue with column ordering; the data engine will only read and
unpack the columns it needs, but it has to read and unpack the columns in
the order they're defined until it gets all the columns it needs.  This
makes it generally better to put more frequently accessed and/or smaller
values at the start of a row.

 -j
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Paul Sanderson]

> Long columns, especially TEXT or BLOBs which may have lots of data in, should 
> go at the end.  Because you don't want SQLite to have to fetch all that data 
> from storage just to get at the column after it.


To be pedantic SQLite does not need to "fetch" all of the data from
strorage before a needed column, it just needs to be able to skip it -
unless the data oveflows in to one or more overflow pages then it will
need to fetch each page until it reaches the one with the data in it.
If the complete row is held in one page and your query just needs the
last column - SQLite just needs to know the size of all of the data
that preceedes the column you want. There is still the overhead of
decoding every serial type before the column you require.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Krishna Shukla]

ok

On 6 Oct 2016 8:08 p.m., "Simon Slavin"  wrote:

>
> On 6 Oct 2016, at 3:37pm, Krishna Shukla 
> wrote:
>
> > Help how can i import exel file in sqlite and get result in c# desktop
> > application ...?
>
> Please start a new thread for this question.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Simon Slavin]

On 6 Oct 2016, at 2:46pm, Jeff Archer  wrote:

> Are there any performance or other considerations of the order of the
> fields for an insert?

No.  Order of columns in the CREATE TABLE command matters.  Order they're named 
in operations after that doesn't.

When SQLite needs to fetch values from a table row, it has to start reading the 
row at the first column mentioned in CREATE TABLE, then go through it reading 
data until it has reached the last column it needs.  Because of this it's best 
to put short, frequently-needed columns first in your CREATE TABLE command.

Long columns, especially TEXT or BLOBs which may have lots of data in, should 
go at the end.  Because you don't want SQLite to have to fetch all that data 
from storage just to get at the column after it.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Simon Slavin]

On 6 Oct 2016, at 3:37pm, Krishna Shukla  wrote:

> Help how can i import exel file in sqlite and get result in c# desktop
> application ...?

Please start a new thread for this question.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Krishna Shukla]

Help how can i import exel file in sqlite and get result in c# desktop
application ...?

On 6 Oct 2016 7:55 p.m., "Hick Gunter"  wrote:

> SQLite compresses rows before storing and decompresses rows before
> returning fields. BLOB fields are the most time consuming to process and so
> should be placed at the end of the row. Often used fields - i.e. (foreign)
> key fields - should be placed at the front of the row. This will help most
> if your select field list is limited to the fields you actually need
> instead of "*".
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Jeff Archer
> Gesendet: Donnerstag, 06. Oktober 2016 15:46
> An: SQLite mailing list 
> Betreff: [sqlite] Order of fields for insert
>
> Just a quick question.  I am actually deciding if I need to do some
> performance testing of this but thought I might gain some quick insight.
> My specific insert and table are below but really I am looking for a
> general answer to the question not just this specific case.
>
> Are there any performance or other considerations of the order of the
> fields for an insert?
> Are the following equivalent?  regardless of number of values inserting?
>  regardless of size of the data being inserted?
>
> INSERT INTO
> ​mytable(
> wid1,cnt,
> ​dat,​
> wid3,wid2) VALUES (?,?,?,?)
> ​ - VS - ​
>
> INSERT INTO
> ​mytable(
> wid1,wid2,wid3,cnt
> ​,dat​
> ) VALUES (?,?,?,?)
>
>
> CREATE TABLE
> ​mytable
> (
> ​id
>  INTEGER PRIMARY KEY AUTOINCREMENT"
> ,
> ​wid1
>  INTEGER REFERENCES
> ​othertable
> (
> ​id
> ) ON DELETE CASCADE
> ,
> ​wid2
>  INTEGER REFERENCES
> othertable
> (
> ​id
> ) ON DELETE CASCADE
> ,
> ​wid3
>  INTEGER REFERENCES
> othertable
> (
> ​id
> ) ON DELETE CASCADE
> ,
> ​cnt
>  INTEGER DEFAULT
> ​1
> ,dat TEXT
> )
>
> Jeff
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Paul Sanderson]

SQLite does not use any compression when storing data.

Occasionally rows have so much data that they overflow to an additonal
page(s) so the advice about defining tables so that blobs are at the
end of the definition is good - also columns that store long strings
might be better at the end of a table definition to avoid the same
sort of overflow.




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 6 October 2016 at 15:25, Hick Gunter  wrote:
> SQLite compresses rows before storing and decompresses rows before returning 
> fields. BLOB fields are the most time consuming to process and so should be 
> placed at the end of the row. Often used fields - i.e. (foreign) key fields - 
> should be placed at the front of the row. This will help most if your select 
> field list is limited to the fields you actually need instead of "*".
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Jeff Archer
> Gesendet: Donnerstag, 06. Oktober 2016 15:46
> An: SQLite mailing list 
> Betreff: [sqlite] Order of fields for insert
>
> Just a quick question.  I am actually deciding if I need to do some 
> performance testing of this but thought I might gain some quick insight.
> My specific insert and table are below but really I am looking for a general 
> answer to the question not just this specific case.
>
> Are there any performance or other considerations of the order of the fields 
> for an insert?
> Are the following equivalent?  regardless of number of values inserting?
>  regardless of size of the data being inserted?
>
> INSERT INTO
> mytable(
> wid1,cnt,
> dat,
> wid3,wid2) VALUES (?,?,?,?)
> - VS -
>
> INSERT INTO
> mytable(
> wid1,wid2,wid3,cnt
> ,dat
> ) VALUES (?,?,?,?)
>
>
> CREATE TABLE
> mytable
> (
> id
>  INTEGER PRIMARY KEY AUTOINCREMENT"
> ,
> wid1
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> wid2
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> wid3
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> cnt
>  INTEGER DEFAULT
> 1
> ,dat TEXT
> )
>
> Jeff
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Hick Gunter]

SQLite compresses rows before storing and decompresses rows before returning 
fields. BLOB fields are the most time consuming to process and so should be 
placed at the end of the row. Often used fields - i.e. (foreign) key fields - 
should be placed at the front of the row. This will help most if your select 
field list is limited to the fields you actually need instead of "*".

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jeff Archer
Gesendet: Donnerstag, 06. Oktober 2016 15:46
An: SQLite mailing list 
Betreff: [sqlite] Order of fields for insert

Just a quick question.  I am actually deciding if I need to do some performance 
testing of this but thought I might gain some quick insight.
My specific insert and table are below but really I am looking for a general 
answer to the question not just this specific case.

Are there any performance or other considerations of the order of the fields 
for an insert?
Are the following equivalent?  regardless of number of values inserting?
 regardless of size of the data being inserted?

INSERT INTO
​mytable(
wid1,cnt,
​dat,​
wid3,wid2) VALUES (?,?,?,?)
​ - VS - ​

INSERT INTO
​mytable(
wid1,wid2,wid3,cnt
​,dat​
) VALUES (?,?,?,?)


CREATE TABLE
​mytable
(
​id
 INTEGER PRIMARY KEY AUTOINCREMENT"
,
​wid1
 INTEGER REFERENCES
​othertable
(
​id
) ON DELETE CASCADE
,
​wid2
 INTEGER REFERENCES
othertable
(
​id
) ON DELETE CASCADE
,
​wid3
 INTEGER REFERENCES
othertable
(
​id
) ON DELETE CASCADE
,
​cnt
 INTEGER DEFAULT
​1
,dat TEXT
)

Jeff
___
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] Order of fields for insert

[Jeff Archer]

Sorry, that was just mistake in reducing the code for the email, please
ignore.

What do you mean "what matters is order of columns in table"?  or was that
just referring to the typo?


Jeff Archer
jeffarch...@gmail.com 


On Thu, Oct 6, 2016 at 9:52 AM, Clemens Ladisch  wrote:

> Jeff Archer wrote:
> > Are there any performance or other considerations of the order of the
> > fields for an insert?
>
> No; what matters is the order of columns in the table.
>
> > INSERT INTO ​mytable(wid1,cnt,​dat,​wid3,wid2) VALUES (?,?,?,?)
> ​> - VS - ​
> > INSERT INTO ​mytable(wid1,wid2,wid3,cnt​,dat​) VALUES (?,?,?,?)
>
> Both statements will result in exactly the same error.  ;-)
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Clemens Ladisch]

Jeff Archer wrote:
> Are there any performance or other considerations of the order of the
> fields for an insert?

No; what matters is the order of columns in the table.

> INSERT INTO ​mytable(wid1,cnt,​dat,​wid3,wid2) VALUES (?,?,?,?)
​> - VS - ​
> INSERT INTO ​mytable(wid1,wid2,wid3,cnt​,dat​) VALUES (?,?,?,?)

Both statements will result in exactly the same error.  ;-)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users