Sqlite batch INSERT is slow

Question

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;
    }

}

http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite — pvg, May 29 '16 at 21:11
@Markus everything is represented as String in json. DB contains different field types: `CREATE TABLE Products (PrdID INTEGER, Barcode TEXT, Brand TEXT, PrdName TEXT, Amount REAL, UnitsID INTEGER, SourcesIDs TEXT, CategoriesIDs TEXT, PrdImgURL TEXT, SellType INTEGER, ImageID INTEGER)` — Andrey Rankov, May 29 '16 at 21:30
aha, and also all values are predefined, including ID's, right? — Andrei Mărcuţ, May 29 '16 at 21:32
Android is for personal devices, you may be reaching the limits. How about syncadapter to a server with connection pooling. I think some kind of online solution may be more suitable. Can you tell more about how this app is used? Handheld devices to do inventory? Is a network connection available? — Pomagranite, May 29 '16 at 21:33
@Markus what do you mean? db contains ~60K values, i'm inserting/replacing new values there — Andrey Rankov, May 29 '16 at 21:37
Can you not ship your Application with the database already populated? — Clive Seebregts, May 29 '16 at 21:39
@Pomagranite yes, using prepopulated db with rest service will be more nice, but currently need to deal with that — Andrey Rankov, May 29 '16 at 21:41
@CliveSeebregts off course i can. but i need to update it daily. update json is different, depends on date when db was updated before (because i don't know, when user will launch the app next time). it may contain up to 10x20000 records. — Andrey Rankov, May 29 '16 at 21:44
what I mean is that you don't use an internal DB ID field which would be require a read from the DB whilst doing write operations, as such would fragment your write-only transaction into write-read-write-read chunks instead — Andrei Mărcuţ, May 29 '16 at 21:44

score 0 · Accepted Answer · answered May 30 '16 at 07:12

0

There isn't much you can to speed up this code; handling 200,000 rows on a mobile device always is slow. (Just doing the flash I/O for that amount of data will take lots of time.)

If possible, ship the complete database with your app.

If you need to get the data from elsewhere, try to replace the JSON file with a database file, which can be ATTACHed to your main database and just copied over:

INSERT INTO main.MyTable SELECT * FROM downloadDB.MyTable;

answered May 30 '16 at 07:12

CL.

173,858
17
217
259

Ok, it seems to be most acceptable solution. But my update db file contain some data, that already exists in main database. For example, main db table contains two fields with values: 1-a, 1-b, 1-c and update table contains 1-a, 1-b, 1-d. Resulting table must contain 1-a, 1-b, 1-c, 1-d. How can i do it? – Andrey Rankov May 31 '16 at 16:57
That would be a different question. – CL. May 31 '16 at 20:26
please answer, if you can http://stackoverflow.com/questions/37551680/delete-same-values-from-another-db-table – Andrey Rankov May 31 '16 at 20:31

Sqlite batch INSERT is slow

1 Answers1