Re: [sqlite] VACUUM and PRAGMA temp_store
Great :-) I guess that PRAGMA temp_store=MEMORY then does not add additional performance on Windows and I can safely let it to DEFAULT or FILE. This will avoid the excessive memory usage during VACUUM for my use case. Thanks. -- Mario ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM and PRAGMA temp_store
On Sun, Jun 23, 2013 at 5:06 AM, Mario M. Westphal wrote: > > When creating/opening a file in Windows, an application can mark this file > as "temporary" (flag: FILE_ATTRIBUTE_TEMPORARY) and Windows will try to > keep > it in memory if sufficient memory is available, avoiding all writes to the > medium. Does SQLite use this feature on Windows when it creates temporary > files? > Yes. It sets FILE_ATTRIBUTE_TEMPORARY, FILE_ATTRIBUTE_HIDDEN, and FILE_ATTRIBUTE_DELETE_ON_CLOSE. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VACUUM and PRAGMA temp_store
Hello List the SQLite databases I use on Windows can become fairly large (several GB). I just noticed that running a VACCUM on such a large database (where several of the tables are also real large) can cause excessive memory usage (more than 2.5 GB RAM in peak). I tracked this down to using "PRAGMA temp_store=MEMORY" when opening the database. When I change this to "PRAGMA temp_store=DEFAULT" or "PRAGMA temp_store=FILE" the VACUUM requires almost no RAM, even for large databases. Question: Can I change PRAGMA temp_store for an open database before I run the VACUUM? I would set it to FIILE before and back to MEMORY afterwards. Is PRAGMA temp_store useful at all when used on Windows? Or can one rely on the Windows built-in file system cache? When creating/opening a file in Windows, an application can mark this file as "temporary" (flag: FILE_ATTRIBUTE_TEMPORARY) and Windows will try to keep it in memory if sufficient memory is available, avoiding all writes to the medium. Does SQLite use this feature on Windows when it creates temporary files? Thanks for your support. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users