SQL Server插入性能

Question

我有一个插入查询，它是这样生成的

INSERT INTO InvoiceDetail (LegacyId,InvoiceId,DetailTypeId,Fee,FeeTax,Investigatorid,SalespersonId,CreateDate,CreatedById,IsChargeBack,Expense,RepoAgentId,PayeeName,ExpensePaymentId,AdjustDetailId) 
VALUES(1,1,2,1500.0000,0.0000,163,1002,'11/30/2001 12:00:00 AM',1116,0,550.0000,850,NULL,@ExpensePay1,NULL); 
DECLARE @InvDetail1 INT; SET @InvDetail1 = (SELECT @@IDENTITY);

这个查询仅针对110K行生成。

执行所有这些查询需要30分钟。

我检查了查询计划，最大的百分比节点是：

57%的查询成本为聚集索引插入，其中包含一个长的xml，我不想发布。

38%的查询成本是Table Spool。

<RelOp AvgRowSize="35" EstimateCPU="5.01038E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Eager Spool" NodeId="80" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0466109">
  <OutputList>
    <ColumnReference Database="[SkipPro]" Schema="[dbo]" Table="[InvoiceDetail]" Column="InvoiceId" />
    <ColumnReference Database="[SkipPro]" Schema="[dbo]" Table="[InvoiceDetail]" Column="InvestigatorId" />
    <ColumnReference Column="Expr1054" />
    <ColumnReference Column="Expr1055" />
  </OutputList>
  <Spool PrimaryNodeId="3" />
</RelOp>

所以我的问题是，有什么我可以做来提高这个事物的速度？在查询之前，我已经运行了ALTER TABLE TABLENAME NOCHECK CONSTRAINTS ALL， 然后在查询之后再次运行 ALTER TABLE TABLENAME NOCHECK CONSTRAINTS ALL 。而这几乎没有减少时间。

现在我正在一个使用SqlCommand对象发送查询的.NET应用程序中运行这些查询。 然后我尝试将SQL命令输出到文件，然后使用sqlcmd执行它，但我没有得到任何关于它执行情况的更新，所以我放弃了。

有任何想法、提示或者帮助吗？

更新：

好的，你们都非常有帮助。解决这个问题的方法有两个。

第一个：

1）我禁用/重新启用了所有外键（比删除它们容易得多）。

ALTER TABLE TableName NOCHECK CONSTRAINT ALL
ALTER TABLE TableName CHECK CONSTRAINT ALL

2) 我禁用/重新启用了索引（这比删除要简单得多）

ALTER INDEX [IX_InvoiceDetail_1] ON [dbo].[InvoiceDetail] DISABLE
ALTER INDEX [IX_InvoiceDetail_1] ON [dbo].[InvoiceDetail] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

第二点：

我将所有的插入语句都包裹在一个事务中。起初我不知道如何在.NET中实现这一点。

非常感谢我得到的所有建议。

如果我以后要进行此类从数据库到数据库的转换，我一定会首先考虑使用BULK INSERT。它似乎更加灵活和快速。

- Jose

---

请告诉我们更多关于您的聚集索引和插入操作。您的插入操作是否与聚集索引相关，以便所有记录都将附加在现有记录之后？（即，这些记录自然地按照聚集索引顺序排列） - Russell Steen

永远不要使用@@Identity！因为它不总是返回正确的值，可能会破坏数据完整性。请改用scope_Identity()。 - HLGEM

我知道这是旧的，但你还记得最终查询花了多少时间吗？只是想知道这些改变会产生多大影响。 - Mladen Mihajlovic

Answer 1

看起来插入操作导致SQL Server需要重新计算索引。一个 可能的 解决方法是删除索引，执行插入操作，然后重新添加索引。使用您尝试的解决方案，即使告诉它忽略约束条件，仍然需要保持索引更新。

- Jaxidian

Answer 2

很可能这是提交等待。如果您不将INSERTs组合到显式管理的事务中，则每个INSERT都是自己的自动提交事务。这意味着每个INSERT都会自动发出提交请求，而提交必须等待日志持久化（即写入磁盘）。在每次插入后刷新日志非常慢。

例如，尝试在单行提交样式上插入像您一样的100k行：

set nocount on; 
declare @start datetime = getutcdate();  

declare @i int = 0;
while @i < 100000
begin
INSERT INTO InvoiceDetail (
  LegacyId,InvoiceId,DetailTypeId,Fee,
  FeeTax,Investigatorid,SalespersonId,
  CreateDate,CreatedById,IsChargeBack,
  Expense,RepoAgentId,PayeeName,ExpensePaymentId,
  AdjustDetailId) 
  VALUES(1,1,2,1500.0000,0.0000,163,1002,
    '11/30/2001 12:00:00 AM',
    1116,0,550.0000,850,NULL,1,NULL); 
  set @i = @i+1;
end

select datediff(ms, @start, getutcdate());

在我的服务器上，这个过程大约需要12秒钟。但是添加了事务管理并且每1000行提交一次，插入10万行只需要大约4秒钟：

set nocount on;  
declare @start datetime = getutcdate();  

declare @i int = 0;
begin transaction
while @i < 100000
begin
INSERT INTO InvoiceDetail (
  LegacyId,InvoiceId,DetailTypeId,
  Fee,FeeTax,Investigatorid,
  SalespersonId,CreateDate,CreatedById,
  IsChargeBack,Expense,RepoAgentId,
  PayeeName,ExpensePaymentId,AdjustDetailId) 
  VALUES(1,1,2,1500.0000,0.0000,163,1002,
    '11/30/2001 12:00:00 AM',
    1116,0,550.0000,850,NULL,1,NULL); 
  set @i = @i+1;
  if (@i%1000 = 0)
  begin
    commit
    begin transaction
  end  
end
commit;
select datediff(ms, @start, getutcdate());

考虑到即使没有批量提交，我也可以在12秒内插入100k行，而你需要30分钟，值得调查的是：1）IO子系统的速度（例如驱动器上看到的Avg. Sec per Transaction），以及2）在从一个调用检索@@identity并调用下一个插入之间，客户端代码还在做什么。可能大部分时间都花在了堆栈的客户端端。一种简单的解决方案是并行启动多个插入（BeginExecuteNonQuery），以便不断地向SQL Server插入数据。

- Remus Rusanu

---

你的负载完全在数据库服务器上运行。我认为他是通过网络逐个发送插入语句，这就是为什么它们很慢的原因。可能更多是由于网络延迟和往返次数而不是实际提交刷新开销。 - Amit Naidu

我刚刚以一种我认为是合理的方式，逐个通过网络运行了他的110K个插入语句，并且是的，首先启动一个事务确实将吞吐量提高了一个数量级！ - Ralph Shillington

@RalphShillington，使用SQL Server 2014及更高版本，您还可以选择惰性耐久性。 - Remus Rusanu

Answer 3

您是从 .Net 客户端一个接一个地执行这些查询吗（即向 SQL Server 发送 110,000 个单独的查询请求）？

如果是这种情况，很可能是网络延迟和其他开销导致将这些 INSERT 发送到 SQL Server 而没有批量处理，而不是 SQL Server 本身。

请查看 BULK INSERT。

- Patrick

Answer 4

以下是你可以做的几件事：

1) Disable any triggers on this table
2) Drop all indexes
3) Drop all foreign keys
4) Disable any check constraints

- Randy Minder

Answer 5

您将此问题标记为“bulkinsert”。那么为什么不使用“BULK INSERT”命令？如果您想要进度更新，可以将大容量插入分成较小的部分，在每个部分完成后更新进度。请参考BULK INSERT命令了解更多信息。

- Mark Byers

---

批量插入允许您输入标识列吗？我必须承认我对批量插入并不是很了解，但我正在研究它。 - Jose

@Jose：我认为这篇文章可以回答你的问题：http://msdn.microsoft.com/zh-cn/library/ms186335.aspx - Mark Byers

Answer 6

逐个运行INSERT语句始终是最慢的选项。此外，@@IDENTITY有什么问题 - 看起来您不需要在中间跟踪它们。

如果您不想从文件或SSIS使用BULK INSERT，则可以使用ADO.NET中的SqlBulkCopy功能，如果您绝对必须在.NET程序内部执行此操作，则可能是最佳选择。

导入110k行应该比我研究和编写这篇答案所需的时间更短。

- Cade Roux

Answer 7

一些提高插入性能的建议：

• 增加ADO.NET BatchSize
• 明智选择目标表的聚集索引，避免插入导致聚集索引节点分裂（例如自增列）
• 先将数据插入到临时堆表中，然后发出一个大的“插入-选择”语句，将所有暂存表数据推入到实际目标表中
• 应用SqlBulkCopy
• 在插入之前放置一个表锁（如果您的业务场景允许）

摘自Tips For Lightning-Fast Insert Performance On SqlServer

- Mabuse

Answer 8

呃，让它运行，检查性能计数器。你看到了什么？你有什么磁盘布局？我可以在30分钟内插入几百万行记录——确切地说是近一亿条（实时财务信息，连接了3个其他表）。我敢打赌你的IO布局很糟糕（即磁盘结构不好，文件分布不好）。

- TomTom