我该如何回收我SqliteCommand加快这一sqlite的大容量插入（IOS）？ [英] How can I recycle my SqliteCommand to speed up this Sqlite bulk insert (iOS)?

问题描述

我用下面散装（一次1000行）插入30000行代码。它毕竟是没有那么快，因为它可以。在这个例子中如何提高SQLite的性能？我可以看到他们正在创建 SqliteCommand 只有一次，然后reycle它被重置并清除绑定。但是，我找不到在iOS / MonoTouch的的apropriate方法。没有复位（）或 ClearBindings（）或其他任何类似的寻找。

 使用（VAR oConn =新SqliteConnection（数据源=+ DB_NAME））
 {
 oConn.Open（）; 
 
 //整个包住批量插入一个块，使其更快，否则每次插入一个交易将被创建。 
 //注意，这是由BEGIN TRANSACTION，这将提高内存的使用了很多differen！ 
 SqliteCommand oCmd =新SqliteCommand（BEGIN，oConn）; 
 oCmd.ExecuteNonQuery（）; 
 oCmd.Dispose（）; 
 
的foreach（MyObj中oObj在aMyObjects）
 {
 oCmd =新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 oMyO​​bj.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 =新SqliteCommand（END，oConn）; 
 oCmd.ExecuteNonQuery（）; 
 oCmd.Dispose（）; 
 
 oConn.Close（）; 
 oConn.Dispose（）; 
} 
  

解决方案

尝试更改您的代码如下：

 使用（VAR oConn =新SqliteConnection（数据源=+ DB_NAME） ）
 {
 oConn.Open（）; 
 
 //整个包住批量插入一个块，使其更快，否则每次插入一个交易将被创建。 
 //注意，这是由BEGIN TRANSACTION，这将提高内存的使用了很多differen！ 
 SqliteCommand oCmd =新SqliteCommand（BEGIN，oConn）; 
 oCmd.ExecuteNonQuery（）; 
 oCmd.Dispose（）; 
 
 oCmd =新SqliteCommand（INSERT INTO LocalObjects（INTID，intParentID，intObjectType，则strName，dtModified VALUES（@intID，@intParentID，@intObjectType，@strName，@dtModified），oConn）; 
 
 //< 
 id.ParameterName =@intID; 
你所有的参数取代。
 VAR ID = oCmd.CreateParameter（）的做到这一点oCmd.Parameters.Add（ID）; 
 //< /做到这一点的所有参数取代。
 
的foreach（MyObj中oObj在aMyObjects）
的{
 //<用于所有参数取代。
的做到这一点id.Value = oMyObj.ID; 
 //<用于所有参数取代。
 $ b的/做到这一点$ b oCmd.ExecuteNonQuery（）; 
} 
 
 oCmd.Dispose（）; 
 
 oCmd =新SqliteCommand（END，oConn）; 
 oCmd.ExecuteNonQuery（）; 
 oCmd.Dispose（）; 
 
 oConn.Close（）; 
 oConn.Dispose（）; 
} 
  

基本上，在每个循环，现在你只需要改变，而不是构建一个全新的查询参数的值。但是，我不知道，是否你的表现真的会从中受益。你需要尝试。

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 How do I improve the 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 (  );
}

解决方案

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.

这篇关于我该如何回收我SqliteCommand加快这一sqlite的大容量插入（IOS）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！