logo标签列表

大量数据下的慢速插入性能问题（SQL Server / C#）

c#sql-server
5
我正在处理实时数字波形的电子设备（每个设备每秒生成大约1000个512字节的数组-我们有12台设备）。我用C#为这些设备编写了一个客户端，大部分工作都正常，没有性能问题。
然而，应用程序的要求之一是存档，Microsoft SQL Server 2010被规定为存储机制（超出我的控制范围）。数据库布局非常简单：每天每个设备都有一个表格（例如，“Archive_Dev02_20131015”）。每个表格都有一个“Id”列、一个“时间戳”列、一个“Data”列（varbinary），还有20个整数列和一些元数据。在“Id”和“timestamp”上有一个聚集主键，另外一个独立的索引在“timestamp”上。我的天真方法是在客户端应用程序中将所有数据排队，然后使用SqlCommand每5秒钟将所有数据插入到数据库中。
基本机制如下所示：
using (SqlTransaction transaction = connection.BeginTransaction()
{
    //Beginning of the insert sql statement...
    string sql = "USE [DatabaseName]\r\n" +
                 "INSERT INTO [dbo].[Archive_Dev02_20131015]\r\n" + 
                 "(\r\n" +
                 "   [Timestamp], \r\n" +
                 "   [Data], \r\n" +
                 "   [IntField1], \r\n" +
                 "   [...], \r\n" +                         
                 ") \r\n" +
                 "VALUES \r\n" +
                 "(\r\n" +
                 "   @timestamp, \r\n" + 
                 "   @data, \r\n" + 
                 "   @int1, \r\n" +
                 "   @..., \r\n" +  
                 ")";

    using (SqlCommand cmd = new SqlCommand(sql))
    {
        cmd.Connection = connection;
    cmd.Transaction = transaction;

    cmd.Parameters.Add("@timestamp", System.Data.SqlDbType.DateTime);
    cmd.Parameters.Add("@data", System.Data.SqlDbType.Binary);
    cmd.Parameters.Add("@int1", System.Data.SqlDbType.Int);

    foreach (var sample in samples)
    {
            cmd.Parameters[0].Value = amples.ReceiveDate;
            cmd.Parameters[1].Value = samples.Data;       //Data is a byte array
            cmd.Parameters[1].Size  = samples.Data.Length;
            cmd.Parameters[2].Value = sample.IntValue1;
             ...

            int affected = cmd.ExecuteNonQuery();

            if (affected != 1)
            {
                throw new Exception("Could not insert sample into the database!");
            }
          }
       }
   }

   transaction.Commit();                
}

简而言之：一批包含一个循环的交易，生成插入语句并执行。但这种方法非常慢。在我的电脑上（i5-2400 @ 3.1GHz、8GB RAM、使用.NET 4.0和SQL Server 2008、2个内部硬盘镜像），保存来自两个设备的数据需要大约2.5秒，因此每5秒保存12个设备的数据是不可能的。相比之下，我编写了一个小的SQL脚本（实际上是从C#与SQL服务器分析器一起运行的代码中提取出来），直接在服务器上执行相同的操作（仍在我的电脑上运行）。
set statistics io on
go

begin transaction
go

declare @i int = 0;

while @i < 24500 begin
SET @i = @i + 1

exec sp_executesql N'USE [DatabaseName]                                                                
INSERT INTO [dbo].[Archive_Dev02_20131015]                                                      
(                                                                                      
   [Timestamp],                                                                        
   [Data],                                                                             
   [int1],                                                                       
    ...                                                    
   [int20]                                                                                
)                                                                                      
VALUES                                                                                 
(                                                                                      
   @timestamp,                                                                         
   @data,                                                                              
   @compressed,                                                                        
   @int1,                                                                           
   ...                                                                  
   @int20,                                                                   

)',N'@timestamp datetime,@data binary(118),@int1 int,...,@int20 int,',
@timestamp='2013-10-14 14:31:12.023',
@data=0xECBD07601C499625262F6DCA7B7F4AF54AD7E074A10880601324D8904010ECC188CDE692EC1D69472329AB2A81CA6556655D661640CCED9DBCF7DE7BEFBDF7DE7BEFBDF7BA3B9D4E27F7DFFF3F5C6664016CF6CE4ADAC99E2180AAC81F3F7E7C1F3F22FEEF5FE347FFFDBFF5BF1FC6F3FF040000FFFF,
@int=0,
...
@int20=0
end

commit transaction

这次我使用了24500次迭代来模拟12个设备，实际上做的是相同的事情（我的想法，但我可能错了 ;)）。 这个查询大约需要2秒钟。 如果我使用与C#版本相同数量的迭代，查询只需要不到1秒钟的时间。

所以我的第一个问题是：为什么在 SQL Server 上运行速度比在 C# 上要快得多？这是否与连接（本地 TCP）有关？

更让我困惑的是，这段代码在生产服务器上运行的速度要慢两倍（IBM BladeCenter，32GB RAM，光纤连接到 SAN，...文件系统操作非常快）。 我尝试查看SQL活动监视器，写入性能从未超过2MB / sec，但这也可能是正常现象。 我对SQL Server完全是新手（事实上与称职的 DBA 相差甚远）。

有什么想法可以让C#代码更具性能？

你尝试过为每个命令删除 USE [DatabaseName] 吗？ - Steve
你可以使用for循环或foreach循环来迭代每个设备。如果你不关心设备运行的顺序，可以尝试在单独的线程上运行并行for循环。 - Steven Wood
6
你可能想要查看 SqlBulkCopy - http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx 。 - juharr
我会查看连接所需的时间。（你有连接字符串吗？）此外，我会研究SQL批量插入以聚合插入语句。 - RoughPlace
1
您每秒生成6兆位（512字节100012台设备）的数据，加上开销等因素，您应该至少拥有一个12兆比特的网络吞吐量连接到SQL Server。如果您只想每5秒中的0.5秒进行插入操作，那么您需要一个具有至少120兆比特/秒可用网络带宽的"管道"到您的SQL服务器。您是否拥有这个？请注意，仅靠网卡是无法解决此问题的，因为它受到客户端和服务器之间最慢/负载最重的设备的限制。 - RBarryYoung
3个回答
12

到目前为止，处理此类数据的最佳方法是使用表值参数和接受数据的存储过程。一个非常简单的示例是使用它的表类型和过程：

CREATE TYPE [dbo].[StringTable]
AS TABLE ([Value] [nvarchar] (MAX) NOT NULL)
GO

CREATE PROCEDURE [dbo].[InsertStrings]
  @Paths [dbo].[StringTable] READONLY
AS
INSERT INTO [dbo].[MyTable] ([Value])
SELECT [Value] FROM @Paths
GO

然后，C#代码应该如下所示（请注意，我已经将其键入到S/O编辑器中，因此可能会有错别字）：

private static IEnumerable<SqlDataRecord> TransformStringList(ICollection<string> source)
{
     if (source == null || source.Count == 0)
     {
         return null;
     }
     return GetRecords(source, 
                       () => new SqlDataRecord(new SqlMetaData("Value", SqlDbType.NVarChar, -1)), 
                       (record, value) => record.SetString(0, value));
}

private static IEnumerable<SqlDataRecord> GetRecords<T>(IEnumerable<T> source, Func<SqlDataRecord> factory, Action<SqlDataRecord, T> hydrator)
{
    SqlDataRecord dataRecord = factory();
    foreach (var value in source)
    {
        hydrator(dataRecord, value);
        yield return dataRecord;
    }
}

private InsertStrings(ICollection<string> strings, SqlConnection connection)
{
    using (var transaction = connection.BeginTransaction())
    {
        using (var cmd = new SqlCommand("dbo.InsertStrings"))
        {
            cmd.Connection = connection;
            cmd.Transaction = transaction;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Paths", SqlDbType.Structured) { Value = TransformStringList(strings) };
            cmd.ExecuteNonQuery();
        }
    }
}

这种方法的速度可以与SqlBulkCopy媲美，但通过运行要更新的内容，它也提供了更好的控制能力，并且让并发处理变得更加容易。

编辑 -> 为了完整起见，此方法适用于SQL Server 2008及以上版本。鉴于没有SQL Server 2010这样的东西，我觉得最好提一下。

你不需要使用存储过程来使用TVPs。如果每秒调用次数较低，它甚至不会提高性能。但是这个概念还是值得肯定的。 - usr
@usr - 不，你不需要，但这是我通常在这种情况下给出的例子。虽然它不会提高性能，但它可以防止数据逻辑在各种代码片段中扩散，并提供一个简单的权限应用点。 - Matt Whitfield
TVPs是处理大批量操作的最佳选择。虽然不一定需要使用存储过程，但考虑到维护方面的好处，为什么不用呢？ - Ashley Pillay
1
我是一名程序员，现在转行做DBA了。以前我会像这样嵌入SQL，但现在我无法忍受其他程序员这样做。正如楼主所提到的，他是SQL的初学者。通常情况下，新手可以让查询返回结果，但不知道如何优化查询。开发人员换工作，表格增长，然后一个1秒的查询需要10分钟，但它被嵌入到一个无法更改的应用程序中... - Ashley Pillay
2
@Jodrell - 这就是同义词的作用。为每天使用不同的表是一个本身设计很差的选择，但同义词确实给了你一个摆脱这个问题的机会。 - Matt Whitfield
显示剩余2条评论
2

在SQL Server中，

CREATE TYPE [dbo].[ArchiveData]
AS TABLE (
    [Timestamp] [DateTime] NOT NULL,
    [Data] [VarBinary](MAX) NOT NULL,
    [IntField1] [Int] NOT NULL,
    [...] [Int] NOT NULL,
    [IntField20] NOT NULL)
GO

那么您的代码应该类似于下面的代码。此代码使用表值参数一次性插入所有待处理数据，是一个单个事务。

请注意省略了缓慢且不必要的USE DATABASE，并使用原始字符串（@""）使代码更易读。

// The insert sql statement.
string sql =
@"INSERT INTO [dbo].[Archive_Dev02_20131015] (
    [Timestamp],
    [Data],
    [IntField1],
    [...],                         
    [IntField20])
 SELECT * FROM @data;";

using (SqlCommand cmd = new SqlCommand(sql))
{
    using (SqlTransaction transaction = connection.BeginTransaction()
    {
        cmd.Connection = connection;
        cmd.Transaction = transaction;
        cmd.Parameters.Add(new SqlParameter("@data", SqlDbType.Structured)
            {
                Value = TransformSamples(samples);
            });

        int affected = cmd.ExecuteNonQuery();
        transaction.Commit();
    }
}

...

private static IEnumerable<SqlDataRecord> TransformSamples(
        {YourSampleType} samples)
{
    var schema = new[]
    {
        new SqlMetaData("Timestamp", SqlDbType.DateTime),
        new SqlMetaData("Timestamp", SqlDbType.VarBinary, -1),
        new SqlMetaData("IntField1", SqlDbType.Int),
        new SqlMetaData("...", SqlDbType.Int),
        new SqlMetaData("IntField20", SqlDbType.Int)
    };

    foreach (var sample in samples)
    {
        var row = new SqlDataRecord(schema);
        row.SetSqlDate(0, sample.ReceiveDate);
        row.SetSqlBinary(1, sample.Data);
        row.SetSqlInt(2, sample.Data.Length);
        row.SetSqlInt(..., ...);
        row.SetSqlInt(24, sample.IntValue19);
        yield return row;
    }
}
您实际上不需要为每行实例化新的SqlDataRecord实例。 - Matt Whitfield
2

我已经通过使用 juharr 在上面的评论中建议的 SqlBulkInsert 解决了我的问题。

我主要依据这篇文章将我的数据转换为可批量插入到数据库的 DataTable：

如何将通用 List/Enumerable 转换为 DataTable？

感谢您所有的回答！

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接