Android: how to insert data faster in sqlite

Question

I'm getting muliple records from webservice and I'm inserting those records in internal database but inserting and updating is taking much time. For faster insertion I call the beginTransaction methods of database helper but it is still taking the same time. Please check where I'm making mistake.

 VideoEntity videoEntity;  
 for(int i = 0; i < response.body().getCategoryList().size(); i++) {
                   videoEntity = response.body().getCategoryList().get(i);
                    dbHelper.insertChannels(videoEntity);

                }

insert method in dbHelper class

public void insertChannels(VideoEntity videoEntity) {
    Log.e("inserting channels", "yes");
    db = this.getWritableDatabase();
    db.beginTransaction();
    try {
        ContentValues initialValues = new ContentValues();
        initialValues.put(CHANNELS_COLUMN_CHANNELID, videoEntity.getVideoEntityId());
        initialValues.put(CHANNELS_COLUMN_CHANNELCATEGORYID, videoEntity.getVideoCategoryId());
        initialValues.put(CHANNELS_COLUMN_CHANNELNAME, videoEntity.getVideoName());
        initialValues.put(CHANNELS_COLUMN_CHANNELDESCRIPTION, videoEntity.getVideoDescription());
        initialValues.put(CHANNELS_COLUMN_CHANNELPATH, videoEntity.getVideoImageThumbnail());
        initialValues.put(CHANNELS_COLUMN_CHANNELPOSTERPATH, videoEntity.getVideoPosterPath());
        initialValues.put(CHANNELS_COLUMN_CHANNELMOBILESMALL, videoEntity.getVideoImagePath());
        initialValues.put(CHANNELS_COLUMN_CHANNELSTREAMURLLOW, videoEntity.getVideoStreamUrlLow());
        initialValues.put(CHANNELS_COLUMN_CHANNELTOTALVIEWS, videoEntity.getVideoTotalViews());
        initialValues.put(CHANNELS_COLUMN_CHANNELMOBILELARGE, videoEntity.getVideoImagePathLarge());
        initialValues.put(CHANNELS_COLUMN_CHANNELSTREAMURLHIGH, videoEntity.getVideoStreamUrl());
        initialValues.put(CHANNELS_COLUMN_CHANNELADDEDDATE, videoEntity.getVideoAddedDate());

        db.insert(TABLE_CHANNELS, null, initialValues);
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}

Possible duplicate of [Improve INSERT-per-second performance of SQLite?](http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite) — Kapta, Nov 21 '16 at 08:20
Possible duplicate of [Android SQLite database: slow insertion](http://stackoverflow.com/questions/3501516/android-sqlite-database-slow-insertion) — Bö macht Blau, Nov 21 '16 at 08:57
@Andrain - in a nutshell: use batch inserts instead of inserting single rows :) — Bö macht Blau, Nov 21 '16 at 08:58

CL. · Answer 1 · 2016-11-21T11:47:12.983

When you do not use explicit transactions, the database automatically wraps a transaction around each single SQL statement.

Using one explicit transaction for each insert does not reduce the transaction overhead.

Add a single transaction around the entire loop:

SQLiteDatabase db = dbHelper.getWritableDatabase();
db.beginTransaction();
try {
    for (...) {
        ... dbHelper.insertChannels() ...
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

score -2 · Answer 2 · answered Nov 21 '16 at 08:23

Please follow same things and Let me know any doubt---

Model class :--UserInfo .class

public class UserInfo implements Serializable {
private int id;
private String name;
private String username;
private String email;
private String address;
private String street;
private String suite;
private String zipcode;
private String geo;
private Long lat;
private Long lng;
private String phone;
private String website;
private String company;
private String phnname;
private String catchPhrase;
private String bs;

public String getPhone() {
    return phone;
}

public void setPhone(String phone) {
    this.phone = phone;
}

public String getWebsite() {
    return website;
}

public void setWebsite(String website) {
    this.website = website;
}

public String getCompany() {
    return company;
}

public void setCompany(String company) {
    this.company = company;
}

public String getPhnname() {
    return phnname;
}

public void setPhnname(String phnname) {
    this.phnname = phnname;
}

public String getCatchPhrase() {
    return catchPhrase;
}

public void setCatchPhrase(String catchPhrase) {
    this.catchPhrase = catchPhrase;
}

public String getBs() {
    return bs;
}

public void setBs(String bs) {
    this.bs = bs;
}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getUsername() {
    return username;
}

public void setUsername(String username) {
    this.username = username;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

public String getAddress() {
    return address;
}

public void setAddress(String address) {
    this.address = address;
}

public String getStreet() {
    return street;
}

public void setStreet(String street) {
    this.street = street;
}

public String getSuite() {
    return suite;
}

public void setSuite(String suite) {
    this.suite = suite;
}

public String getZipcode() {
    return zipcode;
}

public void setZipcode(String zipcode) {
    this.zipcode = zipcode;
}

public String getGeo() {
    return geo;
}

public void setGeo(String geo) {
    this.geo = geo;
}

public Long getLat() {
    return lat;
}

public void setLat(Long lat) {
    this.lat = lat;
}

public Long getLng() {
    return lng;
}

public void setLng(Long lng) {
    this.lng = lng;
}

}

DatabaseHandler class:--


 public class DataBaseHandler extends SQLiteOpenHelper implements EmployeeListener {

private static final int DB_VERSION = 1;
private static final String DB_NAME = "info.db";
private static final String TABLE_NAME = "info_table";
private static final String KEY_ID = "_id";
private static final String KEY_NAME = "_name";
private static final String KEY_USER_NAME = "_username";
private static final String KEY_EMAIL = "_email";
private static final String KEY_ZIP_CODE = "_zipcode";
private static final String KEY_STREET = "_street";
private static final String KEY_SUITE = "_suite";
private static final String KEY_PHONE = "_phone";
private static final String KEY_WEBSITE = "_website";
private static final String KEY_COMPANY = "_company";
private static final String KEY_PHONE_NAME = "_phnname";
private static final String TAG = DataBaseHandler.class.getName();

String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_USER_NAME + " TEXT," + KEY_EMAIL + " TEXT," + KEY_ZIP_CODE + " TEXT," + KEY_STREET + " TEXT," + KEY_SUITE + " TEXT," + KEY_PHONE + " TEXT," + KEY_PHONE_NAME + " TEXT," + KEY_WEBSITE + " TEXT," + KEY_COMPANY + " TEXT)";
String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;

public DataBaseHandler(Context context) {

    super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL(DROP_TABLE);
    onCreate(db);
}


@Override
public void addInfo(UserInfo Info) {
    SQLiteDatabase sqLiteDatabase=this.getWritableDatabase();
    try{
        ContentValues values = new ContentValues();
        values.put(KEY_ID, Info.getId());
        values.put(KEY_NAME,Info.getName());
        values.put(KEY_USER_NAME,Info.getUsername());
        values.put(KEY_EMAIL,Info.getEmail());
        values.put(KEY_ZIP_CODE,Info.getZipcode());
        values.put(KEY_STREET,Info.getStreet());
        values.put(KEY_SUITE,Info.getSuite());
        values.put(KEY_PHONE,Info.getPhone());
        values.put(KEY_WEBSITE,Info.getWebsite());
        values.put(KEY_COMPANY,Info.getCompany());
        values.put(KEY_PHONE_NAME,Info.getPhone());
        sqLiteDatabase.insert(TABLE_NAME, null, values);
        sqLiteDatabase.close();
    }catch (Exception e){
        Log.e(TAG,"getting Error"+e.getMessage());
    }

}

@Override
public ArrayList<UserInfo> getAllInfo() {
    SQLiteDatabase db = this.getReadableDatabase();
    ArrayList<UserInfo> userInfos = null;
    try{
        userInfos = new ArrayList<UserInfo>();
        String QUERY = "SELECT * FROM "+TABLE_NAME;
        Cursor cursor = db.rawQuery(QUERY, null);
        if(!cursor.isLast())
        {
            while (cursor.moveToNext())
            {
                UserInfo userInfo = new UserInfo();
                userInfo.setId(cursor.getInt(0));
                userInfo.setName(cursor.getString(1));
                userInfo.setUsername(cursor.getString(2));
                userInfo.setEmail(cursor.getString(3));
                userInfo.setZipcode(cursor.getString(4));
                userInfo.setStreet(cursor.getString(5));
                userInfo.setSuite(cursor.getString(6));
                userInfo.setPhone(cursor.getString(7));
                userInfo.setWebsite(cursor.getString(8));
                userInfo.setCompany(cursor.getString(9));
                userInfo.setPhnname(cursor.getString(10));
                userInfos.add(userInfo);
            }
        }
        db.close();
    }catch (Exception e){
        Log.e("error",e+"");
    }
    return userInfos;
}

@Override
public int getAllInfoCount() {
    int num = 0;
    SQLiteDatabase db = this.getReadableDatabase();
    try{
        String QUERY = "SELECT * FROM "+TABLE_NAME;
        Cursor cursor = db.rawQuery(QUERY, null);
        num = cursor.getCount();
        db.close();
        return num;
    }catch (Exception e){
        Log.e(TAG,"error"+e.getMessage());
    }
    return 0;
}

}

 List fragment:--

 public class UserListFragment extends Fragment {

private static final String TAG = UserListFragment.class.getSimpleName();
@InjectView(R.id.mRecyclerView)
RecyclerView mRecyclerView;
UserInfoRecylerViewAdapter userInfoRecylerViewAdapter;
private List<UserInfo> userInfoList = new ArrayList<>();
private RequestQueue mQueue;
private Context _context;
private DataBaseHandler dataBaseHandler;
private  ArrayList<UserInfo> userInfoArrayList;


@Override
public void onCreate(@Nullable Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
}

public static UserListFragment newInstance() {
    UserListFragment fragment = new UserListFragment();
    return fragment;
}

@Override
public void onStart() {
    super.onStart();
}

@Override
public void onAttach(Context context) {
    super.onAttach(context);
    _context = (FragmentActivity) context;

}

@Nullable
@Override
public View onCreateView(LayoutInflater inflater, @Nullable ViewGroup container, @Nullable Bundle savedInstanceState) {
    return inflater.inflate(R.layout.content_main, container, false);
}

@Override
public void onViewCreated(View view, @Nullable Bundle savedInstanceState) {
    super.onViewCreated(view, savedInstanceState);
    ButterKnife.inject(view);
    userInfoArrayList=new ArrayList<>();
    dataBaseHandler = new DataBaseHandler(getActivity());
    mRecyclerView = (RecyclerView) view.findViewById(R.id.mRecyclerView);
}

@Override
public void onActivityCreated(@Nullable Bundle savedInstanceState) {
    super.onActivityCreated(savedInstanceState);
    _setRecylerView();
    if (dataBaseHandler.getAllInfoCount() == 0) {
        requestData(AppConstants.BASE_URL);
    } else {
        userInfoArrayList=dataBaseHandler.getAllInfo();
        Log.v(TAG,"userInfoArrayList"+userInfoArrayList.size());
        userInfoRecylerViewAdapter.addData(userInfoArrayList);
    }
}

private void _setRecylerView() {

    mRecyclerView.setLayoutManager(new LinearLayoutManager(getActivity()));
    mRecyclerView.setItemAnimator(new DefaultItemAnimator());
    userInfoRecylerViewAdapter = new UserInfoRecylerViewAdapter(_context, userInfoList);
    mRecyclerView.setAdapter(userInfoRecylerViewAdapter);
    RecyclerView.ItemDecoration itemDecoration =
            new DividerItemDecoration(getActivity(), LinearLayoutManager.VERTICAL);
    mRecyclerView.addItemDecoration(itemDecoration);
}

private void requestData(String baseUrl) {
    StringRequest stringRequest = new StringRequest(baseUrl,
            new Response.Listener<String>() {
                @Override
                public void onResponse(String response) {
                    Log.v(TAG, "getting response" + response.toString());
                    try {

                        JSONArray userInfoArray = new JSONArray(response);
                        for (int i = 0; i < userInfoArray.length(); i++) {

                            JSONObject obj = userInfoArray.getJSONObject(i);
                            UserInfo userInfo = new UserInfo();

                            //getting employee object parsing

                            userInfo.setId(obj.getInt("id"));
                            userInfo.setName(obj.getString("name"));
                            userInfo.setUsername(obj.getString("username"));
                            userInfo.setEmail(obj.getString("email"));
                            userInfo.setPhone(obj.getString("phone"));
                            userInfo.setWebsite(obj.getString("website"));
                            Log.v(TAG, "getting parse response" + obj.getInt("id"));

                            //getting address object parsing

                            if (obj.has("address")) {
                                JSONObject address = obj.getJSONObject("address");
                                userInfo.setStreet(address.getString("street"));
                                userInfo.setSuite(address.getString("suite"));
                                userInfo.setZipcode(address.getString("zipcode"));
                                Log.v(TAG, "getting city" + address.getString("street"));
                                if (address.has("geo")) {
                                    JSONObject geo = address.getJSONObject("geo");
                                    userInfo.setLat(geo.getLong("lat"));
                                    userInfo.setLng(geo.getLong("lng"));
                                    Log.v(TAG, "getting geo" + geo.getLong("lat"));
                                }
                            }

                            //getting comapny object parsing

                           if (obj.has("company")){
                               JSONObject company = obj.getJSONObject("company");
                               userInfo.setPhnname(company.getString("name"));
                               userInfo.setCatchPhrase(company.getString("catchPhrase"));
                               userInfo.setBs(company.getString("bs"));
                               Log.v(TAG, "getting company" + company.getString("name"));
                           }
                            dataBaseHandler.addInfo(userInfo);

                        }

                    } catch (JSONException e) {
                        e.printStackTrace();
                    }
                }
            },
            new Response.ErrorListener() {
                @Override
                public void onErrorResponse(VolleyError error) {
                    Log.v(TAG, "getting error response" + error.toString());
                }
            });
    RequestQueue requestQueue = Volley.newRequestQueue(getActivity());
    requestQueue.add(stringRequest);

}

@Override
public void onDestroyView() {
    super.onDestroyView();
    ButterKnife.reset(this);

}

}

This does not answer the question. – CL. Nov 21 '16 at 09:37 — CL., Nov 21 '16 at 09:37

Android: how to insert data faster in sqlite

2 Answers2