On Android, the ' batch ' operation on SQLite

Using Execsql for batch insertions

Today I learned how to perform an insert operation on SQLite more efficiently.

Note: The real machine test is used

We use Sqlitedatabase's Insert method and Execsql to experiment separately.

The first is the Insert method:

publicbooleaninsert(String table, ContentValues values) {    ifnull0) {           returnfalse;       }       db = helper.getWritableDatabase();       longnull, values);       return0//count >0 就说明插入了数据   }

Next is the test data:

Userdao Userdao= NewUserdao (Mcontext); Contentvalues values= NewContentvalues (); Values.Put (Tbusercolumn.PASSWORD,"Password"); Long time1=System.Currenttimemillis ();Log.ITAG,"-------->>> non-batched insert operation starts:" +TIME1); for (int i= 0; I<  +; I++) {values.Put (Tbusercolumn.USERNAME,"Demo_user" +i); Userdao.Insert (Tbusercolumn.table_name, values); } Long Time2=System.Currenttimemillis ();Log.ITAG,"-------->>> non-batched insert operation completed:" +TIME2);Log.ITAG,"-------->>> non-batched insert operation time:" +(time2-TIME1));List<Map<String, Object>> List =Userdao.Query"SELECT * from Tb_user",NULL);Log.ITAG,"The number of-------->>> execution data is:" + List.Size ());

Here is the result of the execution:
The database is empty at first, and if the data obtained is 1000, it means we're all plugged in.

Through this picture, you can know that inserting 1000 data, it takes 11.5 seconds, it is too time-consuming.

Then we'll use the Execsql method to test how long it takes to insert 1000 data.
Delete the database files before you test them.
The first is the simple encapsulation method

    publicvoidexecSQLByBatch(List<String> sqls) {        ifnull || sqls.isEmpty()) {            return;        }        try {            db = helper.getWritableDatabase();            db.beginTransaction();            for (String sql : sqls) {                db.execSQL(sql);            }            db.setTransactionSuccessful();        catch (Exception e) {            e.printStackTrace();        finally {            db.endTransaction();        }    }

Next is the test data

Long StartTime=System.Currenttimemillis ();Log.ITAG,"-------->>> Batch insert operation starts assembling data");List<String>Sqls= NewArrayList<>(); for (int i= 0; I<  +; I++) {StringSql= "INSERT into Tb_user (Username,password) VALUES ('" +I+ "_user ', ' Password" +I+ "')"; Sqls.Add (SQL); } Long Enttime=System.Currenttimemillis ();Log.ITAG,"-------->>> batch operations assembly data is completed and takes time:" +(Enttime-StartTime)); StartTime=System.Currenttimemillis ();Log.ITAG,"-------->>> batch operation started:" +StartTime); Userdao.Execsqlbybatch (SQLS); Enttime=System.Currenttimemillis ();Log.ITAG,"-------->>> batch operation completed:" +(Enttime-StartTime));List<Map<String, Object>> List =Userdao.Query"SELECT * from Tb_user",NULL);Log.ITAG,"-------->>> get Data number of bars:" + List.Size ());

Finally, the result graph is:

Using Execsql for bulk operations, inserting 1000 data is only used for 0.5 seconds, compared to the Insert method, the speed is more than a few times, so in the batch operation, it is advisable to use this method.

However, when testing this method, another try, insert the primary key already existing data, it will cause all the data can not be inserted, is the entire data.

It may be because of db.settransactionsuccessful (); When the transaction was last committed, it was checked to see if the execution of the SQL statement was inserted failed, and if it failed, the transaction was not committed. (Guess, find a time to test it)

On Android, the ' batch ' operation on SQLite