On 2017/08/10 1:19 PM, x wrote:
Thanks for the replies. I’m not sure I agree with Gunter and Ryan though. I’m 
thinking about this more from the gain in speed rather than saving space.

To clarify, I’m suggesting replacing a compound key (made up of several integer 
cols) with an integer primary key (which sqlite will use rather than the row 
id). I have done my homework on this so I’m familiar with Gunter’s points 
regarding ‘between’ and ‘high end bits’ but will the between on a single 
integer key not be faster than matching on the first m fields of an n compound 
key? If an index is needed on any non-high bit col an expression index would 
work just as fast for lookups (I suppose inserts would be slower). The savings 
on space would contribute to the speed as each disk read would contain more 

Ok, if you require ALL the packed records all the time, and will always access it by the primary value (the first of the packed values) and is very very sure you won't ever need expanding the value range, then you might actually get a speed gain from it.

Problem is, the gain will be minuscule, and the price is high. Lots of development time, loss of useful SQL aggregates and other functionality, possible future reworks... All of that for a very small speed gain? If you are wanting that, why not simply use a custom structure and avoid SQLite completely? The speed gain will actually be significant then, and you're going to lose the SQL-ness of it anyway, so that shouldn't matter.

A structured array mapped to a physical byte-stream will be several times faster than SQLite (or any other RDBMS for that matter). SQL as supported by the average RDBMS is only really helpful when you are looking for SET-type relational data handling or very large data (and your use case is specifically not for large data). Most RDBMSes have great optimizations for speeding up resolving of relational-type questions and their every-day-use advantages are legion, they are however without exception NOT faster than - NOR intended to be faster than - simple byte/structured array handling.

You might even find a synergy between using your own structured array together with an SQLite DB which only get accessed once you need more information than persists in the array itself - it's easy to make a pilot and test the speed gains. And please do that before investing the time to develop a fully fledged dual system.

Even forgetting about keys, if you packed say 8 columns into one int64 column 
would you not be saving a minimum of 7 bits?

No you won't, SQLite stores Integer much more efficiently. Unless you mean use ONLY the 64-bit index and not storing the values in separate fields in the DB at all, in which case yes, you will save a few bytes, possibly less than 7 though (I need to affirm the exact number, don't know off the top...).


sqlite-users mailing list

Reply via email to