Optimize Multiple Insert SQLite Coming From MySQL

Question

I'm currently developing an app that requires sending and receiving of data from android studio going to MySQL and then data coming from MySQL will be saved from the SQLite. I need advice on how to make the process faster. Right now I'm inserting 80,000 + rows of data coming from MySQL and then saving it to SQLite and that process lasts around 25-30 minutes. The sending/recieving of data will happen once the button is clicked.

This is my PHP file

rowItem.php

<?php

require "init.php";

$serial = $_POST['mySerial'];

$sql = "select ITEMCODE, DESCRIPTION, BRAND from items where SERIAL_NO = '" .$serial. "'";
$result = mysqli_query($con, $sql);

$data =array();

while($row = mysqli_fetch_array($result)) {
    $row['ITEMCODE'] = mb_convert_encoding($row['ITEMCODE'], 'UTF-8', 'UTF-8');
    $row['DESCRIPTION'] = mb_convert_encoding($row['DESCRIPTION'], 'UTF-8', 'UTF-8');
    $row['BRAND'] = mb_convert_encoding($row['BRAND'], 'UTF-8', 'UTF-8');
    array_push($data, array('ITEMCODE' => $row['ITEMCODE'], 'DESCRIPTION' => $row['DESCRIPTION'], 'BRAND' => $row['BRAND']));
}

$json = json_encode(array("allItems"=>$data));
echo $json;

?>

This are my Java codes

DatabaseOperations.java

public class DatabaseOperations extends SQLiteOpenHelper {
        public static final int dbVersion = 1;

        public String CREATE_ITEMS_TABLE = "CREATE TABLE " + TableData.TableInfo.TB_ITEMS +
                " (" + TableData.TableInfo.COL_ITEMS_ITEMCODE + " VARCHAR(20) PRIMARY KEY NOT NULL, " +
                TableData.TableInfo.COL_ITEMS_DESCRIPTION + " VARCHAR(50), " +
                TableData.TableInfo.COL_ITEMS_BRAND + " VARCHAR(10), " +
                TableData.TableInfo.COL_ITEMS_BARCODE + " VARCHAR(20));";

        public DatabaseOperations(Context context) {
            super(context, TableData.TableInfo.DB_NAME, null, dbVersion);
        }       

        @Override
        public void onCreate(SQLiteDatabase sdb) {
            sdb.execSQL(CREATE_ITEMS_TABLE);
        }

        public void insertItems (DatabaseOperations dop,
                                 String itemCode, String brand, String desc) {
            SQLiteDatabase sq = dop.getWritableDatabase();
            sq.beginTransaction();
            try {
                ContentValues cv = new ContentValues();
                cv.put(TableData.TableInfo.COL_ITEMS_ITEMCODE, itemCode);
                cv.put(TableData.TableInfo.COL_ITEMS_BRAND, brand);
                cv.put(TableData.TableInfo.COL_ITEMS_DESCRIPTION, desc);
                sq.insert(TableData.TableInfo.TB_ITEMS, null, cv);
                sq.setTransactionSuccessful();
            }
            catch (Exception e) {

            }
            finally {
                sq.endTransaction();
                sq.close();
            }
        }
    }

MainScreen.java

public class MainScreen extends AppCompatActivity {
    Context ctx = this;
    Button btnSync;

    String rowItemURL = "http://192.168.100.118:81/rowItem.php";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main_screen);

        btnSync = findViewById(R.id.btnSync);
        btnSync.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                StringRequest itemImeiRequest = new StringRequest(Request.Method.POST, rowItemURL,
                    new Response.Listener<String>() {
                        @Override
                        public void onResponse(String response) {
                            try {
                                JSONObject jsonObject;
                                jsonObject = new JSONObject(response);
                                JSONArray itemArray = jsonObject.getJSONArray("allItems");

                                for (int i = 0; i < itemArray.length(); i++) {
                                    itemCode = itemArray.getJSONObject(i).getString("ITEMCODE");
                                    itemBrand = itemArray.getJSONObject(i).getString("BRAND");
                                    itemDesc = itemArray.getJSONObject(i).getString("DESCRIPTION");

                                    DatabaseOperations dop = new DatabaseOperations(ctx);
                                    dop.insertItems(dop, itemCode, itemBrand, itemDesc);
                                }

                                Toast.makeText(ctx, "SYNC ITEMS COMPLETED!", Toast.LENGTH_LONG).show();
                                lblDebug.setText("SUCCESS!");

                            }
                            catch (JSONException e) {
                                Toast.makeText(ctx, e.getMessage(), Toast.LENGTH_LONG).show();
                                lblDebug.setText(e.getMessage() + "500");
                            }
                        }
                    }, new Response.ErrorListener() {
                @Override
                public void onErrorResponse(VolleyError error) {
                    Toast.makeText(ctx, error.getMessage(), Toast.LENGTH_LONG).show();
                    lblDebug.setText(error.getMessage() + "511");
                }
            }) {
                @Override
                protected Map<String, String> getParams() throws AuthFailureError {
                    SharedPreferences sharedPreferencesIMEI = getSharedPreferences(IMEI_PREF, MODE_PRIVATE);
                    myIMEI = sharedPreferencesIMEI.getString(TEXT5, "");

                    Map<String,String> params = new HashMap<>();
                    params.put("mySerial", myIMEI);
                    return params;
                }
            };
            MySingleton.getInstance(MainScreen.this).addToRequestQue(itemImeiRequest);
            }
        }
    }
}

MySingleton.java

public class MySingleton {

    private static MySingleton mInstance;
    private RequestQueue requestQueue;
    private static Context mCtx;

    private  MySingleton(Context context) {
        mCtx = context;
        requestQueue = getRequestQueue();
    }

    public  RequestQueue getRequestQueue(){
        if(requestQueue==null) {
            requestQueue = Volley.newRequestQueue(mCtx.getApplicationContext());
        }
        return  requestQueue;
    }

    public static  synchronized  MySingleton getInstance(Context context) {
        if(mInstance==null) {
            mInstance = new MySingleton(context);
        }
        return  mInstance;
    }

    public<T> void  addToRequestQue(Request<T> request) {
        requestQueue.add(request);
    }
}

Answer 1

just to optimize the PHP part

<?php
$con->set_charset("utf8");

$sql = "select ITEMCODE, DESCRIPTION, BRAND from items where SERIAL_NO = ?";
$stmt = $con->prepare($sql);
$stmt->bind_param("s", $_POST['mySerial']);
$stmt->execute();
$data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

echo json_encode(array("allItems"=>$data));

Answer 2

As you are inserting a single row in the insertItems method, there is no need to begin/set/end a transaction as a single SQL statement is a transaction.

You only need to begin/set/end transactions if doing multiple statements together e.g. in a loop, in which case you begin at the start of the loop, set when the loop has completed successfully and then end.

Processing a loop (or a stream of statements) within a transaction can be a large time saver as the disk is written to just the once for all the changes applied as opposed to the disk being written to for each statement.

Closing the database is also not necessary throughout an App for general CRUD operations. Opening the database is relatively intensive, which would have to be done for each iteration after the first as the first closes the database.

So insertItems could be :-

    public long insertItems (
                             String itemCode, String brand, String desc) {
        SQLiteDatabase sq = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(TableData.TableInfo.COL_ITEMS_ITEMCODE, itemCode);
        cv.put(TableData.TableInfo.COL_ITEMS_BRAND, brand);
        cv.put(TableData.TableInfo.COL_ITEMS_DESCRIPTION, desc);
        return sq.insert(TableData.TableInfo.TB_ITEMS, null, cv);
    }

• Note that the signature has been changed to return a long, this will be -1 if the row couldn't be inserted.
• The signature has also been changed as there is no need to pass an instance of DatabaseOperations to the instance, instead this.getWritableDatabase() can be used to get the SQLiteDatabase.
• Typically Try/Catch is not a good idea as it tends to introduce confusion
  • e.g. why didn't attempt to insert a non-UNQIUE row not throw an exception (it results in no exception but the row is inserted so -1 is returned from the insert)

Along with the real potential optimization, doing the loop inside a single transaction :-

    .........
    DatabaseOperations dop = new DatabaseOperations(ctx);
    dop.getWritableDatabase().beginTransaction();
    boolean alldone = true;
    for (int i = 0; i < itemArray.length(); i++) {
        itemCode = itemArray.getJSONObject(i).getString("ITEMCODE");
        itemBrand = itemArray.getJSONObject(i).getString("BRAND");
        itemDesc = itemArray.getJSONObject(i).getString("DESCRIPTION");
        // if any row is not inserted then set alldone to indicate this.
        if (dop.insertItems(dop, itemCode, itemBrand, itemDesc) < 0) alldone = false;
    }
    // You may wish to rollback if alldone is false, if so then use commented line 
    // instead of dop.getWritableDatabase().setTransactionSuccessful();
    //if (alldone) dop.getWritableDatabase().setTransactionSuccessful();
    dop.getWritableDatabase().setTransactionSuccessful();
    dop.getWritableDatabase().endTransaction();

Timing Difference Example

Using the original insertItems method as insertItems and the suggested insertItems method as insertItemsV2 and the following code in an activity :-

public class MainActivity extends AppCompatActivity {
    DatabaseOperations dop;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        int insertCount = 8000;

        dop = new DatabaseOperations(this);
        Log.d("DOPMASSINSERTV1","Starting mass insertItems");
        for (int i=0; i < insertCount; i++) {
            dop.insertItems(dop,"ITEMCODE"+i,"BRAND"+i,"DESCRIPTION"+i);
        }
        Log.d("DOPMASSINSERTV1","mass insertItems completed");

        dop.getWritableDatabase().beginTransaction();
        Log.d("DOPMASSINSERTV2","Starting mass insertItemsV2");
        boolean alldone = true;
        for (int i=0; i< insertCount; i++) {
            if (dop.insertItemsV2("ITEMCODEV2"+i,"BRANDV2"+i,"DESCRIPTIONV2"+i) < 0) alldone = false;
        }
        if (alldone) dop.getWritableDatabase().setTransactionSuccessful();
        else {
            Log.d("DOPMASSINSERTV2","Rows not inserted!!!!");
        }
        dop.getWritableDatabase().endTransaction();
        Log.d("DOPMASSINSERTV2","mass insertItemsV2 completed");
    }
}

i.e. insertion of 8000 rows using first the original insertItems and then repeated (but with rows that will not result in a UNIQUE conflict (duplicate rows which won't be inserted)). A test run produces the following output in the log :-

2019-12-12 21:38:15.180 D/DOPMASSINSERTV1: Starting mass insertItems
2019-12-12 21:39:07.275 D/DOPMASSINSERTV1: mass insertItems completed
2019-12-12 21:39:07.276 D/DOPMASSINSERTV2: Starting mass insertItemsV2
2019-12-12 21:39:08.574 D/DOPMASSINSERTV2: mass insertItemsV2 completed

As such the original took 52 seconds (8.6 minutes for 80000), the amended took about 1.3 seconds (13 seconds for 80000). So in this test case the amended process 1/40th of the time.

Increasing the count to 80000 and commenting out the line dop.insertItems(dop,"ITEMCODE"+i,"BRAND"+i,"DESCRIPTION"+i); so the original inserts are skipped. Results in :-

2019-12-12 22:01:00.834 D/DOPMASSINSERTV1: Starting mass insertItems
2019-12-12 22:01:00.838 D/DOPMASSINSERTV1: mass insertItems completed
2019-12-12 22:01:00.868 D/DOPMASSINSERTV2: Starting mass insertItemsV2
2019-12-12 22:01:13.414 D/DOPMASSINSERTV2: mass insertItemsV2 completed

i.e. about 13 seconds to insert the 80000 rows.