How can I recycle my SqliteCommand to speed up this Sqlite bulk insert (iOS)? How can I recycle my SqliteCommand to speed up this Sqlite bulk insert (iOS)? sqlite sqlite

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.