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

Откуда: Ярославль
Сообщений: 244
Сделал хранимую процедуру для bulk update, но работает медленно. При обновлении 2 тысяч строк тормоза жуткие. Подскажите как оптимизировать и куда копать.

CREATE PROCEDURE dbo.lion_UpdateTasks(  @t_1 TasksTableType READONLY,
                                                             @t_2 TasksCPTableType READONLY
					 		 )
AS

BEGIN
	  	
    DECLARE @ErrorCode     int
    SET @ErrorCode = -1
    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    SELECT * INTO #t1 FROM @t_1
    SELECT * INTO #t2 FROM @t_2
        
    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0    
    	        	        			
    UPDATE dbo.lion_Tasks
    SET     
		uid_parent = #t1.uid_parent, 
		uid_customer = #t1.uid_customer,
		email_performer = #t1.email_performer,
		name = #t1.name,		
		comment = #t1.comment,
		[status] = #t1.[status],		
		date_modify = #t1.date_modify,
		uid_project = #t1.uid_project,

	  еще 20 полей тут
					
	  FROM #t1 WHERE dbo.lion_Tasks.uid_obj = #t1.uid_obj										 	            
    
	  IF( @@ERROR != 0 )
	  BEGIN
		  SET @ErrorCode = -1
		  GOTO Cleanup
	  END		 				
		  
	  UPDATE dbo.lion_Tasks_Changes_Parts
          SET 
	
	  _order_cp = #t2._order_cp,	 
	  uid_marker_cp = #t2.uid_marker_cp,
	  date_begin_cp = #t2.date_begin_cp,
	  date_end_cp = #t2.date_end_cp,
	  readed_cp = #t2.readed_cp,	 
	  collapsed_cp = #t2.collapsed_cp,	 	 	  
          Contacts = #t2.Contacts,

	  еще 20 полей тут
	
	  FROM #t2 WHERE dbo.lion_Tasks_Changes_Parts.uid_task_cp = #t2.uid_task_cp AND
	  dbo.lion_Tasks_Changes_Parts.uid_user_cp = #t2.uid_user_cp                
			   			 
          IF( @@ERROR != 0 )
	  BEGIN
		  SET @ErrorCode = -1
		  GOTO Cleanup
	  END

          IF( @TranStarted = 1 )
          BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
          END
	
          RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode    
    
END

GO
11 дек 14, 20:19    [16985527]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31432
gepard1980
При обновлении 2 тысяч строк тормоза жуткие
Это сколько - миллисекунду, минуту? Что говорит о выполнении профайлер?

2 тыс. строк - это мало. Может, в UPDATE dbo.lion_Tasks и dbo.lion_Tasks_Changes_Parts просто индексов нету на uid_obj и uid_user_cp?
11 дек 14, 20:32    [16985571]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
babona
Member [заблокирован]

Откуда: Батуринск
Сообщений: 1816
на больших количества строк забудьте навсегда про команду UPDATE
11 дек 14, 21:28    [16985668]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
gepard1980,

Индексы? FK?
Где планы запросов?
12 дек 14, 01:07    [16986319]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 244
Использую NewRelic - там показывается в том числе время выполнения хранимых процедур. Все просто вставало наглушняк. Тысячи пользователей делают этот BulkUpdate. Вчера весь день искал место тормозов (в БД около тысячи хранимых процедур). В lion_Tasks и lion_Tasks_Changes_Parts уже есть индексы по where-полям. Сейчас создал индексы во временных таблицах, которые создаются в хранимой процедуре:

CREATE NONCLUSTERED INDEX ii1 ON #t1(uid_obj)
CREATE NONCLUSTERED INDEX ii2 ON #t2(uid_user_cp)

Плюс убрал транзакцию, посмотреть как оно будет без нее. Babona написал забыть про Update при больших обьемах. Как тогда сделать без update?
12 дек 14, 09:42    [16986877]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
Glory
Member

Откуда:
Сообщений: 104760
gepard1980
При обновлении 2 тысяч строк тормоза жуткие.

- 2000 строк в каждой таблице, использованной в ваших запросах
- И какая из команд жутко тормозит ?
12 дек 14, 09:44    [16986883]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 244
В таблице lion_Tasks, lion_Tasks_Changes_Parts примерно по 5 млн строк. Запросов к серверу около 4 тысяч в секунду. BulkUpdate-ом вставляется 2 тысячи строк. Пока BulkUpdate выполняется - остальные курят. Как только поставил по коду фильтр - если больше тысячи строк нужно клиенту обновить, то отлуп, и все стало нормально работать. Поэтому и думаю как заменить или ускорить BulkUpdate.
12 дек 14, 09:56    [16986949]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
Glory
Member

Откуда:
Сообщений: 104760
gepard1980
Пока BulkUpdate выполняется - остальные курят

И вы выяснили, чего именно ждут курящие ? И на какой команде ждут ?
12 дек 14, 09:58    [16986962]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 244
Теперь все нормально. Разбил UpdateTasks на две процедуры, убрал транзацию, сделал индексы во временных таблицах. Теперь полет нормальный.
12 дек 14, 11:02    [16987281]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31432
gepard1980
Теперь все нормально. Разбил UpdateTasks на две процедуры, убрал транзацию, сделал индексы во временных таблицах. Теперь полет нормальный.
Можно было и в одной процедуре оставить, просто убрать транзакцию, раз она вам не нужна.
gepard1980
Поэтому и думаю как заменить или ускорить BulkUpdate.
А почему вы называете BulkUpdate?
Bulk - это определённый класс операций, который может быть только вставкой.
12 дек 14, 11:06    [16987306]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 244
alexeyvg, BulkUpdate - просто как-то по аналогии с BulkInsert, чтоб как-то описать 'массовое обновление'
12 дек 14, 11:24    [16987416]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
Диклевич Александр
Member

Откуда:
Сообщений: 610
gepard1980
alexeyvg, BulkUpdate - просто как-то по аналогии с BulkInsert, чтоб как-то описать 'массовое обновление'

а в SQL есть не массовое обновление? Это как?

Хотя встречаются "процедурщики" пытающиеся "эмулировать" циклы курсорами для обновления.
12 дек 14, 12:11    [16987860]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 244
Диклевич Александр, имеется ввиду следующее: если из сервера приложений вызывать хранимую процедуру с UPDATE для каждой строки - это долго. А если передать в хранимую процедуру сразу список, то можно сделать сразу 'массовое обновление'. Термин может и не камильфовый, но какой уж придумал :-)
12 дек 14, 12:23    [16987959]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31432
gepard1980
если из сервера приложений вызывать хранимую процедуру с UPDATE для каждой строки - это долго
Прикольный оригинальный способ :-) Лучше уж для такого придумать особое название.
12 дек 14, 12:54    [16988258]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
alexeyvg
gepard1980
если из сервера приложений вызывать хранимую процедуру с UPDATE для каждой строки - это долго
Прикольный оригинальный способ :-) Лучше уж для такого придумать особое название.

нда... еще наверно таблица из базы грузится на клиенте в клиентский dataset->правиться-> и отдаётся в виде таблицы параметра назад для внесения изменений на сервере
12 дек 14, 13:00    [16988298]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 244
LexusR, вот наговаривать не надо :-) Делается выборка из БД нужных сущностей, используя хранимую процедуру. Результат выборки пишется в словарь. Далее идет логика обработки этого словаря. Далее полученные результаты пишем в DataTable, и вызываем хранимую процедуру для обновления. Одним из параметров этой процедуры есть DataTable.
12 дек 14, 13:56    [16988661]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
Glory
Member

Откуда:
Сообщений: 104760
gepard1980
Далее полученные результаты пишем в DataTable, и вызываем хранимую процедуру для обновления

Зачем ? Сразу нельзя "полученными результатами" обновлять таблицу ?
12 дек 14, 13:58    [16988674]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
Sorry если чем то обидел.
Просто мне показалось странным передача в процедуру с клиента таблицы-параметра в несколько тысяч строк. Обычно это бывает когда что-то не то в консерватории.
12 дек 14, 14:01    [16988696]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 244
Вот для ясности схема работы : СУБД находитя на одной машине, а сервер приложений на другой. ASP.NET-приложение, находящееся на сервере приложений взаимодействует с СУБД через слой хранимых процедур.
12 дек 14, 14:28    [16988850]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
Glory
Member

Откуда:
Сообщений: 104760
gepard1980
Вот для ясности схема работы : СУБД находитя на одной машине, а сервер приложений на другой. ASP.NET-приложение, находящееся на сервере приложений взаимодействует с СУБД через слой хранимых процедур.

Вы не поверите, но ваша схема не уникальна. Я бы даже сказал, что она массовая.
12 дек 14, 14:30    [16988858]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 244
Glory, дак понятно что массовая :-) как по вашему с сервера приложений писать в SQL изменения?
12 дек 14, 14:45    [16988990]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
Glory
Member

Откуда:
Сообщений: 104760
gepard1980
как по вашему с сервера приложений писать в SQL изменения?

Изменения в SQL можно писать только командой UPDATE(MERGE)
Но вы то сначала зачем пишите изменения еще и в DataTable
12 дек 14, 14:47    [16989011]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 244
Glory, после получения данных от сервера СУБД выполняется бизнес-логика, она производит изменения в словаре с данными. Эти изменения нужно закинуть на сервер. Быстрее всего этого можно сделать через хранимые процедуры, можно конечно и в коде C# вызвать UPDATE, но это будет медленнее. К тому же хранимые процедуры инкапсулируют в себе всю логику работы с БД, т.е. SQL код не раскидан про проекту и не требуется перекомпиляция после изменении в хранимых процедурах.
12 дек 14, 15:43    [16989476]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
Glory
Member

Откуда:
Сообщений: 104760
gepard1980
она производит изменения в словаре с данными. Эти изменения нужно закинуть на сервер.

Два раза что или изменяет ?
Или "словарь с данными" - это не MSSQL ?
12 дек 14, 15:45    [16989487]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Update  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 244
словарь с данными это Dictionary от C#
12 дек 14, 15:53    [16989573]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить