Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
Есть база данных MS SQL Server 2008 R2 с нагрузкой 2000 транзакций в секунду. Есть таблицы lion_Tasks(uid_obj, order_new, __usn_field_order_new, и еще 40 полей) и таблица lion_Tasks_Changes_Parts(uid_task_cp, uid_user_cp, __usn_entity_cp и еще 20 полей ). На веб-сервер прилетает 10 тысяч объектов, у которых изменилось только одно поле order_new. В базе нужно обновить только 3 поля. Эти объекты передаю табличным параметром в хранимую процедуру:

CREATE PROCEDURE dbo.lion_UpdateTasksNewOrder(  @Table TasksNewOrderTableType READONLY )
AS

BEGIN
	  	
    SELECT * INTO #t3 FROM @Table

    CREATE NONCLUSTERED INDEX IDX_T3_UID_TASK ON #t3(UUID_TASK)
    CREATE NONCLUSTERED INDEX IDX_T3_UID_USER ON #t3(UUID_USER)
      	      	        	        			
    UPDATE dbo.lion_Tasks
    SET     

    order_new =  #t3.ORDER_NEW,   
    __usn_field_order_new = #t3.USN_ORDER_NEW
   
    FROM #t3
    WHERE dbo.lion_Tasks.uid_obj = #t3.UUID_TASK
    
	   				
    UPDATE dbo.lion_Tasks_Changes_Parts
    SET 
	
    __usn_entity_cp = #t3.USN_ENTITY

    FROM  #t3
    WHERE dbo.lion_Tasks_Changes_Parts.uid_task_cp = #t3.UUID_TASK AND dbo.lion_Tasks_Changes_Parts.uid_user_cp = #t3.UUID_USER
   			   			 
 
END
GO


Отрабатывает 10-20 секунд. Пробовал еще такой вариант без temp-овой БД. Но результат тот же.

CREATE PROCEDURE dbo.lion_UpdateTasksNewOrder(  @Table TasksNewOrderTableType READONLY )
AS

BEGIN
	  	
    UPDATE TasksTable
    SET     

    order_new = t.ORDER_NEW,   
    __usn_field_order_new = t.USN_ORDER_NEW
   
    FROM dbo.lion_Tasks TasksTable
    INNER JOIN @Table t
    ON t.UUID_TASK = TasksTable.uid_obj
    
    UPDATE TasksTableCP
    SET 
	
    __usn_entity_cp = tcp.USN_ENTITY

    FROM dbo.lion_Tasks_Changes_Parts TasksTableCP
    INNER JOIN @Table tcp
    ON tcp.UUID_TASK = TasksTableCP.uid_task_cp AND tcp.UUID_USER = TasksTableCP.uid_user_cp
   			   			     
END
GO


Temp-овая БД находится на SSD-диске. Может кто подскажет, куда копать для ускорения?

Сообщение было отредактировано: 4 сен 18, 17:33
4 сен 18, 17:30    [21664448]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36989
gepard1980
SELECT * INTO #t3 FROM @Table

CREATE NONCLUSTERED INDEX IDX_T3_UID_TASK ON #t3(UUID_TASK)
CREATE NONCLUSTERED INDEX IDX_T3_UID_USER ON #t3(UUID_USER)
Зачем? Сделайте сразу в типе TasksNewOrderTableType (через unique-констрейнты с участием сурогатного уникального поля). Так же не ясно, зачем эти индексы нужны в принципе.

Ну и планы надо смотреть.

Сообщение было отредактировано: 4 сен 18, 17:37
4 сен 18, 17:35    [21664454]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
Хорошо. Индексы уберу как написали. План для варианта с join-ами прикрепил.

К сообщению приложен файл. Размер - 65Kb
4 сен 18, 18:15    [21664508]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
gepard1980,

Планы нужны актуальные, а не оценочные. И в формате sqlplan, а не картинками.
4 сен 18, 18:18    [21664515]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
Как их получить для хранимых процедур?
4 сен 18, 18:21    [21664520]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
aleks222
Member

Откуда:
Сообщений: 985
gepard1980
куда копать для ускорения?


Откуда уверенность, что можно "быстрее"?
Таки "10 тысяч курьеров скачутобъектов".

Рази тока вот так написать, если вероятность "обновить на то же самое" не нулевая

 UPDATE tt  SET order_new = t.ORDER_NEW,   __usn_field_order_new = t.USN_ORDER_NEW
     FROM dbo.lion_Tasks as tt  INNER JOIN @Table as t ON t.UUID_TASK = tt.uid_obj
where order_new <> t.ORDER_NEW or   __usn_field_order_new <> t.USN_ORDER_NEW
4 сен 18, 18:52    [21664570]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
gepard1980
Отрабатывает 10-20 секунд. Пробовал еще такой вариант без temp-овой БД. Но результат тот же.
А поля dbo.lion_Tasks.uid_obj и dbo.lion_Tasks_Changes_Parts.uid_task_cp какого типа? У первого ГУИД, и на него кластерный индекс, а второй - просто FK?

Если так, то обновление 10 000 записей есть обновление 10 000 случайных страниц, и должно делаться долго.

Вам нужно подумать над моделью данных и архитектурой системы, оптимальны ли они, ну или просто наращивать железо.

Индексы, конечно, не нужны, как и перелив в временную таблицу, это же у вас просто передача записей для обновления, сервер их должен все прочитать, и, соответственно, скан будет самым оптимальным.
4 сен 18, 18:59    [21664577]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
aleks222
Рази тока вот так написать, если вероятность "обновить на то же самое" не нулевая
Да, вот это очень важно, т.к. в данном случае медленное именно обновление, если памяти достаточно.
4 сен 18, 19:00    [21664579]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
gepard1980
Как их получить для хранимых процедур?
Профайлером.
4 сен 18, 19:01    [21664580]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
gepard1980
Как их получить для хранимых процедур?
Профайлер. Extended Events.
Либо выполнить в студии, обеспечив при вызове такие же опции соединения, язык и значения параметров.

После проблемного вызова, посмотрите в sys.dm_exec_procedure_stats данные по процедуре.
Если last_elapsed_time много больше last_worker_time - имеют место ожидания (на блокировках и т.п.).
4 сен 18, 19:06    [21664588]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
Спасибо всем кто окликнулся. Завтра пришлю актуальный план выполнения хранимой процедуры.
4 сен 18, 20:03    [21664651]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
alexeyvg
gepard1980
Отрабатывает 10-20 секунд. Пробовал еще такой вариант без temp-овой БД. Но результат тот же.
А поля dbo.lion_Tasks.uid_obj и dbo.lion_Tasks_Changes_Parts.uid_task_cp какого типа? У первого ГУИД, и на него кластерный индекс, а второй - просто FK?

Если так, то обновление 10 000 записей есть обновление 10 000 случайных страниц, и должно делаться долго.

Вам нужно подумать над моделью данных и архитектурой системы, оптимальны ли они, ну или просто наращивать железо.

Индексы, конечно, не нужны, как и перелив в временную таблицу, это же у вас просто передача записей для обновления, сервер их должен все прочитать, и, соответственно, скан будет самым оптимальным.


Перешел на вариант без использования tempdb. dbo.lion_Tasks.uid_obj - Guid (кластерный), dbo.lion_Tasks_Changes_Parts.uid_task_cp - Guid (некластерный) - просто FK. На архитектуру думаю повлиять так: если пришло больше тысячи, обрабатывать только тысячу и кидать флаг клиенту, чтобы он другие присылал объекты. Короче разбить один большой запрос на несколько. Еще попробовал к хранимой процедуре добавить WITH RECOMPILE. Буду в логах смотреть - есть ли смысл.
5 сен 18, 08:35    [21665013]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
alexeyvg
gepard1980
Как их получить для хранимых процедур?
Профайлером.


Для данной ХП добыл следующие актуальные планы выполнения.

CREATE PROCEDURE dbo.lion_UpdateTasksNewOrder( @Table TasksNewOrderTableType READONLY) WITH RECOMPILE
AS

BEGIN

DECLARE @ErrorCode int
SET @ErrorCode = -1

UPDATE TasksTable
SET

order_new = t.ORDER_NEW,
__usn_field_order_new = t.USN_ORDER_NEW

FROM dbo.lion_Tasks TasksTable
INNER JOIN @Table t
ON t.UUID_TASK = TasksTable.uid_obj

IF( @@ERROR != 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END

UPDATE TasksTableCP
SET

__usn_entity_cp = tcp.USN_ENTITY

FROM dbo.lion_Tasks_Changes_Parts TasksTableCP
INNER JOIN @Table tcp
ON tcp.UUID_TASK = TasksTableCP.uid_task_cp AND tcp.UUID_USER = TasksTableCP.uid_user_cp

IF( @@ERROR != 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END

RETURN 0

Cleanup:

RETURN @ErrorCode

END

GO

К сообщению приложен файл (plans.zip - 4Kb) cкачать
5 сен 18, 09:35    [21665098]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
Вот еще сюда закинул план: https://www.brentozar.com/pastetheplan/?id=HkkqKg6DX
5 сен 18, 09:45    [21665114]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
gepard1980
Для данной ХП добыл следующие актуальные планы выполнения.
Это опять оценочные планы, а не актуальные.
5 сен 18, 09:59    [21665138]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
invm, запустил SQL Profiler. Создал трассировку. Запустил код в Visual Studio. Остановил трассировку. Сохранил в файле результаты. Как правильно сделать?
5 сен 18, 10:13    [21665159]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
gepard1980,

Нужно добавить в трассу событие Showplan XML Statistics Profile
5 сен 18, 10:43    [21665199]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
gepard1980,

Estimated -> Showplan XML
Actual - > Showplan XML Statistics Profile
5 сен 18, 10:44    [21665200]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
Barclay
Member

Откуда:
Сообщений: 127
gepard1980
Temp-овая БД находится на SSD-диске. Может кто подскажет, куда копать для ускорения?

Что-то должно помочь, надо тестить:
1. задействуйте минимальное логирование при записи с flag 610 и/или с хинтом TABLOCK
2.
Замените конструкцию на похожую
--Synchronize the target table with
--refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate)
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
5 сен 18, 10:51    [21665207]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
gepard1980
вообще table variable то ещё зло, пробуйте прибить RECOMPILE, но всё же план

Barclay,
да именно merge и решает проблемы производительности, только в другу сторону
5 сен 18, 10:58    [21665214]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
TaPaK, выставил эти две галки. Получил то что на прикрепленном скриншоте.

Сюда план положил: https://www.brentozar.com/pastetheplan/?id=Bygm2bTDQ

К сообщению приложен файл. Размер - 60Kb
5 сен 18, 10:58    [21665216]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
gepard1980
TaPaK, выставил эти две галки.
Одна галка нужна - Showplan XML Statistics Profile
А не Showplan Statistics Profile
5 сен 18, 11:02    [21665225]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
invm, вот теперь только с этой галкой. Планы прикрепил.

К сообщению приложен файл. Размер - 60Kb
5 сен 18, 11:11    [21665240]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
gepard1980, вот получил актуальные планы.

К сообщению приложен файл (act_plans.zip - 4Kb) cкачать
5 сен 18, 11:14    [21665253]     Ответить | Цитировать Сообщить модератору
 Re: Обновление нескольких полей у большого числа строк  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 240
TaPaK

вообще table variable то ещё зло, пробуйте прибить RECOMPILE, но всё же план


Дак а как без table variable? Не по одной же записи обновлять.
5 сен 18, 11:18    [21665261]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить