How can I recycle my SqliteCommand to speed up this Sqlite bulk insert (iOS)?
Try to change your code to the following:
using ( var oConn = new SqliteConnection ( "Data Source=" + DB_NAME ) ){ oConn.Open ( ); // Wrap the whole bulk insertion into one block to make it faster, otherwise one transaction per INSERT would be created. // Note that this is differen from "BEGIN TRANSACTION" which would increase memory usage a lot! SqliteCommand oCmd = new SqliteCommand ( "BEGIN", oConn ); oCmd.ExecuteNonQuery ( ); oCmd.Dispose ( ); oCmd = new SqliteCommand ( "INSERT INTO LocalObjects ( intID, intParentID, intObjectType, strName, dtModified VALUES (@intID, @intParentID, @intObjectType, @strName, @dtModified)", oConn ); // <do this for all of your parameters>. var id = oCmd.CreateParameter(); id.ParameterName = "@intID"; oCmd.Parameters.Add(id); // </do this for all of your parameters>. foreach ( MyObj oObj in aMyObjects ) { // <do this for all of your parameters>. id.Value = oMyObj.ID; // </do this for all of your parameters>. oCmd.ExecuteNonQuery ( ); } oCmd.Dispose(); oCmd = new SqliteCommand ( "END", oConn ); oCmd.ExecuteNonQuery ( ); oCmd.Dispose ( ); oConn.Close ( ); oConn.Dispose ( );}
Basically, in each loop now you just change the values of the parameters, instead of constructing a whole new query. However, I am not sure, whether your performance will really benefit from it. You need to try that.
You can also try to use a stored procedure for inserting rows. It should be faster than inline statements especially for inserting so many rows.