Android Java JSON parser/insert database optimization

Question

I'm not pretty sure if this question is for here, but I want to ask all of you guys, who really can give me some advices of how to optimize better this piece of code, to run better in proper way and faster. The thing that I'm doing is that I'm downloading data over internet as JSON, parsing it and insert it in sqlite database. If the json string is not big, there is not a big problem for me, but when my json contains a lot of arrays and objects in some situations I'm waiting like 10-13 minutes to download/parse/insert all data in database, which is too much time.

The code that I'm showing is some kind of test code, because I was trying to implement InsertHelper to see if there will be a bit difference in speed, but the result is the same for now. Here is the code :

    UserDatabaseHelper userDbHelper = RPCCommunicator.rpcUserDbHelper;

    SQLiteDatabase db = userDbHelper.getWritableDatabase();
    InsertHelper ih = new InsertHelper(db, "cards");

    ih.prepareForInsert();
    //ContentValues values = new ContentValues();
    ContentValues valuess = new ContentValues();
    try {
        int objectid = ih.getColumnIndex("objectId");
        ih.bind(objectid, objectId);
        //values.put("objectId", objectId);
        Log.d("", "ObjectId: " + objectId);
        int objectoid = ih.getColumnIndex("objectOid");
        ih.bind(objectoid, objectOid);
        //values.put("objectOid", objectOid);

        String jsonData = new String(cardBuffer, "UTF-8");
        Log.d("JSONDATA", "JSONDATA VALID OR NOT : " + jsonData);
        json = new JSONObject(jsonData);
        JSONObject jsonObj = (JSONObject) new JSONTokener(jsonData).nextValue();

        int collectionID = ih.getColumnIndex("collectionId");
        int collectionId = Integer.parseInt(jsonObj.optString("collection_id","0"));
        Log.d("Collection Id ", "Show Collection Id : " + collectionId);
        if(collectionId!=0)
            ih.bind(collectionID, collectionId);

        //values.put("collectionId", collectionId);

        int categoryID = ih.getColumnIndex("categoryId");
        int categoryId = Integer.parseInt(jsonObj.optString("category_id", "0"));
        Log.d("Category Id ", "Show Category Id : " + categoryId);
        if(categoryId!=0)
            ih.bind(categoryID, categoryId);
        //values.put("categoryId", categoryId);

        int dateCreated = ih.getColumnIndex("dateCreated");
        String date = jsonObj.optString("date_created");
        if(date!=null)
            ih.bind(dateCreated, date);
        //values.put("dateCreated", date);

        int titlee = ih.getColumnIndex("title");
        String title = jsonObj.optString("title");
        Log.d("Title", "Show Title : " + title);
        if(title!=null)
            ih.bind(titlee, title);
        //values.put("title", title);

        // ... some other variables to get from JSON

        JSONObject stats = jsonObj.optJSONObject("statistics");

        if (jsonObj.has("statistics")) {
            ContentValues values2 = new ContentValues();
            InsertHelper ihr = new InsertHelper(db, "cardstats");


            Iterator<Object> keys = stats.keys();
            while (keys.hasNext()) {

                ihr.prepareForInsert();
                String key = (String) keys.next();
                JSONObject obj = new JSONObject();
                obj = stats.getJSONObject(key);

                int paramId = Integer.parseInt(obj.optString("param_id"));

                int cardIdTable = ihr.getColumnIndex("cardId");
                ihr.bind(cardIdTable, objectId);

                values2.put("cardId", objectId);

                int statKey = ihr.getColumnIndex("statKeyId");
                ihr.bind(statKey, paramId);

                values2.put("statKeyId", paramId);

                int catIdTable = ihr.getColumnIndex("catId");
                int catId = Integer.parseInt(obj.optString("cat_id"));
                ihr.bind(catIdTable, catId);

                values2.put("catId", catId);

                int paramtitle = ihr.getColumnIndex("title");
                String paramTitle = obj.optString("param_title");
                ihr.bind(paramtitle, paramTitle);

                values2.put("title", paramTitle);

                String cardstats = "SELECT cardId , statKeyId FROM cardstats WHERE cardId="+objectId+" AND statKeyId="+catId;
                Cursor cardStats = userDbHelper.executeSQLQuery(cardstats);
                if(cardStats.getCount()==0){
                    //userDbHelper.executeQuery("cardstats", values2);
                    ihr.execute();
                } else {
                    for(cardStats.moveToFirst(); cardStats.moveToNext(); cardStats.isAfterLast()){
                        //int card = Integer.parseInt(cardStats.getString(cardStats.getColumnIndex("cardId")));
                        int statId = Integer.parseInt(cardStats.getString(cardStats.getColumnIndex("statKeyId")));

                        if(paramId != statId){
                            ihr.execute();
                            //userDbHelper.executeQuery("cardstats", values2);
                        } else {
                            userDbHelper.updateSQL("cardstats", values2, "cardId=?", new String[]{Integer.toString(objectId)});
                        }
                    }
                }
                cardStats.close();

                //userDbHelper.executeQuery("cardstats", values2);
            }
        }// end if

        String sql = "SELECT objectId FROM cards WHERE objectId = " + objectId;
        Cursor cursor = userDbHelper.executeSQLQuery(sql);
        if (cursor.getCount() == 0) {
            ih.execute();
            //userDbHelper.executeQuery("cards", values);
        } else {
            for (cursor.move(0); cursor.moveToNext(); cursor.isAfterLast()) {
                int objectID = Integer.parseInt(cursor.getString(cursor.getColumnIndex("objectId")));
                Log.d("","objectId : objectID - "+objectId+"    "+objectID );
                if (objectId != objectID) {
                    ih.execute();
                    //userDbHelper.executeQuery("cards", values);
                } else if(objectId == objectID){
                    userDbHelper.updateSQL("cards", valuess, "objectId=?", new String[] {Integer.toString(objectId)});
                }
            }
        }
        cursor.close();

    } catch (Exception e) {
        e.printStackTrace();
        Log.d("Error",  ": " + e);
    }
    db.close();
    return true;
}

*Edit: *

And here is how I save the binary data (images) which I get from internet :

public static void saveToExternalStorage(String servername, int userId, String  filename, byte[] buffer){
    try {
        File myDir=new File("/sdcard/.Stampii/Users/"+servername+"/"+userId+"/Storage");
        myDir.mkdirs();

        File file = new File(myDir, filename);
        FileOutputStream fos = new FileOutputStream(file);
        fos.write(buffer);
        fos.flush();
        fos.close();

    } catch (FileNotFoundException e){
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

So any kind of suggestions/advices are welcomed which will help me to improve this piece of code and make it run faster.

Thanks in advance!

What's the performance if you try to do larger chunks in transactions (SQLiteDatabase#beginTransaction()), say do 100-300 inserts/updates a pop? — Jens
– Jens, Commented Feb 16, 2012 at 14:32
Actually in one packet which I'm downloading I have like 250 inserts, and I'm receiving like 200 packets of these. So If I use ContentValues or InsertHelper, the difference is like 10-15 seconds for now. — Android-Droid
– Android-Droid, Commented Feb 16, 2012 at 14:36
Well, starting a SQL transaction would affect both "normal" inserts and the optimized InsertHelper. If it affect the speed any probably requires testing. Have you timed how much time you actually spend in SQLite versus simply downloading and parsing the data? — Jens
– Jens, Commented Feb 16, 2012 at 14:39
I did it with the whole packets.It takes me like 13 minutes to download like 1700 packets / parse the json and insert the whole data in sqlite. — Android-Droid
– Android-Droid, Commented Feb 16, 2012 at 14:43
Actually forget to mention that these test are made on Samsung Galaxy Nexus. I'm doing the same operation on HTC EVO 3D for like 6 minutes - 1700 packets again. — Android-Droid
– Android-Droid, Commented Feb 16, 2012 at 14:45

Jens · Accepted Answer · 2012-02-16 15:10:23Z

Even if you have a lot of HTTP traffic (which you appear to have) you can still optimize your use of the database.

This naïve example that does 10000 inserts will show you the scale of improvement we're talking about here:

public class BombasticActivity extends Activity {
    DBHelper mHelper;
    SQLiteDatabase mDb;
    InsertHelper mInsertHelper;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        mHelper = new DBHelper(this);
        mDb = mHelper.getWritableDatabase();
        mInsertHelper = new InsertHelper(mDb, "table1");
    }
    @Override
    protected void onStart() {
        super.onStart();
        AsyncTask.SERIAL_EXECUTOR.execute(new MeasureTime(new Insert(10000, mInsertHelper)));
        AsyncTask.SERIAL_EXECUTOR.execute(new MeasureTime(new DoInTransaction(mDb, new Insert(10000, mInsertHelper))));
    }
    @Override
    protected void onDestroy() {
        super.onDestroy();
        mInsertHelper.close();
        mDb.close();
        mHelper.close();
    }
    static class MeasureTime implements Runnable {
        final Runnable mAction;
        MeasureTime(Runnable action) {
            mAction = action;
        }
        public void run() {
            final String name = mAction.getClass().getSimpleName();
            System.out.println("Starting action (" + name + ")");
            long t0 = System.currentTimeMillis();
            try {
                mAction.run();
            } finally {
                t0 = System.currentTimeMillis() - t0;
                System.out.println("Time to complete action (" + name + "): " + t0 + "ms");
            }
        }
    }
    static class DoInTransaction implements Runnable {
        final Runnable mAction;
        final SQLiteDatabase mDb;
        DoInTransaction(SQLiteDatabase db, Runnable action) {
            mAction = action;
            mDb = db;
        }
        public void run() {
            mDb.beginTransaction();
            try {
                mAction.run();
                mDb.setTransactionSuccessful();
            } finally {
                mDb.endTransaction();
            }
        }
    }
    static class Insert implements Runnable {
        final int mNumberOfInserts;
        final InsertHelper mInsertHelper;
        Insert(int numberOfInserts, InsertHelper insertHelper) {
            mNumberOfInserts = numberOfInserts;
            mInsertHelper = insertHelper;
        }
        public void run() {
            Random rnd = new Random(0xDEADBEEF);
            ContentValues values = new ContentValues();
            for (int i = 0; i < mNumberOfInserts; i++) {
                values.put("text1", String.valueOf(rnd.nextDouble()));
                values.put("text2", String.valueOf(rnd.nextFloat()));
                values.put("text3", String.valueOf(rnd.nextLong()));
                values.put("int1", rnd.nextInt());
                mInsertHelper.insert(values);
                if (i % 200 == 0) {
                    System.out.println("Done " + i + " inserts");
                }
            }
        }
    }
}

class DBHelper extends SQLiteOpenHelper {
    DBHelper(Context context) {
        super(context.getApplicationContext(), "bombastic", null, 1);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE table1 (_id INTEGER PRIMARY KEY AUTOINCREMENT, text1 TEXT, text2 TEXT, text3 TEXT, int1 INTEGER)");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

On an ICS device (you can run it on Gingerbread if you start a thread or threadpool instead of abusing AsyncTask.SERIAL_EXECUTOR) the non-transaction version takes almost 4 minutes to complete (229484ms) while the version running in the transaction only takes about 3 seconds (2975ms).

So put it shortly, do a lot of updates - do it in a transaction.

To optimize your HTTP you should ensure that you are keeping the HTTP connection alive (keep-alive) and downloading larger chunks. Much larger than the ones you are doing now - if possible switch to a JSON parser that supports reading from a stream instead of loading the entire thing into a String before parsing it.

Community · Accepted Answer · 2017-05-23 12:11:23Z

1

There are two time consuming activity involved in your case.

a. Downloading data in packets (assuming it to be HTTP). For a single packet it should take you about 1-3 sec depending on the network latency. For 200 = 2X100 = 200 seconds ~ 3 mins You can save lots of seconds, if you download entire data in say not more than 3-5 round-trip calls.

b. Database insert You need to do file operation specifically write file operation which takes time. Honestly you cannot much optimization here

Check my other answer here

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Feb 16, 2012 at 14:55

PravinCG

7,7183 gold badges33 silver badges55 bronze badges

3 Comments

Android-Droid Over a year ago

Actually the last few packets are binary data, which I'm saving on SD Card, any good advices about the best way to save them. I'll edit my answer with the function which I'm writing them to a SDCard.

PravinCG Over a year ago

Your method to save image looks good. There is not much you can do to improve them though apart from putting this in AsyncTask and running in Background.

Jens Over a year ago

@Bombastic: You should never hardcode "/sdcard" into your paths when writing to external storage. Try Environment#getExternalStorageDirectory() instead & check its state before.

Collectives™ on Stack Overflow

Android Java JSON parser/insert database optimization

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related