If you change the page size after the database has been created and written to, yes, you need to a vacuum to have SQLite reorganize the database pages. Note that even this does not work if WAL is in use (if WAL is turned on, then you can't change the database page size after the database has been written to, even with a vacuum).
What you want to do, however, is set the page size right after you first open the database, before you issue your first create table statement. When you first open a new database, all you have a file descriptor with a 0-byte file. You want to issue your pragmas at this point, before you actually write to the database. The settings are then storage on the first database page (page 1), so you don't have to do a vacuum since the setting is stored with the database forever more. If you're building SQLite yourself, you can also specify the default page size, default number of cache pages, etc. via defines when you're building the library or DLL. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Sunday, May 29, 2011 9:22 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Is it possible to optimize the readperformanceofa C++ app using sqlite pragma journal_mode = wal On 30 May 2011, at 2:08am, Frank Chang wrote: > Thank you for your help with the PRAGMA page size question. Sometimes I see C++ programs which first issue the PRAGMA page_size = 4096. Then these C++ programs request a sqlite3_execute(Database,"VACUUM",callback,0,&Msg) which takes about 4 minutes to complete. > We wondering if the sqlite3_execute(Database,"VACUUM",callback,0,&Msg) is necessary to change the PRAGMA page_size=4096. I just looked it up: http://www.sqlite.org/lang_vacuum.html You're right: VACUUM allows you to change the page size of a database file. I didn't know that. Clever. Yes, it won't happen without the VACUUM. This rewrites the entire database file. Under normal circumstances the amount of time doesn't matter. You don't normally use VACUUM once you have your system up and running. 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
