Insert 1,000,000 rows into sqlite3 database

Question

I want to insert 10,000,000 rows into a database, but it will take too long, for example.

eg. Now I'm trying with 2055 rows, and it takes 3 minutes to load this data into the database .. and this time too much for 2055 records.

The following is my method of inserting data into a database:

public void insert_database(Context context,String field1,String field2,String field3,String field4,String field5,String field6 ,String field7,String field8,String field9,String field10) { try { //RayAllen_Database.beginTransaction(); RayAllen_Database.execSQL(" insert or replace into "+ TableName_csv+" values( '"+field1+"' ,'"+field2+"','"+field3+"','"+field4+"','"+field5+"','"+field6+"','"+field7+"','"+field8+"','"+field9+"','"+field10+"');"); } catch(Exception e) { //Log.i("Database Exception", "Exception"); e.printStackTrace(); } } 

and in another class: Parsing Data: here I parse the csv file and when parsing:

try {

 CSVReader reader=new CSVReader(new FileReader(filename)); String [] nextLine; //create database obj.create_database(context); obj.OpenDatabase(context); //reader.readNext(); while ((nextLine=reader.readNext())!=null) { //here I am calling the insert_database function } } 

so here he parses the string one by one and calls the insert method to insert the record into the database.

But that is too much time. How can I improve the performance of this?

Answer 1

A quick approximate time, why you should do the right thing, and not "wrong." This has been tested on ICS 4.0.4, which has terrible INSERT performance.

First, just SQLiteOpenHelper , which creates a table with a UNIQUE for the column to cause conflicts now and then.

 class SimpleHelper extends SQLiteOpenHelper { // InsertHelpers are a really good idea - they format a prepared statement // for you automatically. InsertHelper mInsert; public SimpleHelper(Context context) { super(context, "tanika.db", null, 1); } @Override public void onOpen(SQLiteDatabase db) { super.onOpen(db); mInsert = new InsertHelper(db, "target"); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE target (\n" + "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n" + "val1 TEXT NOT NULL,\n" + "val2 TEXT NOT NULL,\n" + "val3 TEXT NOT NULL,\n" + // Let make one unique so we can get some juicy conflicts "val4 TEXT NOT NULL UNIQUE\n" + ")"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } } 

Complete with any old Activity we will add the following simple test method:

 long test(final int n) { long started = System.currentTimeMillis(); ContentValues values = new ContentValues(); for (int i = 0; i < n; i++) { values.clear(); // Every 20th insert, generate a conflict in val4 String val4 = String.valueOf(started + i); if (i % 20 == 0) { val4 = "conflict"; } values.put("val1", "Value1"); values.put("val2", "Value2"); values.put("val3", "Value3"); values.put("val4", val4); mHelper.mInsert.replace(values); } return System.currentTimeMillis() - started; } 

As you can see, this will cause a conflict every 20 INSERT or so. Calling InsertHelper#replace(..) forces the helper to use INSERT OR REPLACE conflicts.

Now run this test code with and without the transaction surrounding it.

 class Test1 extends AsyncTask<Integer, Void, Long> { @Override protected Long doInBackground(Integer... params) { return test(params[0]); } @Override protected void onPostExecute(Long result) { System.out.println(getClass().getSimpleName() + " finished in " + result + "ms"); } } class Test2 extends AsyncTask<Integer, Void, Long> { protected Long doInBackground(Integer... params) { SQLiteDatabase db = mHelper.getWritableDatabase(); db.beginTransaction(); long started = System.currentTimeMillis(); try { test(params[0]); db.setTransactionSuccessful(); } finally { db.endTransaction(); } return System.currentTimeMillis() - started; } @Override protected void onPostExecute(Long result) { System.out.println(getClass().getSimpleName() + " finished in " + result + "ms"); } } 

It all started as follows:

 @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); mHelper = new SimpleHelper(this); mHelper.getWritableDatabase(); // Forces the helper to initialize. new Test1().execute(2055); new Test2().execute(2055); } 

And the results? Without transaction INSERT take 41072ms . With transactions, they take 940 ms . In short, FFS, start using InsertHelper and transactions.

Answer 2

You can populate your database with stand-alone tools and then import it when you install the package. You can either save the database on an external SD card, or in the folder with the resources of your application.

Here is how I do it:

• Copy the application database to a local folder using Android Debuger Bridge (adb), for example: adb pull /data/data/<your application provider>/databases/yourdatbase.db C:/users/databases/yourdatbase.db .

• Connect to the SQLites C:/users/databases/yourdatbase.db database C:/users/databases/yourdatbase.db using your favorite GUI / CLI tool and fill out your collection of 1,000,000 records.

• Copy your populated database into the asset folder for Android application development.

• Now uninstall the application from the device to make sure that the installation does not create the database for the first time.

• Change your SQLiteHepler class to check if the database exists and if it exists, it uses it. If the database does not exist, the Helper copies it from your folder with your 1,000,000 entries. Here is how I did it:

   public class MyDatabaseHelper extends SQLiteOpenHelper { /* Other SQLiteOpenHelper declarations here ... */ private static final String DATABASE_NAME = "application.db"; private static final String DB_PATH = "/data/data/" + context.getPackageName() + "/databases/"; /* Your SQLiteOpenHelper functions/procedures here ... */ public boolean isDataBaseExist() { File dbFile = new File(DB_PATH + DATABASE_NAME); return dbFile.exists(); } public void copyDataBase(Context context) throws IOException { this.getReadableDatabase(); InputStream inFile = context.getResources().getAssets().open(DATABASE_NAME); // Path to the just created empty db String outFileName = DB_PATH + DATABASE_NAME; OutputStream outFile = new FileOutputStream(outFileName); // transfer bytes from the inputfile to the outputfile byte[] buffer = new byte[1024]; int length; while ((length = inFile.read(buffer)) > 0) { outFile.write(buffer, 0, length); } // Close the streams outFile.flush(); outFile.close(); inFile.close(); } 

This database will be supplemented by your application and at the first start all the data will be there. There may be a simpler method, but I hope this helps someone.

Answer 3

Speeding up insert operations in sqlite goes through a similar case and shows how to use transactions to optimize insert.