Re: Extremely Slow Inserts?

[Eric Sender]

To answer my own question, you need to force SQLite to do all the inserts in the same transaction, so adding:

db.exec("BEGIN");
...
...
db.exec("COMMIT");

Did the trick. Very fast now.


On Sunday, February 24, 2013 12:33:35 PM UTC-8, Eric Sender wrote:

First off, I want to say this wrapper seems very cool and I am excited to use it. I created some test code to play with the main functions but I am finding my insertion statements are executing very slowly and I was wondering what you think could be causing this and/or how to get over this.

I have worked with other databases like MySQL that seemed pretty instant when you do lots of inserts in a row; This takes about 2-3 minutes to insert 1000 integers into a table...

SqliteTest.java:

package sqlitetest;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import com.almworks.sqlite4java.*;

public class SqliteTest {
    File fDb;
    static SQLiteConnection db;

    public SqliteTest() throws IOException, SQLiteException,
            InterruptedException {
        SQLite.setLibraryPath(System.getProperty("user.dir")
                + "/sqlite4java.library.path");
        fDb = new File(System.getProperty("user.dir") + "/db.db");
        boolean created = fDb.createNewFile();
        db = new SQLiteConnection(fDb).open();// Creates database.
        // if (created) {
        String sql = "DROP TABLE IF EXISTS `orders`; CREATE TABLE orders (order_id INTEGER PRIMARY KEY, quantity BIGINT);";
        db.exec(sql);

        String sql2 = "INSERT INTO `orders` (quantity) VALUES(11); INSERT INTO `orders` (quantity) VALUES(144); ";
        db.exec(sql2);
        // }

        SQLParts sqlParts = new SQLParts(
                "INSERT INTO `orders` (quantity) VALUES(RANDOM());");
        SQLiteConnection conn = null;
        for (int j = 2; j < 1000; j++) {
            try {
                conn = db.exec(sqlParts.toString());
            } catch (SQLiteException e) {
                // for (int i = 0; i < 100 && conn.getErrorCode() != 5; i++) {
                Thread.sleep(10);
                System.out.println("Database found busy at j = " + j
                        + " Error code = " + conn.getErrorCode());
                // conn = db.exec(sqlParts.toString());
                // }
            }
        }

    }

    /**
     * @param args
     * @throws SQLiteException
     * @throws IOException
     * @throws InterruptedException
     */
    public static void main(String[] args) throws SQLiteException, IOException,
            InterruptedException {
        System.out.println(System.getProperty("user.dir"));
        SqliteTest slt = new SqliteTest();
        /**/
        // SQLiteConnection db = new SQLiteConnection(new
        // File("/tmp/database"));
        slt.db.open(true);
        int minimumQuantity = 5;
        SQLiteStatement st = db
                .prepare("SELECT order_id FROM orders WHERE quantity >= ?");
        List<Long> orders = new ArrayList<Long>();
        try {

            st.bind(1, minimumQuantity);
            while (st.step()) {
                orders.add(st.columnLong(0));
            }
        } finally {
            st.dispose();
        }
        // ...
        db.dispose();
        /**/
    }
}

As you can see, my insert statement is just:
INSERT INTO `orders` (quantity) VALUES(RANDOM())

And my table creation is pretty standard:
DROP TABLE IF EXISTS `orders`; CREATE TABLE orders (order_id INTEGER PRIMARY KEY, quantity BIGINT)

I'm not sure if this is a SQLite database issue, the wrapper issue, or if I am doing something the wrong way...

[Igor Sereda]

Eric, thanks for following up on this.

Cheers,

Igor