Lets say I have a table with columns A, B, C, D, E, F, G, H, I in that order, and I need to select only columns A, C, F, I (it could be the case that the table has many more columns and I have to retrieve many more columns too).
My question is, would it make a difference (performance wise) if I keep the order of the columns to be retrieved in the projection in ascending column index number (e.g A, C, F, I) rather than retrieving them at a complete random order (e.g. F, A, I, C). And why?
I understand that sequential access is faster than random access, however none of the cases in my example is sequential so I'm not sure what the performance difference of these two projection orders would be.
Thank you.
Short answer: NO.
Long answer: it depends.
In general case, this question is impossible to answer without knowing which product you use.
Ordering of output columns should not matter.
In most row-based relational databases (including Microsoft, PostgreSQL and Oracle), ordering of output columns will make no visible difference. This is because row data is read from memory block-wise (in 8kB or 32kB chunks, for example). After reading into memory, processing is quite cheap.
Number of output columns can make a difference, especially in databases built with columnar (column-based) storage. Also with row-based storage this can matter (just because of in-memory processing cost and data transfer cost).
Please specify if you have particular database engine on your mind.