SQL Server 插入性能

2022-01-09 00:00:00 bulkinsert insert sql-server

我有一个像这样生成的插入查询

I have an insert query that gets generated like this

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 行生成.

This query is generated for only 110K rows.

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

It takes 30 minutes for all of these query's to execute

我检查了查询计划，最大的 % 节点是

I checked the query plan and the largest % nodes are

以 57% 的查询成本插入聚集索引其中有一个我不想发布的长 xml.

A Clustered Index Insert at 57% query cost which has a long xml that I don't want to post.

查询成本为 38% 的 Table Spool

A Table Spool which is 38% query cost

<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 约束所有在查询之前然后ALTER TABLE TABLENAME NOCHECK 约束所有在查询之后.

So my question is what is there that I can do to improve the speed of this thing? I already run ALTER TABLE TABLENAME NOCHECK CONSTRAINTS ALL Before the queries and then ALTER TABLE TABLENAME NOCHECK CONSTRAINTS ALL after the queries.

这并没有减少任何时间.

And that didn't shave off hardly anything off of the time.

知道我在使用 SqlCommand 对象发送查询的 .NET 应用程序中运行这些查询.

Know I am running these queries in a .NET application that uses a SqlCommand object to send the query.

然后我尝试将 sql 命令输出到一个文件，然后使用 sqlcmd 执行它，但我没有得到任何关于它如何做的更新，所以我放弃了.

I then tried to output the sql commands to a file and then execute it using sqlcmd, but I wasn't getting any updates on how it was doing, so I gave up on that.

有什么想法、提示或帮助吗?

Any ideas or hints or help?

更新:

好的，你们所有人都非常有帮助.在这种情况下，我希望我可以将多个答案归功于多个答案.

Ok so all of you were very helpful. In this situation I wish I could give credit to more than one answer.

解决此问题的方法有两个.

The solution to fix this was twofold.

第一个:

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

1) I disabled/reenabled all the foreign keys(much easier than dropping them)

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

2) 我禁用/重新启用了索引(同样比删除容易得多)

2) I disabled/Reenabled the indexes (again much easier than dropping)

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 中做到这一点.

I wrapped all of the insert statements into one transaction. I initially didn't know how to do that in .NET.

我非常感谢我收到的所有意见.

I really appreciate all of the input I got.

如果我曾经做过这种从 DB 到 DB 的翻译，我肯定会从 BULK INSERT 开始.它似乎更加灵活和快速.

If I ever do this kind of translation from DB to DB I will definitely start with BULK INSERT. It seems much more flexible and faster.

推荐答案

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

Sounds like the inserts are causing SQL Server to recalculate the indexes. One possible solution would be to drop the index, perform the insert, and re-add the index. With your attempted solution, even if you tell it to ignore constraints, it will still need to keep the index updated.

相关文章