“Bulk Insert” for SQLite does not exist. However, there are some tricks to speed up bulk loading of SQLite data. In this post, I am going to show you some techniques that I’ve learned to load data as fast as possible into a SQLite database.
First, every SQL statement ran on SQLite is ran under a transaction even if you don’t specify one. When loading thousands of records, this can be very taxing and slow things down very fast. As a result, you’ll want to load all records under one transaction so that the SQLite DBMS will not start/commit a transaction for every row.
Second, use prepared statements and/or reuse your SQLiteCommand objects. What you do is dependent on what technology you are using SQLite with (e.g. Java for Android or ADO.NET). Rebuilding objects for every record inserted is very taxing. So you’ll want to build the object once, then pass values to it for each row.
In summary, do the following for quick “bulk inserts”:
1) Use 1 transaction to load all records.
2) Reuse command/prepared statements to insert each row.
Here’s a snippet on how to bulk insert in Java for Android:
db.beginTransaction();
try {
SQLiteStatement insert = null;
insert = db.compileStatement("INSERT OR REPLACE INTO \"MyTable\" ("
+ "\"MyColumnName\") VALUES (?)");
for (i = 0; i++; i < 10000)
{
insert.bindLong(1, i);
insert.execute();
insert.clearBindings();
}
db.setTransactionSuccessful();
}
catch (Exception e) {
String errMsg = (e.getMessage() == null) ? "bulkInsert failed" : e.getMessage();
Log.e("bulkInsert:", errMsg);
}
finally {
db.endTransaction();
}
Here’s an example of how to bulk insert in VB .NET:
Using sqliteConn As SQLiteConnection = New SQLiteConnection("Data Source=c:\test.s3db")
Dim sqliteTran As SQLiteTransaction = Nothing
Try
Dim sql As StringBuilder = New StringBuilder()
sql.Append("INSERT INTO ""MyTable""(""MyColumnName"") VALUES (:MyColumnName);")
Dim cmdSQLite As SQLiteCommand = sqliteConn.CreateCommand()
With cmdSQLite
.CommandType = CommandType.Text
.CommandText = sql.ToString()
cmdSQLite.Parameters.Add(":MyColumnName", DbType.Int64)
End With
Dim i As Integer = 0
While i < 10000
cmdSQLite.Parameters(":MyColumnName").Value = i
cmdSQLite.ExecuteNonQuery()
i += 1
End While
sqliteTran.Commit()
Catch ex As Exception
'attempt to rollback the transaction
Try
sqliteTran.Rollback()
Catch ex2 As Exception
'do nothing
End Try
'rethrow the exception
Throw (ex)
End Try
End Using
Both examples insert 10,000 records. Notice that we explicitly kick off one transaction for all inserts and commit it after we are finished inserting. Also notice that in both examples, one command/prepared statement is built and reused for all 10,000 inserts. There are some other SQLite tweaks you can do to slightly speed up performance, but the 2 mentioned will give you the most gains without having to get too technical with the SQLite DBMS engine.