Как избежать сканирования таблиц на SQL Server в этой ситуации

Есть две таблицы, Costs и Logs. Данные в таблице Costs могут содержать миллионы строк, а в таблице Logs — миллиарды строк.

Мне нужно обновить столбец CostBy в таблице Costs в сервисной задаче в производственной среде в пределах 100 записей при каждом запуске.

CREATE TABLE Cost
(
    C_PK uniqueidentifier primary key not null,
    C_CostBy varchar(3) not null
)

CREATE TABLE Logs
(
    L_PK uniqueidentifier primary key not null,
    L_ParentTable varchar(255) not null,  -- Table Cost and other table's name
    L_ParentID uniqueidentifier not null, -- Cost's pk and other table's pk
    L_Event varchar(3) not null, -- Part are 'ADD' and other event types
    L_User varchar(3) not null 
 )

CREATE NONCLUSTERED INDEX [L_ParentID] 
    ON [dbo].[Costs] ([L_ParentID] ASC)

Вот исходное заявление об обновлении:

UPDATE TOP(100) Costs
SET CostBy = ISNULL(L_User, '~UK')
FROM Costs
LEFT JOIN Logs ON L_ParentID = C_PK AND L_Event = 'ADD'
WHERE CostBy = ''

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

Мой вопрос: как избежать сканирования таблицы в таблице Costs или как оптимизировать оператор обновления?

Заранее спасибо.


sql-server database-performance query-performance full-table-scan
person Jimmy Wang    schedule 27.03.2020    source источник
comment
Отображаемый вами T-SQL не компилируется, опубликуйте правильный код. Кроме того, было бы неплохо добавить несколько строк выборочных данных.   -  person Thailo    schedule 27.03.2020
comment
В чем причина обновления только первых 100 строк? Это функциональное требование или вы делаете это, чтобы обновлять несколько результатов за раз/выполнять несколько вызовов?   -  person JohnLBevan    schedule 27.03.2020
comment
Является ли это разовой задачей или этот код будет использоваться на регулярной основе (т. е. требуется ли обновлять таблицу затрат каждые 10 минут / каждый раз, когда кто-то выполняет какое-либо действие / и т. д., или это просто исправление некоторых неверных данных в разовая задача?   -  person JohnLBevan    schedule 27.03.2020

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете попробовать следующее.

Сначала создайте индекс для журналов, включая все соответствующие столбцы:

CREATE INDEX ix ON Logs 
(
  L_Parent_ID -- join condition, variable
)
INCLUDE 
(
  L_User -- no filter condition, but you use it your update
)
WHERE 
(
  L_Event = 'ADD' -- join condition, constant
)

Если это уникальный индекс, т.е. только одна строка будет когда-либо существовать с событием ADD для данного родительского идентификатора, обязательно сделайте этот индекс уникальным, поскольку он может значительно повысить производительность.

Во-вторых, и это случайная ситуация, вы можете попробовать использовать индекс для Costs (CostBy), потому что вы ищете только пустые значения CostBy для обновления. Этот индекс необходимо будет обновить по вашему запросу, потому что он обновляет его, поэтому он может замедлить ваш запрос, а не ускорить его. Это зависит от ряда факторов.

Если у вас есть корпоративная лицензия, попробуйте оба с WITH (DATA_COMPRESSION = PAGE), это может значительно улучшить время ввода-вывода за счет ЦП. Это зависит от того, что является вашим узким местом.

Кроме того, в зависимости от характера ваших данных обновление статистики может улучшить ваши запросы. Если количество строк с CostBy = '' непропорционально другим значениям, вы можете воспользоваться полной статистикой по этому полю. Рассмотрите NORECOMPUTE, если они вам нужны только для этого конкретного запроса, в этот раз.

CREATE STATISTICS st_Costs_CostBy
ON Costs (CostBy)  
WITH FULLSCAN, NORECOMPUTE;
person MarcinJ    schedule 27.03.2020