Improve performance of storing json response from server to sqllite

Question

I want this data for creating offline bible app for my local language.

Is there any other quicker way to do this?

String url ="https://raw.githubusercontent.com/godlytalias/Bible-Database/master/Tamil/bible.json";


        RequestQueue queue = MySingleton.getInstance(this.getApplicationContext()).
                getRequestQueue();


        JsonObjectRequest bibledata=new JsonObjectRequest(Request.Method.GET, url, null, new Response.Listener<JSONObject>() {
            @Override
            public void onResponse(JSONObject response) {

                try {
                    JSONArray Books=response.getJSONArray("Book");

                    for(int i=0;i< Books.length();i++)
                    {

                        JSONObject book=Books.getJSONObject(i);

                        JSONArray chapters=book.getJSONArray("Chapter");
                        for(int j=0;j< chapters.length();j++) {
                            JSONObject chapter=chapters.getJSONObject(j);
                            JSONArray verses=chapter.getJSONArray("Verse");

                            for (int k=0;k<verses.length();k++)
                            {
                                JSONObject verse=verses.getJSONObject(k);
                                int bookno=i+1;
                                int chapterno=j+1;
                                Cursor c=mydb.getrequiredData(verse.getString("Verseid"));
                                if(c.getCount()==0) {
                                    Bible bible = new Bible("Book " + bookno, "Chapter " + chapterno, verse.getString("Verseid"), verse.getString("Verse"));
                                    mydb.insertTamilBibledata(bible);
                                    if(c !=null)
                                    {
                                        c.close();
                                    }
                                }
                                else
                                { if(c !=null)
                                {
                                    c.close();
                                }

                                }


                            }
                        }
                    }


                } catch (JSONException e1) {
                    e1.printStackTrace();
                }

            }

        }, new Response.ErrorListener() {
            @Override
            public void onErrorResponse(VolleyError error) {
                Log.i("errrror",error.getMessage());
            }
        })

that is because your JSON is enormous. You can't save large data fast. — Vladyslav Matviienko, Mar 09 '18 at 06:12

CarlLee · Accepted Answer · 2018-03-09T08:21:42.973

0

Sqlite will read from/write to log file for every transaction you execute, that's why it's so slow. You need to combine your insertions into one single transaction.

Here's how to use it

 db.beginTransaction();
 try {
   ...
   db.setTransactionSuccessful();
 } finally {
   db.endTransaction();
 }

https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#beginTransaction()

Edit:

Here's something easier for you to understand:

JSONArray chapters=book.getJSONArray("Chapter");

mydb.beginTransaction();
try {
    for(int j=0;j< chapters.length();j++) {
        JSONObject chapter=chapters.getJSONObject(j);
        JSONArray verses=chapter.getJSONArray("Verse");

        for (int k=0;k<verses.length();k++)
        {
            JSONObject verse=verses.getJSONObject(k);
            int bookno=i+1;
            int chapterno=j+1;
            Cursor c=mydb.getrequiredData(verse.getString("Verseid"));
            if(c.getCount()==0) {
                Bible bible = new Bible("Book " + bookno, "Chapter " + chapterno, verse.getString("Verseid"), verse.getString("Verse"));
                mydb.insertTamilBibledata(bible);
                if(c !=null)
                {
                    c.close();
                }
            }
            else
            { if(c !=null)
            {
                c.close();
            }

            }


        }
    }
    mydb.setTransactionSuccessful();
}
finally {
    mydb.endTransaction();
}

Here's an example of how to use ON CONFILICT clause on Android:

SQLite table constraint unique and ON CONFLICT REPLACE usage

edited Mar 09 '18 at 08:21

answered Mar 09 '18 at 06:01

CarlLee

3,952
5
23
33

Thanks for your help CarlLee.I tried but i am getting same issue.It consists 31102 verses so it is taking long time to save – Rajkumar Gnanaraj Mar 09 '18 at 06:12
Did you put your for loop and insertion where "..." is? – CarlLee Mar 09 '18 at 07:12
For insertion i am not using where condition.I am only checking whether previous data exist using cursor.. – Rajkumar Gnanaraj Mar 09 '18 at 07:24
I meant did you put for loop and insertion between the call to `db.beginTransaction();` and `db.endTransaction();` ? You better show some code – CarlLee Mar 09 '18 at 07:45
public long insertTamilBibledata(Bible bible) { long result=0; SQLiteDatabase db = this.getWritableDatabase(); try { db.beginTransaction(); ContentValues values = new ContentValues(); values.put(BookNumber, bible.getBookname()); values.put(ChapterNo, bible.getChapterName()); values.put(VerseID, bible.getVerseID()); values.put(Verse, bible.getVerse()); result = db.insert(Table_Tamil, null, values); db.setTransactionSuccessful(); } finally { db.endTransaction(); } return result; } – Rajkumar Gnanaraj Mar 09 '18 at 08:15
You have to put the for loop between ``db.beginTransaction();`` and ``db.endTransaction();`` too. Not just the insert method – CarlLee Mar 09 '18 at 08:16
Plus, don't do unnecessary query before insertion, just use a "on conflict" clause in your insert SQL. – CarlLee Mar 09 '18 at 08:18

score 0 · Answer 2 · answered Mar 09 '18 at 06:08

0

You can use some Persistence Library like Room,Realm or ObjectBox. These are very good Libraries.

answered Mar 09 '18 at 06:08

Rohit kumar

31
2

1) https://developer.android.com/training/data-storage/room/index.html 2) https://realm.io/docs/java/latest/#getting-started 3) http://objectbox.io/ – Rohit kumar Mar 09 '18 at 06:09

Improve performance of storing json response from server to sqllite

2 Answers2