Hi Simon,
As always thanks for your prompt reply. My answers inline.
On 12/7/2013 1:11 PM, Simon Slavin wrote:
On 12 Jul 2013, at 5:19am, Mohit Sindhwani <m...@onghu.com> wrote:
We could try to renumber the IDs so that all the IDs are in sequence, but that
is not the easiest thing to do. Does insertion order have an impact on how the
data is stored? If we inserted the most frequently accessed records first,
would it mean that they would be closer to each other in the table and the
index and therefore, we could get a better performance?
There are some 'yes' answers here, but some of them depend on many things about
your setup and how the database file was generated. Generally speaking, the
fastest way to make such a file would be to write all the rows in primary key
order, assuming that your primary key was an AUTOINCREMENTed INTEGER.
Our primary key is not an AUTOINCREMENTED integer, it is a unique
numeric ID for the record. The insertion during preparation is done
using a .import for the file, ordered in increasing ID.
One simple thing you might try is running VACUUM on the database file. Given
your setup, this may be an overnight run. And it can temporarily require twice
as much spare disk space as your database file occupies. It will result in the
index of each table being very efficiently packed.
VACUUM is done in our production process before we compress and encrypt
the database. So, it should be efficiently packed.
But I'm not familiar with CEROD in real life. It may be that the process of
building the packed database inherently does a VACUUM. If not, it would be
best to do the VACUUM immediately before packing the database.
Yes, that is what we do.
Best Regards,
Mohit.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
