Faster database table creation

Question

I have a table in my database that has 14 columns and 125 rows. When I launch my application to create the database it takes definitely too much time (in my opinion): I did 16 tries and these are the results.

===========================
 Average time:    12.715 s
 Minimum time:     8.486 s
 Maximum time:    21.159 s
===========================

This is how I create my table:

private static final String DATABASE_RECIPES_CREATE = "CREATE TABLE IF NOT EXISTS "
    + MetaData.TABLE_RECIPES + " ("
    + MetaData.KEY_ID + " integer primary key autoincrement, "
    + MetaData.KEY_NEW + " integer not null, "
    + MetaData.KEY_TITLE + " text not null, "
    + MetaData.KEY_INGREDIENTS + " text not null, "
    + MetaData.KEY_TIME + " text not null, "
    + MetaData.KEY_PEOPLE + " text not null, "
    + MetaData.KEY_WINE + " text, "
    + MetaData.KEY_SUGGESTIONS + " text, "
    + MetaData.KEY_DIRECTIONS + " text not null, "
    + MetaData.KEY_AUTHOR + " text, "
    + MetaData.KEY_DIFFICULTY + " text not null, "
    + MetaData.KEY_CATEGORY + " text not null, "
    + MetaData.KEY_VEGETARIAN + " integer, "
    + MetaData.KEY_IMAGE + " text)";

and this is how I add all the row values to my table:

public long addRecipe(int _id, int _new, String _title, String _ingredients, String _time, String _people, String _wine, String _suggestions, 
        String _directions, String _author, String _difficulty, String _category, int _vegetarian, String _image) 
{
    ContentValues initialValues = new ContentValues();

    initialValues.put(MetaData.KEY_ID, _id);
    initialValues.put(MetaData.KEY_NEW, _new);
    initialValues.put(MetaData.KEY_TITLE, _title);
    initialValues.put(MetaData.KEY_INGREDIENTS, _ingredients);
    initialValues.put(MetaData.KEY_TIME, _time);
    initialValues.put(MetaData.KEY_PEOPLE, _people);
    initialValues.put(MetaData.KEY_WINE, _wine);
    initialValues.put(MetaData.KEY_SUGGESTIONS, _suggestions);
    initialValues.put(MetaData.KEY_DIRECTIONS, _directions);
    initialValues.put(MetaData.KEY_AUTHOR, _author);
    initialValues.put(MetaData.KEY_DIFFICULTY, _difficulty);
    initialValues.put(MetaData.KEY_CATEGORY, _category);
    initialValues.put(MetaData.KEY_VEGETARIAN, _vegetarian);
    initialValues.put(MetaData.KEY_IMAGE, _image);

    return mDb.insert(MetaData.TABLE_RECIPES, null, initialValues);
}

Question:

Since my final table will contain at least 400 rows, is there any way to make its creation faster?

EDIT 1: I add one more question: for some columns types "text" or "int" is not necessary at all. Changing them to "tinytext", "smallint" and "tinyint" (where possible) would make any performance difference?

Answer 1

Create and fill db on your PC and put it in app's assets. All you need is to copy db file from assets to app's data folder when your app first launched.

Or you can use transactions to speedup insertions. If you have any indexes in db create them after all data inserted.

Answer 2

Faster database table creation

You don't need to create your db on PC. All what you need is to use TRANSACTION that rapidly increase your insertion speed.

I did a few tests and difference between insertion with and without transaction was significant:

• Insertion of 100 000 rows toook approximately 55,015 seconds with transaction against insertion without transaction that took much more than 6 minutes.

Also it's worth to mention that an usage of transaction is very good practise. Your dealing with database becomes much more efficient and what is main much more safe.

But now, here is a question.

Since you want to improve table's creating, you have probably a bigger number of rows in table. Now, is it good and efficient to store your db on internal storage?

Will be better to store database on external storage? Holds database sensitive data? If not, i should store database on SD card otherwise i should store db on internal storage for security issues / reasons.