Most efficient way to INSERT SQLite data

Question

On first install, my app has to cache data it gets from the network, and insert rows into an SQLite database. I end up with around 700 inserts, which takes at least 15 seconds. Currently my logic is as follows (pseudo):

ScrollThroughAllObjects(){
    if(objectDoesNotExist){
        cacheObject();
    }
}

cacheObject(Object ob){
    ContentValues values = new ContentValues();
    values.put(KEY_ID, ob.getId);
    // add lots more values
    return mDb.insert(DATABASE_TABLE, null, values);
}

Is there a more efficient, quicker, way for me to handle this insert?

possible duplicate of [Android SQLite database: slow insertion](http://stackoverflow.com/questions/3501516/android-sqlite-database-slow-insertion) — WarrenFaith, Jun 05 '12 at 09:03

score 6 · Answer 1 · answered Jun 05 '12 at 09:01

6

Perform your record insertion using SQLite Transaction. It will amazingly speed up the entire process; because when you perform single insertion (one at a time) SQLite treats each insertion as a transaction and needs to create Journal-file for each execution (which takes time). However, when you bind all your add/edit/delete queries to a single transaction then only one Journal file is created throughout the end of transaction causing SQLite to execute queries rapidly.

answered Jun 05 '12 at 09:01

waqaslam

67,549
16
165
178

Great that took the whole thing down to less than 3 seconds, thanks. – androidneil Jun 05 '12 at 13:34
optimization does offer great effects :) – waqaslam Jun 05 '12 at 14:21

score 0 · Answer 2 · answered Jun 05 '12 at 09:02

0

Wrap the entire operation in a transaction. This alone will give you a huge speed increase, as well as giving you the benefits of transactional behaviour.

answered Jun 05 '12 at 09:02

Graham Borland

60,055
21
138
179

score 0 · Answer 3 · answered Jun 05 '12 at 09:10

0

You should use bulkInsert method. You can get the code from SQLiteContentProvider. Modify it for your need.

answered Jun 05 '12 at 09:10

Gaurav Agarwal

18,754
29
105
166

score 0 · Answer 4 · answered Jun 05 '12 at 10:59

Creating and connecting to a database

First import android.databse.sqlite.SQLiteDatabase into your application. Then use the openOrCreateDatabase() method to create or connect to a database. Sqlite documentation.

onCreate() – These is where we need to write create table statements. This is called when database is created. onUpgrade() – This method is called when database is upgraded like modifying the table structure, adding constraints to database etc.,

public class DatabaseHandler extends SQLiteOpenHelper {

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "contactsManager";

// Contacts table name
private static final String TABLE_CONTACTS = "contacts";

// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PH_NO = "phone_number";

public DatabaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_PH_NO + " TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

    // Create tables again
    onCreate(db);
}

Inserting records

Android comes with a series of classes that simplify database usage. Use a ContentValues instance to create a series of table field to data matchings that will be passed into an insert() method. Android has created similar methods for updating and deleting records.

The addContact() method accepts Contact object as parameter. We need to build ContentValues parameters using Contact object. Once we inserted data in database we need to close the database connection.

    // Adding new contact
public void addContact(Contact contact) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.getName()); // Contact Name
    values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone Number

    // Inserting Row
    db.insert(TABLE_CONTACTS, null, values);
    db.close(); // Closing database connection
}

See full article for this link.

Watch this video, which is simple define, how to create table and inserting data in to android.

Finally dont miss this, Android SQLite : Insert, Update, Delete and Display Data(Link).

Most efficient way to INSERT SQLite data

4 Answers4