Eduardo, I need to do range searches based on id's so R-tree doesn't help, but your suggestion of attaching database sounds useful. I will also look at the size of the page and post my results.
On Wed, Jun 10, 2015 at 7:19 PM Eduardo Morras <emorrasg at yahoo.es> wrote: > On Wed, 10 Jun 2015 14:13:29 +0000 > Igor Stassiy <istassiy at gmail.com> wrote: > > > Thanks for all your replies. Please let me clarify: > > > > I need to do certain range operations efficiently and I prefer SQL > > functionality, plus SQLite gives a cross platform file storage > > format. The table having 3.2 megabytes is just an example. I am > > storing much more (orders of 10^5 of points) however the effect is > > similar. > > > > When I store several tables side by side the effect seems to be worse > > (I assume SQLite leaves the possibility for inserts and hence needs > > to have some space to do them efficiently, please correctly if I am > > wrong). SQLite forums suggest to store tables in separate files to > > avoid vacuum space in between the tables, are there any other methods > > to save space? > > Have you look at R-Tree extension? https://www.sqlite.org/rtree.html > > It creates a table and indexs to store and query latitude and longitude > efficiently. > > Or spatialite? https://en.wikipedia.org/wiki/SpatiaLite > http://www.gaia-gis.it/gaia-sins/ > > It's opensource under MPL. > > A page is dedicated only to one thing, table A, table B, internal sqlite3 > data, index C, whatever... A page can't store data from one table and data > from another table or index. This means that if your page size (default > 1024bytes) is not defined wise enough (f.ex. 64KB=65536byes) and your > tables uses a few bytes (say 128bytes), the rest of the page is empty > (65536-128=65408 bytes) for future table grows. There are page header and > tail with data you must count too, so it's a bit less. Note that you want > to store a big table with data, and the space lost on unwise page size is > minimal. > > You can have empty pages inside the database because you deleted data or > drop an index or a table, but in your static db, it shouldn't happen. In > any case, a vacuum and analyze solve the issue. > > I use the trick to store tables in separate db files and attach them to > main db for speed on mechanical hard disks, because those files on > different disks maximize the iops. An autovacumm and proper db maintenance > should minimize the vacuum space between tables. > > HTH > > --- --- > Eduardo Morras <emorrasg at yahoo.es> > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
