Обновление SQL очень медленное (около 20-50 секунд), выбор занимает менее 1 секунды

У меня есть SQL-документ "Документ", который содержит много строк (до нескольких миллионов).

Когда я выполняю оператор Select, это занимает около 0,5 секунды. Но когда я выполняю обновление с тем же предложением WHERE, это занимает от 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 ​​(база данных OR-Mapper в C#).

Может ли кто-нибудь помочь мне здесь?

Редактировать:

И это наши индексы, автоматически создаваемые SQL-сервером:

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) имеют уровень изоляции для чтения (по умолчанию). Поможет ли мне здесь изменить все выборки для чтения незафиксированными, чтобы избежать этой блокировки во время обновления индекса?

Спасибо Тоби

Источник

3 ответа

Однажды у меня была эта проблема на связанных серверах SQL Server 2008 и SQL Server 2014. Обходным решением для меня было сохранить результаты "Выбрать" во временную таблицу и использовать ее для обновления, а не для сложных запросов и обновления одновременно.

В вашем случае это будет:

--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
Источник

Без плана выполнения мы можем только догадываться, что произойдет.

Я бы начал с:

  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_ совершенно неправильно.

Он включает в себя множество столбцов, которые могут замедлить обновление.

Попробуйте удалить его или заменить на CLUSTERED индекс на state колонка. CLUSTERED index * include * (имеет прямой доступ) ко всем столбцам записи без дополнительных чтений.

Возможно, его следует объединить с одним из других индексов, начинающихся с state колонка - полагаю state имеет всего несколько значений.

К сожалению, я не могу интерпретировать план выполнения в текстовом формате.

Источник

Возможно, у вас есть индексы в таблице Document. Индексы делают выбор быстрее, но медленнее операции обновления / вставки / удаления.

Попробуйте удалить ненужные индексы.

Источник
Другие вопросы по тегам