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
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
