Android + sqlite insert speed improvements?

Question

I recently inherited a project where a sqlite db is stored on the users sdcard (tables and columns only, no content). For the initial install (and subsequent data updates), an XML file is parsed via saxParser storing it's contents to the db columns like so:

saxParser:

    @Override
    public void endElement(String uri, String localName, String qName) throws SAXException {
            currentElement = false;

            if (localName.equals("StoreID")) {
                buffer.toString().trim();
                storeDetails.setStoreId(buffer.toString());
            } else if (localName.equals("StoreName")) {
                buffer.toString().trim();
                storeDetails.setStoreName(buffer.toString());
            ...

            } else if (localName.equals("StoreDescription")) {
              buffer.toString().trim();
              storeDetails.setStoreDescription(buffer.toString());
              // when the final column is checked, call custom db helper method
              dBHelper.addtoStoreDetail(storeDetails);
            }

            buffer = new StringBuffer();
    }           


    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        if (currentElement) {
            buffer.append(ch, start, length);
        }
    }

DatabaseHelper:

    // add to StoreDetails table
    public void addtoStoreDetail(StoreDetails storeDetails) {
        SQLiteDatabase database = null;
        InsertHelper ih = null;
        try {
            database = getWritableDatabase();
            ih = new InsertHelper(database, "StoreDetails");

            // Get the numeric indexes for each of the columns that we're updating
            final int idColumn = ih.getColumnIndex("_id");
            final int nameColumn = ih.getColumnIndex("StoreName");
            ...
            final int descColumn = ih.getColumnIndex("StoreDescription");

            // Add the data for each column
            ih.bind(idColumn, storeDetails.getStoreId());
            ih.bind(nameColumn, storeDetails.getStoreName());
            ...
            ih.bind(descColumn, storeDetails.getStoreDescription());

            // Insert the row into the database.
            ih.execute();
        } finally {
            ih.close();
            safeCloseDataBase(database);
        }
    }

The loaded xml document is 6000+ lines long. When testing on the device it stops inserting after around halfway (no errors) which takes about 4-5 minutes. On the emulator however, it runs rather quickly, writing all lines to the database in about 20 seconds. I have log statements that run when the db is opened, data added, then closed. The LogCat outputs are significantly slower when running on the device. Is there something I'm missing here? Why is my data taking so long to write? I thought the improved InsertHelper would help, but unfortunately not even a little faster. Can someone point out my flaw(s) here?

Answer 1

I also counted on InsertHelper improving singificantly the speed, but the difference wasnt that drastic when I tested it.

Still the strength of the InsertHelper is in multiple inserts, because it compiles the query just once. The way you do it you declare new InsertHelper for every insert, which is bypassing the one-time-compilation improvement. Try using the same instance for multiple inserts.

However, I do not think that 6000+ inserts will go in less than a minute on slow device.

EDIT Also make sure you fetch the column indices only once, this will speed up a bit more. Place these outside the loop for the batch insert.

 // Get the numeric indexes for each of the columns that we're updating
 final int idColumn = ih.getColumnIndex("_id");
 final int nameColumn = ih.getColumnIndex("StoreName");
 final int descColumn = ih.getColumnIndex("StoreDescription");