android : Loading data from XML to SQLite take very long time

Question

I have very large table 300,000 records I am loading from XML file to SQLite,

unfortunately, this is taking very very long time (took 15 min then I had to stop it).

is this normal?

is there anything wrong with my code.

how to speed it up?

thanks

    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_list_filter);

        new Thread(new Runnable() {
            public void run() {
                FillLocations();
            }
        }).start();

}




private void FillLocations()
{
    OpenDB();
    Resources res = getResources();
    ContentValues _Values = new ContentValues();

    //Open xml file
    XmlResourceParser _xml = res.getXml(R.xml.locations);
    try
    {
        //Check for end of document
        int eventType = _xml.getEventType();
        while (eventType != XmlPullParser.END_DOCUMENT) {
            //Search for record tags
            if ((eventType == XmlPullParser.START_TAG) &&(_xml.getName().equals("Cities"))){
                //Record tag found, now get values and insert record
                String City = _xml.getAttributeValue(null, "City");
                String Country = _xml.getAttributeValue(null, "Country");
                String Time = _xml.getAttributeValue(null, "Time");
                _Values.put("City", City);
                _Values.put("Country", Country );
                _Values.put("Time", Time);
                db.insert("Locations", null, _Values);
            }
            eventType = _xml.next();
        }
    } catch (XmlPullParserException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

Have you benchmarked your code to see exactly where it spends that much time? Also, why not pre-populate sqllite and distribute it as a binarly blob? — Tassos Bassoukos, Jul 10 '14 at 06:59
How can I pre-populate sqllite and distribute it as a binarly blob? — asmgx, Jul 10 '14 at 07:02
@asmgx you can try with this https://github.com/jgilfelt/android-sqlite-asset-helper OR you can copy the file manually — LordRaydenMK, Jul 10 '14 at 07:07
you mean to copy the SQLite file manually? is this possible? how to it? — asmgx, Jul 10 '14 at 07:17
If you don't know how to copy it manually use the library in the link above. It will copy the database file for you. — LordRaydenMK, Jul 10 '14 at 07:51
I dont know who add this comment to this question "Android SQLite database: slow insertion 5 answers" but this is not the answer for my question, in that question they talk about 200 records, and 200kb file and they waited 1 min. mine is 300,000 records, and 25MB file, and after applying the same solution, I had to stop the app after 10 min process. so for sure this is not the answer for my question, — asmgx, Jul 10 '14 at 10:11

score 1 · Accepted Answer · answered Jul 10 '14 at 07:02

Try this code:

Before while loop beginTransaction and after successful insertion of all rows endSuccessfullTransaction.

public static void beginTransaction()
{
    final SQLiteDatabase db = openDatabase();
    if (db != null)
    {
        db.beginTransaction();
    }
}

public static void endSuccessfullTransaction()
{
    final SQLiteDatabase db = openDatabase();
    db.setTransactionSuccessful();
    db.endTransaction();
}

android : Loading data from XML to SQLite take very long time

1 Answers1