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.Advertising
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 records.
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...).
Cheers, Ryan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
