System error android sqlite insert

Question

I am trying to build a sqlite database. It has 2800 entries in them. When i try to insert, it takes a minute or so and later gives the system error message. The respective codes are given below.

In the create database java file,

ContentValues cv4 = new ContentValues();    
public long createVariantEntry(String varid, String makeid, String modelid, String varname, String posteddate) {        
    cv4.put(VARIANT_ID, varid);
    cv4.put(VARIANT_MAKE_ID, makeid);
    cv4.put(VARIANT_MODEL_ID, modelid);
    cv4.put(VARIANT_NAME, varname);
    cv4.put(VARIANT_POSTED_DATE, posteddate);
    return Database.insert(VARIANT_TABLE_NAME, null, cv4);
}

In the mainActivity,

for(int i = 0; i<build_emp_database.size();i++)
{                   
    md.createVariantEntry(build_emp_database.get(i).get(0), build_emp_database.get(i).get(1), build_emp_database.get(i).get(2), build_emp_database.get(i).get(3), build_emp_database.get(i).get(4));                                
}

Also, just for 2800 entries, it is taking more than a minute, is there any way to speed them up?? I have several small databases, and have loaded them successfully. This is the only big database and its creating an issue while inserting. Please help where am i going wrong.

For starters, you could assign `build_emp_database.get(i)` to a local variable in the `loop` and call the `get()` method so many times on that! — Rahul, May 07 '13 at 03:30
Thanks for the reply, what do you mean by call the get() method so many times on that?? — bharath, May 07 '13 at 03:33
`build_emp_database.get(i).get(0)`, `build_emp_database.get(i).get(1)`, etc. This is the line I was referring to. Assign that `build_emp_database.get(i)` to a local variable say `myObj` and then call `myObj.get(0)`, `myObj.get(1)`, etc. — Rahul, May 07 '13 at 03:35
Instead of calling insert for 2800 times, which I think that is exactly the issue. I think you may try refer to this post : http://stackoverflow.com/a/5009740/1131470, it teaches you how to insert multiple row at once. So do some logic, generate a string which is able to insert 2800 records at once, or portion by portion, minimizing the insert process. And yes, referring to Joe Malin, try to do it in AsyncTask. — Chor Wai Chun, May 07 '13 at 03:40
Tried that, after 64 sec, its stops giving system error in the line create variantentry! — bharath, May 07 '13 at 03:41
@Chor, this is for mysql, not sure if it works with sqlite, thanks for the reply, will try that as well! — bharath, May 07 '13 at 03:49

score 1 · Accepted Answer · answered May 07 '13 at 03:33

1

Don't try to run the insert operation on the UI thread, for starters.

I suggest you investigate using a content provider as a wrapper around your database. The ContentResolver object provides methods for doing operations in batch, and is in general a more robust way of working with databases. Use an IntentService to run the insert operation in the background.

answered May 07 '13 at 03:33

Joe Malin

8,621
1
23
18

Thanks for the reply, both the create database and mainactivity are different java function. you suggest to run the insert using intentservice inside a asyntask? – bharath May 07 '13 at 03:35

score 1 · Answer 2 · answered May 07 '13 at 04:58

If you do a lot insert operation, you need use the ContentProviderOperation to optimize your db operation. like these:

 ArrayList<ContentProviderOperation> ops = new ArrayList<ContentProviderOperation>();
     for(int i = 0; i<build_emp_database.size();i++) {
       ContentProviderOperation.Builder builder = 
                 ContentProviderOperation.newInsert(yourUrl);
        builder.withValue(VARIANT_ID, varid)
               .withValue(VARIANT_MAKE_ID, makeid)
               ...
        ops.add(builder.build());
    }

     yourContentResolver.applyBatch(yourauthority, ops);

System error android sqlite insert

2 Answers2