How Improve SQLite Performance?

Question

I want to inset more than 3000 records in android SQLite but the following code take to much time for the data inserion Here is my code.

public boolean addSale(Sale objSale) {
    ContentValues values = new ContentValues();
    values.put(SALE_BRANCH, objSale.getBranch());
    values.put(SALE_SUPPLIER, objSale.getSupplier());
    values.put(SALE_BUYER, objSale.getBuyer());
    values.put(SALE_CAT1, objSale.getCat1());
    values.put(SALE_CAT2, objSale.getCat2());
    values.put(SALE_CAT3, objSale.getCat3());
    values.put(SALE_CAT4, objSale.getCat4());
    values.put(SALE_CAT5, objSale.getCat5());
    values.put(SALE_CAT6, objSale.getCat6());
    values.put(SALE_DESIGNO, objSale.getDesigNo());
    values.put(SALE_ITEMSIZE, objSale.getItemSize());
    values.put(SALE_SALEQTY, objSale.getSaleQty());
    values.put(SALE_STOCKQTY, objSale.getStockQty());
    values.put(SALE_FinalProduct, objSale.getFinalProduct());
    values.put(SALE_PriceRange, objSale.getPriceRange());
    values.put(SALE_CoreNonCore, objSale.getCoreNonCore());
    values.put(SALE_Color, objSale.getColor());
    values.put(SALE_GSLCode, objSale.getGSLCode());
    values.put(SALE_Wanted, objSale.getWanted());
    values.put(SALE_Pqty, objSale.getPqty());
    values.put(SALE_MRP, objSale.getMRP());
    values.put(SALE_PRate, objSale.getPRate());
    SQLiteDatabase db = this.getWritableDatabase(); 
    db.insert(TABLE_SALE, null, values);
    db.close();
    return true;
}

And this one is my Asynk Task. Where i fetch data from webservice and insert into SQLite

protected void onPostExecute(String result) 
        {
            try {

                String l = result.replace("\\", "");
                l = l.replace("''", "");
                String sdsd = l.substring(1, l.length() - 1);
                JSONArray jsonArray = new JSONArray(sdsd);
                Log.i("JSON", "Number of surveys in feed: " +jsonArray.length());
                /*if(db.delSaleData()){

                }*/
                    for (int i = 0; i < jsonArray.length(); i++) 
                    {
                        JSONObject jsonObject = jsonArray.getJSONObject(i);

                            sl.setBranch(jsonObject.getString("Branch"));
                            sl.setSupplier(jsonObject.getString("Supplier"));
                            sl.setBuyer(jsonObject.getString("Buyer"));
                            sl.setCat1(jsonObject.getString("Cat1"));
                            sl.setCat2(jsonObject.getString("Cat2"));
                            sl.setCat3(jsonObject.getString("Cat3"));
                            sl.setCat4(jsonObject.getString("Cat4"));
                            sl.setCat5(jsonObject.getString("Cat5"));
                            sl.setCat6(jsonObject.getString("Cat6"));
                            sl.setDesigNo(jsonObject.getString("DesigNo"));
                            sl.setItemSize(jsonObject.getString("ItemSize"));
                            sl.setSaleQty(jsonObject.getString("SaleQty"));
                            sl.setStockQty(jsonObject.getString("StockQty"));
                            sl.setFinalProduct(jsonObject.getString("FinalProduct"));
                            sl.setPriceRange(jsonObject.getString("PriceRange"));
                            sl.setCoreNonCore(jsonObject.getString("CoreNonCore"));
                            sl.setColor(jsonObject.getString("Color"));
                            sl.setGSLCode(jsonObject.getString("GSLCode"));
                            sl.setWanted(jsonObject.getString("Wanted"));
                            sl.setPqty(jsonObject.getString("Pqty"));
                            sl.setMRP(jsonObject.getString("MRP"));
                            sl.setPRate(jsonObject.getString("PRate"));

                            if(db.addSale(sl))
                            {
                                //Toast.makeText(getApplicationContext(), " Insert.." , Toast.LENGTH_SHORT).show(); 
                            }
                    }

                    setData();


                } catch (Exception e) {
                    e.printStackTrace();
            }
            dialog.dismiss();
            setTableData("All");


        }

Check this .. http://www.techrepublic.com/blog/software-engineer/turbocharge-your-sqlite-inserts-on-android/ — Jagadesh Seeram, Oct 30 '14 at 13:56
Is this any wrong in this que..?if not then why voted negative? — Harshal, Oct 30 '14 at 14:47

score 2 · Answer 1 · answered Oct 30 '14 at 14:01

2

use the SQLiteStatement for example:

private void bulkInsertRecords(String[] records) {
          String sql = "INSERT INTO "+ SAMPLE_TABLE_NAME +" VALUES (?,?,?);";
          SQLiteStatement statement = sampleDB.compileStatement(sql); //Este é o prepare
          sampleDB.beginTransaction();
          for (int i = 0; i<records.length; i++) {
                    statement.clearBindings();
                    statement.bindString(1, records[0]);
                    statement.bindString(2, records[1]);
                    statement.bindString(3, records[2]);
                    statement.execute();
           }
           sampleDB.setTransactionSuccessful(); 
           sampleDB.endTransaction();
}

answered Oct 30 '14 at 14:01

Ludger

362
3
16

Does this make it faster? – Blaze Tama Oct 30 '14 at 14:02
Really...its make data insertion faster..?? – Harshal Oct 30 '14 at 14:03
yes but read this link to get more information http://tech.vg.no/2011/04/04/speeding-up-sqlite-insert-operations/ – Ludger Oct 30 '14 at 14:07
Yes, it does make it faster. Each transaction is an file open and close. Doing that for each insert is extremely expensive. – G. Blake Meike Oct 30 '14 at 14:22

score 1 · Answer 2 · answered Oct 30 '14 at 13:59

1

Don't open and close the database every time you want to do an insert. Open it once and then close it when exiting the app.

answered Oct 30 '14 at 13:59

reactivemobile

505
3
9

+1, You should also check this. – Blaze Tama Oct 30 '14 at 14:01

Blaze Tama · Answer 3 · 2014-10-30T14:03:46.810

-1

Use the Asyncclass. Do the operation onBackground, not in the main UI thread.

UPDATE

On your code, you insert the SQLite via onPost, not onBackground. onPost do things on UI thread, while onBackground do it on the separate (background) thread so it doesnt effect the UI. So move the operation code to onBackground.

edited Oct 30 '14 at 14:03

answered Oct 30 '14 at 13:53

Blaze Tama

10,828
13
69
129

Does that improve SQLite performance? – Simas Oct 30 '14 at 13:54
Yes..all the insertion is performed in Asynk Task.. – Harshal Oct 30 '14 at 13:55
@user3249477 yes, it makes the UI thread run like usually so user wont notice you are inserting the DB. Remember, we talk about thousand of rows here. – Blaze Tama Oct 30 '14 at 13:57
@user1690714 Please post the async code too, maybe we can find something there. – Blaze Tama Oct 30 '14 at 13:57
@BlazeTama you didn't answer my question. Only said that user won't notice, that doesn't mean that SQLite performance has improved. – Simas Oct 30 '14 at 13:58
@user3249477 Sorry i dont really get your point. I think he create this question because the insert operation make his apps lag. If not, why we care? – Blaze Tama Oct 30 '14 at 14:01
My point is the OP asked for a faster insert not an insert that is done in the background. `If not, why we care` ? – Simas Oct 30 '14 at 14:08
plz..give the proper answer guys.. – Harshal Oct 30 '14 at 14:13
I tried to help with what i knew. A downvote? Where is the love guys? – Blaze Tama Oct 30 '14 at 14:22

How Improve SQLite Performance?

3 Answers3