我使用下面的代码批量插入 30000 行(一次 1000 行)。但它仍然没有达到应有的速度。在这个例子中提高 SQLite 的每秒插入性能?我可以看到他们正在创建SqliteCommand
仅一次,然后通过重置并清除绑定来回收它。但是,我在 iOS/Monotouch 上找不到合适的方法。没有Reset()
or ClearBindings()
或任何其他看起来类似的东西。
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 ( );
}
尝试将您的代码更改为以下内容:
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 ( );
}
基本上,在每个循环中,您现在只需更改参数的值,而不是构造一个全新的查询。然而,我不确定你的表现是否真的会从中受益。你需要尝试一下。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)