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

Question

I'm using the code below to bulk insert a 30000 rows (1000 rows at a time). Still it is not as fast as it could be. In this example Improve INSERT-per-second performance of SQLite? I can see that they are creating the SqliteCommand only once and then reycle it be resetting it and clearing the bindings. However, I cannot find the apropriate methods on iOS/Monotouch. There is no Reset() or ClearBindings() or anything else looking similar.

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 (  );

    foreach ( MyObj oObj in aMyObjects )
    {
        oCmd = new SqliteCommand ( "INSERT INTO LocalObjects ( intID, intParentID, intObjectType, strName, dtModified VALUES (@intID, @intParentID, @intObjectType, @strName, @dtModified)", oConn );
        oCmd.Parameters.AddWithValue ( "@intID", oMyObj.ID );
        oCmd.Parameters.AddWithValue ( "@intParentID", oMyObj.ParentID );
        oCmd.Parameters.AddWithValue ( "@intObjectType", ( int ) oMyObj.Type );
        oCmd.Parameters.AddWithValue ( "@strName", oMyObj.Name );
        oCmd.Parameters.AddWithValue ( "@dtModified", oMyObj.Modified );
        oCmd.ExecuteNonQuery (  );
        oCmd.Dispose (  );
    }

    oCmd = new SqliteCommand ( "END", oConn );
    oCmd.ExecuteNonQuery (  );
    oCmd.Dispose (  );

    oConn.Close (  );
    oConn.Dispose (  );
}

Placencia

Answer 1

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.

Israelitish

Good approach. It'll go even faster if you wrap the command in a transaction. – Loeb

Haven't found time yet to try it. I will accept your answer once I tested it! – Placencia

Tested it. Your version processes my data in 37 seconds, my version needed 73 seconds. Thanks! Only thing I'm concerned: memory usage seems to be higher with your solution. Any idea why? – Placencia

No, I have no idea, I am sorry. How do you come to that conclusion? – Israelitish

I see it from Instruments. It's using some MB more. – Placencia

love it! i was looking for a how to on transaction command for sqlite. – Tupelo

Answer 2

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.

Fissiparous

sqlite don't have stored procedures – Nightly