Thanks Scott, It is much better without the indexes. The uncompressed db file after dropping the indexes and vacuuming is 23MB compared to 43MB with indexes. Deflate cuts the size down to 10MB. The LZMA archive is less than 7MB - more or less the same as the Deflated SQL. I really need an LZMA library for Windows Phone - maybe that's my next project ;)
Anyway, only rebuilding the indexes seems like a good starting point for writing to the (previously read-only) database on-device, so I'll start there. Thanks for the suggestion. L On 6 March 2012 01:44, Scott Hess <sh...@google.com> wrote: > Note that importing the .dump file does effectively strip the indices > and re-create them. In fact, there's a good chance that if you drop > the indices, VACUUM, and then compress the database file, you'll find > that it comes close to what you get with the .dump-then-compress > values. > > -scott > > > On Mon, Mar 5, 2012 at 5:14 AM, Larry Knibb <larry.kn...@gmail.com> wrote: >> Thanks Mario. I did think about stripping the indexes to cut down the >> size. Recreating them on-device wouldn't be difficult (just >> time-consuming) but offset against the smaller download it's possibly >> worth it... although if I'm into partial on-device db creation then I >> should really do some timing to see whether importing the whole .dump >> file on the device is really that bad. Anyway, cheers for the advice. >> >> L >> >> On 5 March 2012 21:06, Mario Becroft <m...@becroft.co.nz> wrote: >>> I don't know whether this is practical in your application, but if you >>> want to reduce redundancy in the database file when downloading, the >>> obvious thing is to leave off the indexes, and generate them once the >>> file is downloaded onto the device. This way you'll have to download >>> only the actual data set, and this combined with compression should be >>> about as small as you can go without using domain-specific data >>> compression or reformulating the data model. >>> >>> This is similar to loading from DML statements, which you don't want to >>> do, but executing only the CREATE INDEX statements after downloading >>> might be more acceptable than loading the entire data set. >>> >>> -- >>> Mario Becroft <m...@becroft.co.nz> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
