CODEWITHSUNDEEP
androidmysqldatabase
Mangusto
Mangusto

Reputation: 1505

Faster database table creation

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?

Upvotes: 3

Views: 311

Answers (5)

Makers_F
Makers_F

Reputation: 3073

As I understand your database is populated when the app is started, and then you don't need to add the recipes any more, am I correct?

If you are using a SQLiteOpenHelper as the official documentation suggests, you could call the function that inserts all the data into your database from the onCreate, onUpdate and onDownGrade methods.

As you can see here, these methods are called in between a transaction, so your effort in refactoring your code would just be as hard as moving a method call from a class to another.

       db.beginTransaction();
       try {
            if (version == 0) {
                onCreate(db);
            } else {
                if (version > mNewVersion) {
                    onDowngrade(db, version, mNewVersion);
                } else {
                    onUpgrade(db, version, mNewVersion);
                }
            }
            db.setVersion(mNewVersion);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }

In case you are not, i highly suggest you to use one, it will make the upgrading of your database between the different versions a lot easier:

Here the documentation

Additional references:

Even if not directly related to android, this is an interesting article that talks about the optimizations for fast insertions. http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/

Upvotes: 0

Leonidos
Leonidos

Reputation: 10518

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.

Upvotes: 2

Simon Dorociak
Simon Dorociak

Reputation: 33515

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.

Upvotes: 1

Hemantvc
Hemantvc

Reputation: 2119

Create sqlite data base using pc and put that database run time in android data folder. use below codde for copy database .

I am use this code ,I am creating database in my pc and that database put into asset folder.

and that database copy run-time and put into data folder in android.

copy data base in android data folderenter link description here

Upvotes: 0

Shrikant Ballal
Shrikant Ballal

Reputation: 7087

You can go for InsertHelper in android.

By using this class you can actually finish insertion operation in less than one second.

:O

To know how to implement this please see this: http://orange-coding.net/2012/04/13/inserthelperandroid/

http://www.mysamplecode.com/2011/10/android-sqlite-bulk-insert-update.html

Upvotes: 0

Related Questions