主页 / coding / 问题 / 78699617
Accepted
Joshua
Asked: 2024-07-03 07:57:12 +0800 CST

通过 SqlDataRecord 一次往返将多行导入临时表

  • 772

这个问题刚刚开始在分析器中出现。我们得到的结果如下：

using (var cmd = connection.CreateCommand()) 
{
    cmd.CommandText = "INSERT INTO #Ids VALUES (@0);";
    cmd.Parameters.Add(cmd.CreateParameter() { ParameterName = "@0", DbType = GetDbType() });

    cmd.Prepare();

    foreach (var value in values)
    {
        ((IDbDataParameter)cmd.Parameters[0]).Value = value;
        cmd.ExecuteNonQuery();
    }
}

我们正在研究通过减少往返次数来加快速度的可能性。在这里使用 DataTable 会很麻烦，但有一种类型SqlDataRecord似乎适合，所以我们应该能够做这样的事情：

using (var cmd = connection.CreateCommand()) 
{
    cmd.CommandText = "INSERT INTO #Ids SELECT * FROM @0;";

    var param  = cmd.CreateParameter();
    param.ParameterName = "@0";
    // FIXME more properties
    cmd.Parameters.Add(param);
    param.Value = values.Cast<T>().Select((x) => {
                             // FIXME Create new SqlDataRecord
                         }.ToList();

    cmd.ExecuteNonQuery();
}

与其他示例不同的是，这里的主要困难在于T；此代码是动态的，接收大量可能的类型；因此CREATE TYPE无法工作。不可能为每种可能的 VARCHAR 长度创建一个类型。

我该如何填写上面的 FIXME 注释？

相关问题，没有好的答案：当我有一个很大的 ID 列表时，如何在 SQL Server 中创建临时表

评论中有人要求看大图。这里确实没有很好的办法通过缩小范围来获取更多信息。代码库中只有一个例程副本，用于设置所有 SQL 调用（即存储过程）的参数，就是这样。

行数的比例因子是长尾的；平均行数小于 10，但最大大小非常大，并且超过一千次的调用经常发生。因此，我们加载一个临时表；并且在转换为执行循环以外的操作时，foreach我们仍然加载一个临时表。

c#

2 个回答

  1. 一个完整工作的例子。

    using var connection = new SqlConnection(@"...");
connection.Open();
int[] values = [1, 2, 3, 4, 5];

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "CREATE TYPE dbo.Ids AS TABLE (Id INT );";
    cmd.ExecuteNonQuery();    
}

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "CREATE TABLE #Ids (Id INT);";
    cmd.ExecuteNonQuery();    
}

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "INSERT INTO #Ids SELECT * FROM @0;";

    var param = cmd.CreateParameter();
    param.ParameterName = "@0";
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "dbo.Ids";

    cmd.Parameters.Add(param);

    var record = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int));

    param.Value = values.Cast<int>()
        .Select(x =>
        {
            //var record = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int));
            record.SetInt32(0, x);
            return record;
        })
        //.ToList()
        ;

    var affectedRows = cmd.ExecuteNonQuery();
    Console.WriteLine($"Rows inserted: {affectedRows}");
}

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "SELECT COUNT(*) FROM #Ids;";
    var count = cmd.ExecuteScalar();
    Console.WriteLine($"Count: {count}");
}
    • 1
  2. Best Answer

    听起来 TVP 可能不太适合您。

    但是您可以使用临时表SqlBulkCopy，这比单独插入要快得多。

    您可以传递SqlBulkCopy或DataTable。DbDataReader前者创建起来很简单，但占用的内存很大。您可以使用免费的FastMember 库简单地创建一个读取器。

    using var conn = new SqlConnection(GetConnectionStringFromSomewhere());
await conn.OpenAsync();
var createTable = $"CREATE TABLE #ids (Id {columnTypeHere} PRIMARY KEY);";
using (var cmd = new SqlCommand(createTable, conn)) 
{
    // do NOT pass any parameters for this command, otherwise it gets wrapped as sp_executesql and the table will be dropped immediately
    // cmd.Prepare() is unnecessary and counter-productive

    await cmd.ExecuteNonQueryAsync();
}

using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock))
using (var reader = ObjectReader.Create(data)) 
{
    bulk.ColumnMappings.Add("Id", "Id");
    bulk.DestinationTableName = "#ids";
    await bulk.WriteToServerAsync(reader);
}

using (var cmd = new SqlCommand("someStoredProcedure", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(  // etc
    await cmd.ExecuteNonQueryAsync();
}
    • 1

相关问题