The "anyone" that does this already is called "SQLite".

It doesn't amalgamate the INT fields as such, but it stores INTEGER values in only as much bits as is needed, so it achieves the goal of being more memory-efficient. So a 0, 1 or 2 takes up much less space than say a 786587626 or other high-value integer.

It saves a lot more than is immediately evident, because it turns out in *most* cases, *most* tables with INT columns/values have *most* values in the low-end of the Integer value spectrum. Especially for Indexes, Key relations and the like.

The way you refer to, is simply a form of compression, and it has the advantage of saving space, but the common weakness of all compression algorithms - It takes CPU cycles, i.e. Work. You asked about "speed" gain - there is NO speed gain, there might be a bit of space gain, but there is a Speed loss. Perhaps you think that because a processor is natively 64bit (these days anyway) it will transfer 64bit integers just as fast as any lower-bitted integer, and you are correct about that, but if it involves ANY cycles to build that Integer (and it will involve MANY), the gain turns into a loss quickly.

Remember: In the CPU registers, something that looks very lean to your eye, like left-shifting 2 bytes packing it into a 16-bit INT [ex: i16 = (b1 << 8) + b2] actually expands to many low-level CPU operations involving moving the larger int into a register/adder, shifting it, adding, etc. (Repeat-extrapolate for larger INTs.) As you can see, simply speeding the 8 bytes as-is into cpu/memory/device consecutively, using only an 1/8th of the highway, is much faster than first building the 64 bit INT and then sending that. The loss effectively repeats the day you read it back and needs to decipher.

Verdict: Don't do it. Ever.


On 2017/08/10 9:44 AM, x wrote:
As in cramming numerous integer columns into a 64 bit integer.

Does anyone do this?

Is the speed gain worth the additional confusion of extracting the columns from 
the key?

How is it best accomplished (virtual table maybe)?
sqlite-users mailing list

sqlite-users mailing list

Reply via email to