Hi, Im Auftrag von uncle.f
> Hello all, > > I have a process that runs 24/7 and permanently inserts data into an SQLite > database: > > 1) Create new database at midnight, keep it open for writing for the next > 24 hours > 2) There are no deletions and not even reads during the database creation > 3) I use only bulk inserts wrapped in a transaction (for each minute of > data) that may contain a few thousands of rows in every transaction. > 4) My journal mode is MEMORY. > 5) Once the insertion process is done with I build several indices and close > the database > 6) After that the database file is moved over the network to a storage device > > The database will only be used again for reading and will remain unmodified > forever. > > Each database is fairly large (3-5 GB) and considering it will never be > modified again I would like to take all possible measures to ensure that the > file size / fragmentation / data access times are all as low as possible. > > So my question is about how to ensure most efficient data allocation for such > scenario. I thought of several options: > > 1) VACUUM before creating indices, then create indices, then move database > off to storage > 2) Create indices, then VACUUM, then move off to storage > 3) Create indices, move to storage, VACUUM when already on storage (using > SQLite process running locally on storage device) > > ... or any other sequence of those 3 steps (vacuum, indexing, moving to > storage) Did you consider running ANALYZE, too? That will populate the statistics which the query planner can use to gain optimal index usage. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
