database insert performance PHP prepared statement sqlite transaction

Android SQLite Transaction Example with INSERT Prepared Statement

At present I’m going to share an Android SQLite transaction example that I think about as one of the crucial useful check I made with Android SQLite. I’m really excited to share this since it helped me quite a bit and perhaps it could assist some more individuals as nicely.

This publish will cowl the next contents:

1.0 The Back Story
2.0 Insert Velocity Drawback
three.zero Android SQLite Transaction Example Supply Code
4.zero Software Code Output
5.0 Different Ideas On Enhancing Insert Velocity

1.zero The Again Story

Lately, my app was required to download 30,000 data throughout sync. I feel that’s numerous knowledge for a telephone app, however that’s the best way our app is.

The info have been from a URL with knowledge in JSON format. Our Android app has to read, parse and store the info on the system SQLite database.

30,000 data in a single URL load shouldn’t be advisable, we did several checks. I tried to parse it but failed, memory leaks happen, typically it was an out of reminiscence error. So I attempted some more check till I discovered the right variety of data per URL. 7,000 data and our app was capable of read and parse it all. But to be safer, I made it to 5,000 data per page.

We needed to paginate the obtain, so in our case, we had 6 pages. 6 pages x 5,000 data = 30,000. So yeah, it was very efficient. All data have been downloaded and inserted to the gadget SQLite database.

2.0 Insert Velocity Drawback

However before we have been capable of effectively insert the data to the database, we run into the problem of “insert speed”. The standard insert command in Android is sluggish, so we had to use a transaction and ready assertion.

In our case, we use INSERT OR REPLACE INTO on the insert question since we need to update a row if it already exists, based mostly on the trigger (INDEX) created.

In the event you’re utilizing INSERT OR REPLACE INTO command, you must create a trigger. This SQL trigger is executed after the table has been created (see DatabaseHandler.java under)

Another essential think about rushing up your insert is using prepared statements.

3.zero Android SQLite Transaction Example Supply Code

Our supply code may have three fundamental information, the MainActivity.java, DatabaseHandler.java and activity_main.xml. I made the code as simple as potential for this example to be clear and straightforward to comply with.

DOWNLOAD SOURCE CODE

MainActivity.java – our program’s first run, it additionally accommodates the AsyncTask that can be executed when the consumer clicks a button.

package deal com.example.androidsqlitetransaction;

import android.os.AsyncTask;
import android.os.Bundle;
import android.app.Activity;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;

public class MainActivity extends Exercise

remaining String TAG = “MainActivity.java”;
EditText editTextRecordNum;
TextView tvStatus;

@Override
protected void onCreate(Bundle savedInstanceState)

super.onCreate(savedInstanceState);
setContentView(R.format.activity_main);

View.OnClickListener handler = new View.OnClickListener()
public void onClick(View v)

change (v.getId())

case R.id.buttonNormalInsert:
new AsyncInsertData(“normal”).execute();
break;
case R.id.buttonFastInsert:
new AsyncInsertData(“fast”).execute();
break;

;

// EditText for getting into desired variety of data to be inserted
editTextRecordNum = (EditText) findViewById(R.id.editTextRecordNum);

// Button for normal and quick insert
findViewById(R.id.buttonNormalInsert).setOnClickListener(handler);
findViewById(R.id.buttonFastInsert).setOnClickListener(handler);

// status TextView
tvStatus = (TextView) findViewById(R.id.textViewStatus);

// we used AsyncTask so it gained’t block the UI thread throughout inserts.
class AsyncInsertData extends AsyncTask

DatabaseHandler databaseHandler;
String sort;
long timeElapsed;

protected AsyncInsertData(String sort)
this.sort = sort;
this.databaseHandler = new DatabaseHandler(MainActivity.this);

// @sort – might be ‘regular’ or ‘fast’
@Override
protected void onPreExecute()
tremendous.onPreExecute();
tvStatus.setText(“Inserting ” + editTextRecordNum.getText() + ” data…”);

@Override
protected String doInBackground(String… aurl)

attempt

// get number of data to be inserted
int insertCount = Integer.parseInt(editTextRecordNum.getText().toString());

// empty the desk
databaseHandler.deleteRecords();

// maintain monitor of execution time
long lStartTime = System.nanoTime();

if (sort.equals(“normal”))
databaseHandler.insertNormal(insertCount);
else
databaseHandler.insertFast(insertCount);

// execution finised
lengthy lEndTime = System.nanoTime();

// display execution time
timeElapsed = lEndTime – lStartTime;

catch (Exception e)
e.printStackTrace();

return null;

protected void onPostExecute(String unused)
tvStatus.setText(“Done inserting ” + databaseHandler.countRecords() + ” data. Time elapsed: ” + timeElapsed / 1000000 + ” ms.”);

DatabaseHandler.java – handles the database operations comparable to desk creation, emptying the database, counting database data and the inserting our knowledge using a loop.

package deal com.instance.androidsqlitetransaction;

import android.content material.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper

// for our logs
public static ultimate String TAG = “DatabaseHandler.java”;

// database model
personal static last int DATABASE_VERSION = 7;

// database identify
protected static last String DATABASE_NAME = “NinjaDatabase2”;

// table particulars
public String tableName = “locations”;
public String fieldObjectId = “id”;
public String fieldObjectName = “name”;
public String fieldObjectDescription = “description”;

// constructor
public DatabaseHandler(Context context)
tremendous(context, DATABASE_NAME, null, DATABASE_VERSION);

// creating table
@Override
public void onCreate(SQLiteDatabase db)

String sql = “”;

sql += “CREATE TABLE ” + tableName;
sql += ” ( “;
sql += fieldObjectId + ” INTEGER PRIMARY KEY AUTOINCREMENT, “;
sql += fieldObjectName + ” TEXT, “;
sql += fieldObjectDescription + ” TEXT “;
sql += ” ) “;

db.execSQL(sql);

// create the index for our INSERT OR REPLACE INTO statement.
// this acts as the WHERE identify=”identify input” AND description=”description input”
// if that WHERE clause is true, I mean, it finds the identical identify and outline within the database,
// it is going to be REPLACEd.
// ELSE, what’s in the database will remain and the enter might be INSERTed (new report)
String INDEX = “CREATE UNIQUE INDEX locations_index ON ”
+ tableName + ” (identify, description)”;

db.execSQL(INDEX);

// When upgrading the database, it’s going to drop the current table and recreate.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

String sql = “DROP TABLE IF EXISTS ” + tableName;
db.execSQL(sql);

onCreate(db);

// insert knowledge utilizing transaction and ready statement
public void insertFast(int insertCount)

// you need to use INSERT only
String sql = “INSERT OR REPLACE INTO ” + tableName + ” ( identify, description ) VALUES ( ?, ? )”;

SQLiteDatabase db = this.getWritableDatabase();

/*
* In response to the docs http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html
* Writers ought to use beginTransactionNonExclusive() or beginTransactionWithListenerNonExclusive(SQLiteTransactionListener)
* to start out a transaction. Non-exclusive mode permits database file to be in readable by different threads executing queries.
*/
db.beginTransactionNonExclusive();
// db.beginTransaction();

SQLiteStatement stmt = db.compileStatement(sql);

for(int x=1; x<=insertCount; x++) stmt.bindString(1, "Name # " + x); stmt.bindString(2, "Description # " + x); stmt.execute(); stmt.clearBindings(); db.setTransactionSuccessful(); db.endTransaction(); db.close(); // inserts the document with out using transaction and prepare assertion public void insertNormal(int insertCount) attempt SQLiteDatabase db = this.getWritableDatabase(); for(int x=1; x<=insertCount; x++) ContentValues values = new ContentValues(); values.put(fieldObjectName, "Name # " + x); values.put(fieldObjectDescription, "Description # " + x); db.insert(tableName, null, values); db.shut(); catch(Exception e) e.printStackTrace(); // deletes all data public void deleteRecords() SQLiteDatabase db = this.getWritableDatabase(); db.execSQL("delete from "+ tableName); db.shut(); // rely data public int countRecords() SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery("SELECT count(*) from " + tableName, null); cursor.moveToFirst(); int recCount = cursor.getInt(0); cursor.close(); db.close(); return recCount;

activity_main.xml – the format so we will enter the desired variety of data to be inserted, choose whether we would like it to be a ‘normal’ or ‘fast’ insert, and the status of the operation.