Space savings will depend very much on what other data is in the table. If you have a 4096 byte page size and with an average record size of 1000 bytes then saving 7 bytes for each of the 4 records wont free up enough space to fit a new record into that page. So savings in this scenario will effectively be nil.
If on the otherhand the average record is 100 bytes you may well fit more records into the page, conversely changing the page size to 64K would also reduce the number of reads. I suspect that biggest time savings may be gained by reducing disk I/O. Better advice could possibly be given if we know the full table schema including typical sizes for data in any fields/ Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 10 August 2017 at 14:13, R Smith <rsm...@rsweb.co.za> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
