SQLite Java Insert - Many smaller inserts versus fewer, larger inserts

Question

I have a database thread that sorts all insert queries into a single transaction every 500ms. Currently, that application does a single insert for every piece of data. Would it be more efficient or less efficient to sort those many insert statements into alike inserts and do a fewer amount of inserts with a lot more elements? I'm trying to reduce the amount of CPU power needed for the inserts.

IE (But in actuality it's on a much larger scale with many more elements)

Start Transaction
INSERT (A,B,C) INTO TABLE VALUES(1,2,3)
INSERT (A,B,C) INTO TABLE VALUES(4,5,6)
INSERT (A,B,C) INTO TABLE VALUES(7,8,9)
INSERT (A,B,C) INTO TABLE VALUES(10,11,12)
INSERT (A,B,C) INTO TABLE VALUES(13,14,15)
INSERT (A,B,C) INTO TABLE2 VALUES(1,2,3)
INSERT (A,B,C) INTO TABLE2 VALUES(4,5,6)
INSERT (A,B,C) INTO TABLE2 VALUES(7,8,9)
INSERT (A,B,C) INTO TABLE2 VALUES(10,11,12)
INSERT (A,B,C) INTO TABLE2 VALUES(13,14,15)
End Transaction

Versus

Start Transaction
INSERT(A,B,C) INTO TABLE VALUES((1,2,3),(4,5,6),(7,8,9),(10,11,12),(13,14,15))
INSERT(A,B,C) INTO TABLE2 VALUES((1,2,3),(4,5,6),(7,8,9),(10,11,12),(13,14,15))
End Transaction

Code as requested:

public void dbCallInsert(List<String> query) {
    // Good practice to create a new statement and result set instead of reusing
    Statement stmt = null;
    Connection conn = null;

    try {
        // Fetch the query and the request out of the QueryRequest object
        conn = cpds.getConnection();
        conn.setAutoCommit(false);
        stmt = conn.createStatement();
        String temp;

        for (String a: query) {                    
            stmt.execute(a);
        }

        conn.commit();
        stmt.close();
        conn.close();

    } catch (Exception e) {

        errorLog.error("Failed to issue database command " + query + ": " + e);

    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                errorLog.error("Failed to close JDBC statement.");
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                errorLog.error("Failed to close JDBC connection.");
            }
        }
    }
}

Can we see that java code you're using for the insert? Are you using a `Statement` (or `PreparedStatement`)? — mypetlion, Feb 26 '18 at 17:15

score 1 · Accepted Answer · answered Feb 26 '18 at 17:29

Moderately borrowed from this answer to Is it possible to insert multiple rows at a time in an SQLite database?.

Since both of your INSERT blocks are wrapped in a TRANSACTION there is little difference. See this comment from the SQLite Mailing List:

On Thu, Feb 23, 2012 at 8:25 AM, Petite Abeille wrote:

On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote:

. I was wondering if I could insert them using a single INSERT query

Ah, also, there is not much benefit in using a compound insert.

You could as well simply insert all your values in one transaction and be done.

On the other hand, the forthcoming 3.7.11 release seems to support multi-valued insert statements.

http://www.sqlite.org/draft/releaselog/3_7_11.html

The new multi-valued insert is merely syntactic suger for the compound insert. There is no performance advantage one way or the other.

SQLite Java Insert - Many smaller inserts versus fewer, larger inserts

1 Answers1