Have Geek Brain

SQLite - preallocate database size

Is there a way to preallocate my SQLite database to a specific size? I am currently adding and deleting multiple entries and would like to avoid this overhead during creation.

+1


a source to share


2 answers


There is a hack - insert a lot of data into the database until the size of the database is what you want, then delete the data. This works because:

"When an object (table, index, or trigger) is removed from the database, it leaves a blank space. This blank space will be reused and new information about the time the database is added. But at the same time, the database file may be larger than strictly necessary." ...



Naturally, this is not the most reliable method. (Also, you will need to make sure auto_vacuum is disabled for this to work). You can read more here - http://www.sqlite.org/lang_vacuum.html

+2


a source


The fastest way to do this is with the zero_blob function:

Example:

Y:> sqlite3 large.sqlite
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with ";"
sqlite> create table large (a) ;
sqlite> insert into large values ​​(zeroblob (1024 * 1024));
sqlite> large drop table;
sqlite> .q

Y:> dir large.sqlite
 The volume in the driver Y is personal
 Bulk serial number 365D-6110

Directory Y: \



01/27/2011 12:10 PM 1,054,720 large.sqlite

Note: As Kyle correctly points out in his comment:

There is a limit to how large each blob can be, so you may need to insert multiple blocks if you expect your database to be larger than ~ 1 GB.

+5


a source






More articles:


All Articles