> SQLite is about equally space-efficient for pretty-much everything you do.
Yeah, I figured it probably would be. I'm generating my database in one shot off-device at the moment and it's all sequential, so there's no chance of anything being out of order. Even the vacuum only cleaned up 250KB out of 41.5MB. The page_size find was a little bit expected because smaller chunks will go through the Deflate algorithm better but I guess I was hoping maybe someone had tried to do this before and there is some magic combination of pragmas which perfectly align to what Deflate expects (unusual page_size, perhaps?). Anyway, I'll grab an analyser and see what's what. Thanks Simon. L On 5 March 2012 21:02, Simon Slavin <slav...@bigfraud.org> wrote: > > On 5 Mar 2012, at 12:16pm, Larry Knibb <larry.kn...@gmail.com> wrote: > >> Thanks for the quick reply, Simon. I was rather hoping there might be >> some efficiency tweaks I can do to the db file (e.g. more explicit >> creation statements, or postprocessing) instead of getting into custom >> sqlite builds. [snip] > > SQLite is about equally space-efficient for pretty-much everything you do. I > have seen some reports that creating tables by INSERTing rows in primary key > order makes a database smaller, but I don't remember any of the experts > posting anything definite about it one way or the other. > > Something you might try is to build your database file however you want, then > use the shell tool's '.dump' and '.read' commands to make another database > file out of it. Among other things I believe this does INSERT rows in > primary key order. If the two files are radically different in size then > there's something strange going on and, of course, you've found a way to make > your database smaller. > >> Can I limit possible string lengths for the TEXT fields or is it >> already using VARCHAR internally? > > Affinities of VARCHAR are interpreted as TEXT, with length limits being > ignored. All strings in SQLite are variable-length strings. See > > <http://sqlite.org/datatype3.html> > >> If so, any idea where all the space >> is being used? Can I profile the tables to learn how much data they >> hold individually or the efficiency of that storage? > > Take a look on > > <http://sqlite.org/download.html> > > to see if there's a build of 'sqlite-analyzer' for your platform. This is a > good way of getting a ton of information about your database file. You might > find something weird. > > Generally, in cases where it /is/ possible to save a lot of space in a > database, it's because people have made indexes which are unnecessary, or > unnecessarily long, or duplicates of existing indexes. If you understand SQL > well and made your indexes by examining your INSERT and UPDATE commands, then > this probably isn't your problem. > > Simon. > _______________________________________________ > 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
