Fastest and most efficient way to pre-populate database in Android
android, database, sql, sqlite
Solution
I suggest the following:
- Wrap all of your `INSERT` logic into a transaction (`BEGIN... COMMIT`, or via the beginTransaction()... endTransaction() APIs)
- As already suggested, utilize the bind APIs and recycle objects.
- Don't create any indexes until after this bulk insert is complete.
Additionally take a look at Faster bulk inserts in sqlite3?
Problem
If you want to pre-populate a database (SQLite) in Android, this is not that easy as one might think. So I found this tutorial which is often referenced here on Stack Overflow as well. But I don't really like that way of pre-populating the database since you take the control from the database handler and create the files yourself. I would prefer to not touch the file system and let the database handler do everything on its own. So what I thought one could do is create the database in the database handler's onCreate() as usual but then load a file (.sql) from /assets which contains the statements to fill in the values: ``` INSERT INTO testTable (name, pet) VALUES ('Mike', 'Tiger'); INSERT INTO testTable (name, pet) VALUES ('Tom', 'Cat'); ... ``` But calling execSQL() in the handler's onCreate() doesn't really work. It seems that the /assets file must not have more than 1MB and the execSQL() only executes the first statement (Mike - Tiger). What would you do do pre-populate the database?