SQLite Forum

Does the column order in an insert statement impact the insert speed?
Login
> Will the order of the columns in the insert statement have any impact on the insert speed? (Maybe based on how the SQLite code is written it will be faster to have the columns in the same order as they are defined in the table definition?)

No.  This can make no possible difference.  Either the order of the fields is the default or it is specified by the INSERT statement -- in either case the order is known at prepare time.  It is also known at prepare time what a "row" to go into the "table" looks like.  So, no matter what, the prepare process takes the data that you provide in the order that you provide and stores it where it belongs.  It makes no difference the ordering of the data items since the ordering is not dynamic -- it is determined for that statement forever when the statement is prepared and does not and cannot change for that statement after that instant.

If you want a statement with a different ordering, you have to prepare that other statement which will always and forever deal only with the specific ordering it specifies and no others.

Notwithstanding, however, the total length of the record does matter.  If the single record will fit on one page, then only one page needs to be read/written when it is accessed.  However, if it does not fit on a page, then part of it must reside on an "overflow page" and when the record is read/written/updated then *all* the pages including the overflow pages must be read and written.

If 50% of the records occupy two pages (have a one page overflow) then "accessing" N records requires "accessing" 1.5N pages which will take *at least* half-as-long-again as accessing all N records where there was no overflow.