从.NET代码向SQL Server表中插入数据的最快方法是什么？

Question

如何最快地完成以下操作：

• 一个表，没有我不能预先填充的引用（即有一个引用键，但我已经填好所有数据）
• 大量数据。我们谈论每天来自API的数亿行数据流动
• 请求必须/应该在接近实时的情况下尽快处理（即不要写出到文件以供每天上传）。2秒是正常的最大延迟
• 为数据/应用程序和SQL Server分别使用不同的机器

目前我的做法：

• 聚合至32 * 1024行到数组中，然后将其排队。
• 在2-3个线程中读取队列。使用SqlBulkCopy插入数据库。

我每秒导入约60k-75k行，这还不够，但非常接近。 我希望达到250,000行。

到目前为止，什么都没有真正用上。我得到20％的"网络I/O"阻塞时间，其中一个核心80％的CPU负载。磁盘正在写入7mb-14mb，大部分处于空闲状态。由6个Raptors组成的RAID 10的平均队列长度为.... 0.25。

有人有任何想法如何加快速度吗？更快的服务器（目前是虚拟的，8GB RAM，4核心，数据物理盘通过）。

---

添加一些澄清：

• 这是一个2008 R2 Enterprise SQL Server，运行于2008 R2服务器上。 机器有4个核心，8GB RAM。全部是64位的。80％的负载平均值来自于此机器显示约20％的CPU负载。
• 表是简单的，没有主键，只有一个关系参考上的索引（仪器参考），以及一个唯一的时间戳（在一组仪器内是唯一的，因此不受强制执行）。

表格中的字段包括：时间戳，工具参考（未强制执行的外键），数据类型（char 1，表示发布的数据类型中的某个字符），价格（double）和交易量（int）。可以看出这是一个非常轻的表格。涉及的数据是金融工具的Tick数据。

问题还涉及硬件等方面，主要是因为我没有发现真正的瓶颈。我进行了多次插入，并获得了一些好处，但很小。磁盘、CPU并没有显示出明显的负载，网络io等待高（每秒300毫秒，目前为30％），但这是在同一虚拟化平台上运行JSUT两个服务器，并且有足够的核心来运行所有内容。我基本上开放“购买另一台服务器”的可能性，但我想首先确定瓶颈……特别是考虑到最终我没有抓住瓶颈所在。日志记录无关紧要-批量插入不会作为数据进入数据日志（没有聚集索引）。

竖直分区是否有帮助，例如通过一个字节（tinyint）将工具宇宙划分为16张表格，我因此同时进行多达16次插入？实际上，数据来自不同的交易所，因此我可以根据交易所进行分区。这将是自然的分裂领域（实际上在工具中，但我可以在此处复制此数据）。

---

更多澄清：速度提高了（90k），现在明显受到机器之间的网络IO的限制，这可能是VM切换造成的。

我现在所做的是每32k行进行一次连接，设置一个临时表，在此表格中使用SqlBUlkdCopy进行插入，然后使用一个sql语句将其复制到主表格中-最小化主表格上的任何锁定时间。

大部分等待时间仍在网络IO上。似乎我遇到了VM问题。未来几个月将转向物理硬件；）

- TomTom

---

你确定将数据存储在关系型数据库中是你真正需要的解决方案吗？你不能先将数据存储在某种日志文件中，当你要分析数据时，运行某种聚合过程来提取只与你的数据库相关的信息吗？ - Doc Brown

是的，但我真的不想这样做。这里有很多东西要处理，而且它也是一个很好的编程示例。此外，当从压缩二进制格式提取用于活动使用的日志时，我可能需要尽快将10-20亿行数据处理成关系型数据。只是试图了解极限。 - TomTom

这是特别真实的，因为最后我真的不明白为什么它没有更快地插入。即使一个核心也没有被使用，磁盘也没有，而且我有网络I/O作为等待条件。我没有传输大量数据。这对我来说只是一个小问题要考虑... ;) 并解决。 - TomTom

看起来你在这里取得了很好的进展！请考虑尝试“表参数插入”方法；使用它，我可以在一个适度优化的数据库上轻松地每秒插入数十万行而不会遇到任何问题。 - Ted Spence

Answer 1

如果你每秒管理70k行，到目前为止你非常幸运。但我怀疑这是因为你有一个非常简单的架构。

我无法相信你会在以下环境下询问此类负载： 虚拟服务器 单个阵列 SATA硬盘

网络和CPU是共享的，IO受限制：你不能使用所有资源。 你看到的任何负载统计数据都不太有用。我怀疑你看到的网络负载是两个虚拟服务器之间的流量，如果你解决了这个问题，你将成为IO边界。

在我继续之前，先读一下这篇文章 35K tps的10个教训。他没有使用虚拟机。

假设没有SAN和DR功能，如果你想增加容量，我会这样做。

• 购买2台大型物理服务器，CPU和RAM不太相关，最大RAM，安装x64
• 磁盘+控制器=最快的主轴，最快的SCSI。或者一个非常大的NAS
• 1000MB +网卡
• RAID 10，使用6-10个磁盘为您的数据库仅有一个日志文件
• 剩余磁盘使用RAID 5或RAID 10作为数据文件

参考我们的峰值负载是每小时1200万行（16核，16GB，SAN，x64），但我们的负载存在复杂性。我们还未达到容量极限。

- gbn

---

抱歉，IO是不受限制的。如果使用的硬盘是10K的猛禽，并且只有10个磁盘供 SQL 服务器使用，并且这些磁盘没有被 RAID 控制器的信息占用，单个数组的存储容量并不小。这是一个网络问题 - 等待时间明显指向网络IO ;) - TomTom

Answer 2

根据我在这里阅读的答案，似乎您真正遇到的是硬件问题而不是代码问题。理想情况下，您可以通过提供更多的磁盘I/O或网络带宽，或者在托管数据库的同一虚拟机上运行程序来获得性能提升。

然而，我想分享的是，对于大数据传输，表参数插入确实非常理想；尽管SqlBulkCopy看起来同样快速，但其灵活性显着较低。

我在这里写了一篇关于这个主题的文章：http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/ 总体答案是，您大致需要创建一个表类型：

CREATE TYPE item_drop_bulk_table_rev4 AS TABLE (
    item_id BIGINT,
    monster_class_id INT,
    zone_id INT,
    xpos REAL,
    ypos REAL,
    kill_time datetime
)

然后，您可以创建一个存储过程，直接将表参数中的内容复制到实际表中，这样就减少了中间步骤：

CREATE PROCEDURE insert_item_drops_rev4
    @mytable item_drop_bulk_table_rev4 READONLY
AS

INSERT INTO item_drops_rev4 
    (item_id, monster_class_id, zone_id, xpos, ypos, kill_time)
SELECT 
    item_id, monster_class_id, zone_id, xpos, ypos, kill_time 
FROM 
    @mytable

SQL Server后台代码如下：

DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("item_id", typeof(Int64)));
dt.Columns.Add(new DataColumn("monster_class_id", typeof(int)));
dt.Columns.Add(new DataColumn("zone_id", typeof(int)));
dt.Columns.Add(new DataColumn("xpos", typeof(float)));
dt.Columns.Add(new DataColumn("ypos", typeof(float)));
dt.Columns.Add(new DataColumn("timestamp", typeof(DateTime)));

for (int i = 0; i < MY_INSERT_SIZE; i++) {
    dt.Rows.Add(new object[] { item_id, monster_class_id, zone_id, xpos, ypos, DateTime.Now });
}

// Now we&#039;re going to do all the work with one connection!
using (SqlConnection conn = new SqlConnection(my_connection_string)) {
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("insert_item_drops_rev4", conn)) {
        cmd.CommandType = CommandType.StoredProcedure;

        // Adding a "structured" parameter allows you to insert tons of data with low overhead
        SqlParameter param = new SqlParameter("@mytable", SqlDbType.Structured);
        param.Value = dt;
        cmd.Parameters.Add(param);
        cmd.ExecuteNonQuery();
    }
}

- Ted Spence

Answer 3

所有都很慢。

一段时间以前，我们解决了一个类似的问题（将成千上万个价格数据插入到数据库中，我记得每次大约是50K，而且我们有8个时间段都在00:00发生冲突，所以大约有400K条记录），对于我们来说它运行得非常快（MS SQL 2005）。想象一下今天它将如何工作（SQL 2012）：

<...init...>
if(bcp_init(m_hdbc, TableName, NULL, NULL, DB_IN) == FAIL)
    return FALSE;

int col_number = 1;

// Bind columns
if(bcp_bind(m_hdbc, (BYTE *)&m_sd.SymbolName, 0, 16, (LPCBYTE)"", 1, 0, col_number++) == FAIL) return FALSE;
if(bcp_bind(m_hdbc, (BYTE *)&m_sd.Time, 0, 4, 0, 0, 0, col_number++) == FAIL) return FALSE;
if(bcp_bind(m_hdbc, (BYTE *)&m_sd.Open, 0, 8, 0, 0, 0, col_number++) == FAIL) return FALSE;
if(bcp_bind(m_hdbc, (BYTE *)&m_sd.High, 0, 8, 0, 0, 0, col_number++) == FAIL) return FALSE;
if(bcp_bind(m_hdbc, (BYTE *)&m_sd.Low, 0, 8, 0, 0, 0, col_number++) == FAIL) return FALSE;
if(bcp_bind(m_hdbc, (BYTE *)&m_sd.Close, 0, 8, 0, 0, 0, col_number++) == FAIL) return FALSE;
if(bcp_bind(m_hdbc, (BYTE *)&m_sd.Volume, 0, 8, 0, 0, 0, col_number++) == FAIL) return FALSE;


<...save into sql...>
BOOL CSymbolStorage::Copy(SQL_SYMBOL_DATA *sd)
{
    if(!m_bUseDB)
        return TRUE;

    memcpy(&m_sd, sd, sizeof(SQL_SYMBOL_DATA));

    if(bcp_sendrow(m_hdbc) != SUCCEED)
        return FALSE;

    return TRUE;
}

- Dima

Answer 4

这个表上有哪些索引是可以不用的吗？编辑：我在您正在打字的时候问。

是否可以将价格转换为整数，然后在查询中除以1000或其他数值？

- Tim

---

实际上，有可能将价格作为 int 和 tinyint 进行编码 - 这是可以考虑的。由于价格没有索引，这会产生很大的差异吗？尽管如此，还是要试一试。 - TomTom

.NET客户端在哪里执行？ - Tim

同一台机器，不同的虚拟机。很快将要转移到物理硬件上——并非出于性能原因，而是因为我在虚拟机中遇到了时钟“漂移”问题。如果你得到的数据具有25毫秒时间戳增量，并且获取数据的库将其标记为“可疑”的波动时间戳延迟，那么这将是一个大问题。 - TomTom

不确定你的计划是什么，但如果客户端和服务器进程在不同的机器上运行，那么当然，局域网延迟可能会成为最大的性能瓶颈，尽管你可以通过调整数据行大小来使数据包的使用最为高效，从而提高性能。对于高速批量加载，最好让客户端和服务器在同一台机器上运行。根据我的经验，客户端和服务器上总是有足够的CPU，而磁盘子系统和/或传输层是减速发生的地方。 - Tim

Answer 5

你尝试过在表中添加主键吗？这样会提高速度吗？

还有一种基于集合的方法可以使用计数表从http://www.sqlservercentral.com/articles/T-SQL/62867/导入CSV数据（接近底部，需要免费注册但值得）。

你可以尝试一下，并测试它的性能...使用一个小的、正确索引的计数表。

- Ruskin

---

未添加pk...将尝试添加。CSV文件有问题，我不希望应用程序能够操纵服务器文件系统。 - TomTom

通常情况下，添加任何形式的索引或主键都会降低插入速率。 - andora

Answer 6

您是否可以使用水平分区？请参见：http://msdn.microsoft.com/en-us/library/ms178148.aspx和http://msdn.microsoft.com/en-us/library/ms188706.aspx

您可能还想查看这个问题，并可能更改恢复模型： Sql Server 2008 Tuning with large transactions (700k+ rows/transaction)

一些问题： 您正在使用哪个版本的SQL Server？

为什么一个核心占用了80％？这可能是瓶颈，因此值得调查。

您正在使用哪个操作系统，它是64位吗？

- Bravax

---

在回应中编辑问题。请注意，80％的核心是从系统负载取得的平均值。该机器有4个核心，并显示20％的CPU负载平均值。 - TomTom