I'm doing insert into sqlite db from json file. Json contains ~20000 products. Each product field contain ~ 10 categories (added to separate table, CategoryProduct_MM). This gives about 200000 records. This operation took about 30 sec. Is it possible to make it faster?
@Override
protected String doInBackground(Void... voids) {
if (db.isOpen()) {
db.beginTransaction();
String sql_prod = "Insert or Replace into Products (Amount, SourcesIDs, ImageID, PrdID," +
" Barcode, UnitsID, Brand, PrdName, PrdImgURL, SellType, CategoriesIDs)" +
" values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
String sql_cat = "Insert or Replace into Categories (ImageUpdateDate, CategoryID, ParentCatID," +
" ThisItemsOrder, CategoryName) values(?, ?, ?, ?, ?)";
String sql_cat_prod = "Insert or Replace into CategoryProduct_MM (PrdID, CategoryID)" +
" values(?, ?)";
String sql_fts = "Insert or Replace into Products_fts (rowid, PrdID, ConcatSearchField)" +
" values(?, ?, ?)";
insert_prod = db.compileStatement(sql_prod);
insert_cat = db.compileStatement(sql_cat);
insert_cat_prod = db.compileStatement(sql_cat_prod);
insert_fts = db.compileStatement(sql_fts);
try {
for (final Product product : products) {
insertField(1, product.getAmount(), insert_prod);
insertField(2, product.getSourcesIDs(), insert_prod);
insertField(3, product.getImageID(), insert_prod);
insertField(4, product.getPrdID(), insert_prod);
insertField(5, product.getBarcode(), insert_prod);
insertField(6, product.getUnitsID(), insert_prod);
insertField(7, product.getBrand(), insert_prod);
insertField(8, product.getPrdName(), insert_prod);
insertField(9, product.getPrdImgURL(), insert_prod);
insertField(10, product.getSellType(), insert_prod);
insertField(11, product.getCategoriesIDs(), insert_prod);
insert_prod.execute();
String[] id = product.getCategoriesIDs().split(",");
for (int i = 0; i < id.length; i++) {
insertField(1, product.getPrdID(), insert_cat_prod);
insertField(2, id[i], insert_cat_prod);
insert_cat_prod.execute();
}
if (product.getConcatSearchField() != null) {
insertField(1, product.getPrdID(), insert_fts);
insertField(2, product.getPrdID(), insert_fts);
insertField(3, product.getConcatSearchField(), insert_fts);
insert_fts.execute();
}
}
for (Category category : categories) {
insertField(1, category.getImageUpdateDate(), insert_cat);
insertField(2, category.getCategoryID(), insert_cat);
insertField(3, category.getImageUpdateDate(), insert_cat);
insertField(4, category.getParentCatID(), insert_cat);
insertField(5, category.getCategoryName(), insert_cat);
insert_cat.execute();
}
db.setTransactionSuccessful();
listener.onUpdateFinished();
} finally {
db.endTransaction();
db.close();
}
}
return null;
}
private void insertField(int index, String field, SQLiteStatement insert) {
if (field == null) {
insert.bindNull(index);
} else {
insert.bindString(index, field);
}
}
Product.class
package me.superup.updatedb.model;
import com.google.gson.annotations.Expose;
import com.google.gson.annotations.SerializedName;
import javax.annotation.Generated;
@Generated("org.jsonschema2pojo")
public class Product {
@SerializedName("Amount")
@Expose
private String amount;
@SerializedName("SourcesIDs")
@Expose
private String sourcesIDs;
@SerializedName("ImageID")
@Expose
private String imageID;
@SerializedName("Action")
@Expose
private String action;
@SerializedName("PrdID")
@Expose
private String prdID;
@SerializedName("Barcode")
@Expose
private String barcode;
@SerializedName("UnitsID")
@Expose
private String unitsID;
@SerializedName("Brand")
@Expose
private String brand;
@SerializedName("PrdName")
@Expose
private String prdName;
@SerializedName("PrdImgURL")
@Expose
private String prdImgURL;
@SerializedName("SellType")
@Expose
private String sellType;
@SerializedName("CategoriesIDs")
@Expose
private String categoriesIDs;
@SerializedName("ConcatSearchField")
@Expose
private String concatSearchField;
/**
*
* @return
* The amount
*/
public String getAmount() {
return amount;
}
/**
*
* @param amount
* The Amount
*/
public void setAmount(String amount) {
this.amount = amount;
}
/**
*
* @return
* The sourcesIDs
*/
public String getSourcesIDs() {
return sourcesIDs;
}
/**
*
* @param sourcesIDs
* The SourcesIDs
*/
public void setSourcesIDs(String sourcesIDs) {
this.sourcesIDs = sourcesIDs;
}
/**
*
* @return
* The imageID
*/
public String getImageID() {
return imageID;
}
/**
*
* @param imageID
* The ImageID
*/
public void setImageID(String imageID) {
this.imageID = imageID;
}
/**
*
* @return
* The action
*/
public String getAction() {
return action;
}
/**
*
* @param action
* The Action
*/
public void setAction(String action) {
this.action = action;
}
/**
*
* @return
* The prdID
*/
public String getPrdID() {
return prdID;
}
/**
*
* @param prdID
* The PrdID
*/
public void setPrdID(String prdID) {
this.prdID = prdID;
}
/**
*
* @return
* The barcode
*/
public String getBarcode() {
return barcode;
}
/**
*
* @param barcode
* The Barcode
*/
public void setBarcode(String barcode) {
this.barcode = barcode;
}
/**
*
* @return
* The unitsID
*/
public String getUnitsID() {
return unitsID;
}
/**
*
* @param unitsID
* The UnitsID
*/
public void setUnitsID(String unitsID) {
this.unitsID = unitsID;
}
/**
*
* @return
* The brand
*/
public String getBrand() {
return brand;
}
/**
*
* @param brand
* The Brand
*/
public void setBrand(String brand) {
this.brand = brand;
}
/**
*
* @return
* The prdName
*/
public String getPrdName() {
return prdName;
}
/**
*
* @param prdName
* The PrdName
*/
public void setPrdName(String prdName) {
this.prdName = prdName;
}
/**
*
* @return
* The prdImgURL
*/
public String getPrdImgURL() {
return prdImgURL;
}
/**
*
* @param prdImgURL
* The PrdImgURL
*/
public void setPrdImgURL(String prdImgURL) {
this.prdImgURL = prdImgURL;
}
/**
*
* @return
* The sellType
*/
public String getSellType() {
return sellType;
}
/**
*
* @param sellType
* The SellType
*/
public void setSellType(String sellType) {
this.sellType = sellType;
}
/**
*
* @return
* The categoriesIDs
*/
public String getCategoriesIDs() {
return categoriesIDs;
}
/**
*
* @param categoriesIDs
* The CategoriesIDs
*/
public void setCategoriesIDs(String categoriesIDs) {
this.categoriesIDs = categoriesIDs;
}
/**
*
* @return
* The concatSearchField
*/
public String getConcatSearchField() {
return concatSearchField;
}
/**
*
* @param concatSearchField
* The ConcatSearchField
*/
public void setConcatSearchField(String concatSearchField) {
this.concatSearchField = concatSearchField;
}
}
Category.class
public class Category {
@SerializedName("ImageUpdateDate")
@Expose
private String imageUpdateDate;
@SerializedName("CategoryID")
@Expose
private String categoryID;
@SerializedName("ParentCatID")
@Expose
private String parentCatID;
@SerializedName("ThisItemsOrder")
@Expose
private String thisItemsOrder;
@SerializedName("CategoryName")
@Expose
private String categoryName;
/**
*
* @return
* The imageUpdateDate
*/
public String getImageUpdateDate() {
if (imageUpdateDate.equals(""))
return String.valueOf(0);
return imageUpdateDate;
}
/**
*
* @param imageUpdateDate
* The ImageUpdateDate
*/
public void setImageUpdateDate(String imageUpdateDate) {
this.imageUpdateDate = imageUpdateDate;
}
/**
*
* @return
* The categoryID
*/
public String getCategoryID() {
return categoryID;
}
/**
*
* @param categoryID
* The CategoryID
*/
public void setCategoryID(String categoryID) {
this.categoryID = categoryID;
}
/**
*
* @return
* The parentCatID
*/
public String getParentCatID() {
return parentCatID;
}
/**
*
* @param parentCatID
* The ParentCatID
*/
public void setParentCatID(String parentCatID) {
this.parentCatID = parentCatID;
}
/**
*
* @return
* The thisItemsOrder
*/
public String getThisItemsOrder() {
return thisItemsOrder;
}
/**
*
* @param thisItemsOrder
* The ThisItemsOrder
*/
public void setThisItemsOrder(String thisItemsOrder) {
this.thisItemsOrder = thisItemsOrder;
}
/**
*
* @return
* The categoryName
*/
public String getCategoryName() {
return categoryName;
}
/**
*
* @param categoryName
* The CategoryName
*/
public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
}
