sql server语言 - sql Update真的很慢(大约20-50秒)，Select只需要不到1秒

Question

我有一个SQL表"Document"，其中包含了很多行(高达几百万)。

当我执行一个Select-Statement时，它需要大约0.5秒。但是，当我使用相同的where子句执行Update时，它需要大约20到50秒，这取决于受影响的行数。

这是我的陈述。

//选择

SELECT * FROM Document 
WHERE (State=20 OR State=23) AND 
LetterClosed IS NOT NULL AND 
TYPE=0 AND
SendLetter=1

//更新

UPDATE Document set State=32 
WHERE (State=20 OR State=23) AND 
LetterClosed IS NOT NULL AND 
TYPE=0 AND
SendLetter=1

OR-Mapper内部发送如下更新语句到数据库:

exec sp_executesql N'Update
Document
SET
    State=@p4
WHERE
(
  (
    (
      (Document.State = @p0 OR Document.State = @p1) 
      AND Document.LetterClosed IS NOT NULL
    ) 
    AND Document.Type = @p2
  ) 
  AND Document.SendLetter = @p3
)'
,N'@p0 int,@p1 int,@p2 int,@p3 bit,@p4 int',@p0=20,@p1=23,@p2=0,@p3=1,@p4=32

问题是，我在30秒后从我的LightSpeed(c#中的数据库OR-Mapper)获得超时异常。

有人能帮我一下吗?编辑:

这是SQL-Server自动创建的索引:

CREATE NONCLUSTERED INDEX [_dta_index_Document_9_133575514__K42_1_2_3_4_5_6_7_8_9_11_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_28_29_30_31_32_33_34_] ON [Document] 
(
    [State] ASC
)
INCLUDE ( 
[Id],[DocumentId],[SendLetter],[SendFax],[Archive],[Crm],[Validation],[CreationDate],[PageCount],
[InformationLetter],[TermsOfDelivery],[DeliveryTypeNo],[SeparateDelivery],[FormName],[FormDescription],[TemplateFileName],[RecipientType],
[HealthInsuranceNo],[FamilyHealthInsuranceNo],[PensionInsuranceNo],[EmployerCompanyNo],[RecipientName1],[RecipientName2],[RecipientName3],
[RecipientStreet],[RecipientCountryCode],[RecipientZipCode],[RecipientCity],[RecipientFaxNo],[AuthorId],
[AuthorName],[AuthorEmailAddress],[CostcenterDepartment],[CostcenterDescription],[MandatorNo],[MandatorName],[ControllerId],
[ControllerName],[EditorId],[EditorName],[StateFax],[Editable],[LetterClosedDate],[JobId],[DeliveryId],[DocumentIdExternal],[JobGroupIdExternal],
[GcosyInformed]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_Document_9_133575514__K2_1_46] ON [Document] 
(
    [DocumentId] ASC
)
INCLUDE ( [Id],
[JobId]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_Document_9_133575514__K46_K2] ON [Document] 
(
    [JobId] ASC,
    [DocumentId] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [Document_State_Id] ON [Document] 
(
    [State] ASC,
    [Id] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [Document_State_CreationDate] ON [Document] 
(
    [State] ASC,
    [CreationDate] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

编辑2:现在我有了一个图形化的执行计划:执行计划:https://skydrive.live.com/redir?resid=597F6CF1AB696567 ! 444, authkey = !ABq72SAWXOoAXfI

执行计划索引更新详情:https://skydrive.live.com/?cid=597f6cf1ab696567&id=597F6CF1AB696567%21445&sff=1&authkey=!ADDPWvxB2JLLvWo

执行这个sql更新大约需要35秒。通常这个更新只需要0.3秒。似乎另一个进程阻止了这个进程。我看到其他一些选择在更新的中间开始，直到更新完成，直到他们完成选择执行。

所以看起来索引本身是正确的(通常是0,3秒执行)。所有选择(来自java/jtds, php， .net)都是隔离级别的读提交(默认)。它会帮助我在这里改变所有的选择读未提交，以避免这种阻塞在索引更新?

谢谢托比

Answer 1

我曾经在SQL Server 2008和SQL Server 2014链接服务器上遇到过这个问题。对于我来说，的一个解决方案是将"Select"结果存储到一个临时表中，并使用它来执行更新，而不是立即执行复杂的查询和更新。

在你的例子中，这将是:

--Select
SELECT * FROM Document
into #temp 
WHERE (State=20 OR State=23) AND 
LetterClosed IS NOT NULL AND 
TYPE=0 AND
SendLetter=1
--Update
UPDATE Document set State=32 
from #temp
WHERE #temp.id = Document.id 
--assuming that id is your PK

Answer 2

没有执行计划，我们只能猜测会发生什么。

我将从:

1. 检查document表有多少索引(但很难相信更新索引需要这么长的时间)。
2. 检查是否有触发器在更新时执行。

所有这些都应该在执行计划中可见。

另一个原因可能是SQL引擎对SELECT查询有一个执行计划，对UPDATE查询有不同的执行计划…

更新

在查看索引之后。

我认为指数_dta_index_Document_9_133575514__K42_1_2_3_4_5_6_7_8_9_11_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_28_29_30_31_32_33_34_是完全错误的。

它包含很多列，这可能会使更新缓慢。

尝试将其删除或替换为state列上的CLUSTERED索引。CLUSTERED index* include*(直接访问)记录的所有列，不需要额外的读取。

可能它应该与其他以state列开头的索引之一组合—我假设state只有几个值。

很遗憾，我无法以文本格式解释执行计划。

Answer 3

可能在表Document上有索引。索引使选择更快，但更新/插入/删除操作慢。

尝试删除不必要的索引