c# - 如何在 10 秒内将 200 万行插入到 sql server 中的两个表中

Question

我目前使用存储过程在 90 到 100 秒内同时将 100 万条记录插入到两个表中。这在我的场景中是 Not Acceptable 。我想找到一种方法将时间缩短到 10 秒以内。

我曾尝试在订单之后插入记录，但速度非常慢 - 大约需要一个小时。然后我尝试使用表值参数将所有记录插入一次。这使时间缩短到 90 - 100 秒。

这是 C# 调用代码:

public Task<int> CreateGiftVoucher(IEnumerable<Gift> vouchersList)
{

    GiftStreamingSqlRecord record = new GiftStreamingSqlRecord(vouchersList);

    foreach (var t in vouchersList)
    {
        Console.WriteLine($"<<<<<gfts>>> {t}");
    }

    try
    {
        var connection = Connection;

        if (connection.State == ConnectionState.Closed) connection.Open();

        string storedProcedure = "dbo.usp_CreateGiftVoucher";

        var command = new SqlCommand(storedProcedure, connection as SqlConnection);
        command.CommandType = CommandType.StoredProcedure;

        var param = new SqlParameter();
        param.ParameterName = "@tblGift";
        param.TypeName = "dbo.GiftVoucherType";   
        param.SqlDbType = SqlDbType.Structured;             
        param.Value = record;

        command.Parameters.Add(param);
        command.CommandTimeout = 60;
        return command.ExecuteNonQueryAsync();                 

    }
    catch (System.Exception)
    {
        throw;
    }
    finally
    {
        Connection.Close();
    }
}

---

这是 GiftStreamingRecord 类

public GiftStreamingSqlRecord(IEnumerable<Gift> gifts) =>  this._gifts = gifts;

public IEnumerator<SqlDataRecord> GetEnumerator()
{
    SqlMetaData[] columnStructure = new SqlMetaData[11];
    columnStructure[0] = new SqlMetaData("VoucherId",SqlDbType.BigInt, 
                useServerDefault: false,  
                isUniqueKey: true, 
                columnSortOrder:SortOrder.Ascending, sortOrdinal: 0);
    columnStructure[1] = new SqlMetaData("Code", SqlDbType.NVarChar, maxLength: 100);
    columnStructure[2] = new SqlMetaData("VoucherType", SqlDbType.NVarChar, maxLength: 50);
    columnStructure[3] = new SqlMetaData("CreationDate", SqlDbType.DateTime);
    columnStructure[4] = new SqlMetaData("ExpiryDate", SqlDbType.DateTime);
    columnStructure[5] = new SqlMetaData("VoucherStatus", SqlDbType.NVarChar, maxLength: 10);
    columnStructure[6] = new SqlMetaData("MerchantId", SqlDbType.NVarChar, maxLength: 100);
    columnStructure[7] = new SqlMetaData("Metadata", SqlDbType.NVarChar, maxLength: 100);
    columnStructure[8] = new SqlMetaData("Description", SqlDbType.NVarChar, maxLength: 100);
    columnStructure[9] = new SqlMetaData("GiftAmount", SqlDbType.BigInt);
    columnStructure[10] = new SqlMetaData("GiftBalance", SqlDbType.BigInt);

    var columnId = 1L;

    foreach (var gift in _gifts)
    {
        var record =  new SqlDataRecord(columnStructure);
        record.SetInt64(0, columnId++);
        record.SetString(1, gift.Code);
        record.SetString(2, gift.VoucherType);
        record.SetDateTime(3, gift.CreationDate);
        record.SetDateTime(4, gift.ExpiryDate);
        record.SetString(5, gift.VoucherStatus);
        record.SetString(6, gift.MerchantId);
        record.SetString(7, gift.Metadata);
        record.SetString(8, gift.Description);
        record.SetInt64(9, gift.GiftAmount);
        record.SetInt64(10, gift.GiftBalance);
        yield return record;
    }
}

---

这是存储过程和 tvp:

CREATE TYPE [dbo].GiftVoucherType AS TABLE (
[VoucherId] [bigint] PRIMARY KEY,
[Code] [nvarchar](100) NOT NULL,
[VoucherType] [nvarchar](50) NOT NULL,
[CreationDate] [datetime] NOT NULL,
[ExpiryDate] [datetime] NOT NULL,
[VoucherStatus] [nvarchar](10) NOT NULL,
[MerchantId] [nvarchar](100) NOT NULL,
[Metadata] [nvarchar](100) NULL,
[Description] [nvarchar](100) NULL,
[GiftAmount] [bigint] NOT NULL,
[GiftBalance] [bigint] NOT NULL
)

GO

CREATE PROCEDURE [dbo].[usp_CreateGiftVoucher]

@tblGift [dbo].GiftVoucherType READONLY
AS

    DECLARE @idmap TABLE (TempId BIGINT NOT NULL PRIMARY KEY, 
                            VId BIGINT UNIQUE NOT NULL)

BEGIN TRY
    BEGIN TRANSACTION CreateGiftVoucher

        MERGE Voucher V 
        USING (SELECT [VoucherId], [Code], [VoucherType], [MerchantId], [ExpiryDate],
            [Metadata], [Description] FROM @tblGift) TB ON 1 = 0
        WHEN NOT MATCHED BY TARGET THEN
        INSERT ([Code], [VoucherType], [MerchantId], [ExpiryDate], [Metadata], [Description])
        VALUES(TB.Code, TB.VoucherType, TB.MerchantId, TB.ExpiryDate, TB.Metadata, TB.[Description])
        OUTPUT TB.VoucherId, inserted.VoucherId INTO @idmap(TempId, VId);

        -- Insert rows into table 'GiftVoucher'
        INSERT GiftVoucher
        (
        GiftAmount, GiftBalance, VoucherId
        )
        SELECT TB.GiftAmount, TB.GiftBalance, i.VId
        FROM @tblGift TB
        JOIN @idmap i ON i.TempId = TB.VoucherId

    COMMIT TRANSACTION CreateGiftVoucher
END TRY
BEGIN CATCH
    ROLLBACK
END CATCH
GO

所有这些都让我在 90 到 100 秒内插入 100 万。我想在 10 秒内完成。

Answer 1

插入大量行的最快方法是使用批量插入(SqlBulkCopy 或其他 API)。我可以看到您正在使用 MERGE。这不能与批量复制一起使用，因此此设计将强制使用表值参数，就像您现在正在使用它们一样。就更多的 CPU 使用率而言，TVP 有点慢。您还可以尝试批量插入临时表，然后使用 MERGE。据我了解，无论如何，TVP 在物理上就是一个临时表。没有真正的流媒体进行。您在 C# 代码中流入其中的所有数据都由服务器简单地插入到一个自动管理的表中。

您所做的 TVP 流式传输 (SqlMetaData) 是正确的。根据我的经验，这是传输 TVP 数据最快的方式。

您将需要并行化。根据经验，对于相当简单的行，在最佳条件下每秒很难超过 100k 行。那时，CPU 在一个核心上饱和。在记录的某些条件下，您可以在多个内核上并行插入。对索引结构有要求。您也可能会遇到锁定问题。解决这些问题的可靠方法是插入到独立的表或分区中。但是，这当然会迫使您更改针对这些表运行的其他查询。

如果您在插入时必须执行复杂的逻辑，您仍然可以插入到新表中，然后在查询时执行逻辑。这需要更多工作并且容易出错，但它可能会让您满足延迟要求。

我希望这些想法能帮助您走上正确的道路。欢迎发表评论。