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

Откуда: Саратов
Сообщений: 487
Произвожу копирование данных из старых таблиц в новые. Общий объем данных - внушительный, около 50 млн строк. Но скрипт
+
BEGIN TRY

	declare @original	tinyint = 1,
			@calculated	tinyint = 2,
			@Top		        tinyint = 1,
			@NR			tinyint = 9,
			@SP			tinyint = 10			
			
	BEGIN TRANSACTION		
	
	SET IDENTITY_INSERT slices.LBudgetPositions ON
	-- Команда 1. 6 млн записей	
	insert into slices.LBudgetPositions(
		   [ID]
		  ,[Name]
		  ,[code]
		  ,[SectionId]
		  ,[LBudgetId]
		  ,[Type]
		  ,[Statuses]
		  ,[Amount]
		  ,[Order]
		  ,[OriginalOrder]
		  ,[Performer]
		  ,[UnitName]
		  ,[CalcMethod]
		  ,[IsCheckedOnGesn]
		  ,[Description]		  
		  ,[LocalId]
		  ,[SsrCode]
		  ,[SsrIndex]
		  ,[CostCode]
		  ,[CostExt]
		  ,[OriginalAmount]
		  ,[OriginalId])
	select 
		 BL.ID
		,BL.Name
		,BL.Code
		,BL.SectionId
		,BL.LBudgetID
		,BL.[Type]
		,BL.StatusID
		,BL.Amount
		,BL.Number
		,BL.OriginalOrder
		,case WhoID 
			when 1 then 1
			when 2 then 2
			else 0
		 end as performer
		,U.Code as unitname
		,case when BL.StatusID & 1 > 0 then 1 --базисно-индексный метод
			else 0
		 end as CalcMethod
		,isnull(BL.IsCheckedOnGesn,0)
		,BL.[Description]
		,BL.LocalID	
		,BL.SsrCode
		,BL.SsrIndex
	    ,BL.CostCode
	    ,BL.CostExt
	    ,ISNULL(lp.OriginalAmount, BL.Amount)
	    ,OriginalID
	from slices.BudgetLocalOld BL
	left join dbo.LBudgetPositions lp on lp.ID = BL.OriginalID
	left join dbo.Local_tbl L on L.ID = BL.LocalID
	left join dbo.krUnit_tbl U on U.ID = L.IDUnit
	SET IDENTITY_INSERT slices.LBudgetPositions OFF	

	-- Команда 2. 46 млн записей, условиям поиска соответствует  23 млн
	update slices.LBudgetPositionCosts
	set SourceType = @original
	where SourceType = @calculated and LevelType <> @Top
	
	-- Команда 3. 11,5 млн записей 
	delete 
	from slices.LBudgetPositionCosts
	where SourceType = @calculated and LevelType = @Top
	
	-- Команда 4. 5 записей
	INSERT INTO [slices].[LBPosCoeffCorrections]
           ([PositionCoefficientId]
           ,[Name]
           ,[Value]
           ,[Operation])
	select ID
		  ,''
		  ,ValueWithCorrects / Value
		  ,0  --умножение 
	from slices.LBudgetPositionCoefficients C
	where Value <> 0 and ValueWithCorrects <> Value

	declare @tb_temp table (id1 bigint, id2 bigint)
	
	-- Команда 5. 3 млн записей
	insert into slices.LBudgetPositionCoefficients(
		   [PositionId]
		  ,[Type]
		  ,[Statuses]
		  ,[Name]
		  ,[Justification]
		  ,[Operation]
		  ,[Order]
		  ,[Level]
		  ,[Value]
		  ,[ValueWithCorrects]
		  ,[OriginalCharge]
		  ,[CalculatedCharge]
		  ,[OriginalCost]
		  ,[CalculatedCost]
		  ,[LBudgetId])
		OUTPUT	inserted.ID
			  , inserted.PositionId
		INTO	@tb_temp(id1, id2)
	select [ID]
		  ,@NR as [Type]
		  ,2	--Рассчитывать от полных стоимостей
		  ,'НР'
		  ,NULL as Justification
		  ,0	--умножение
		  ,(select COUNT(*) from  slices.LBudgetPositionCoefficients c where c.PositionId = bl.ID) + 1 as [Order]
		  ,0 as [Level]
		  ,OverCharge
		  ,CASE when overChargeCoef <> 1 then overChargeCoef * OverCharge
				else OverCharge
			END
		  ,0
		  ,0
		  ,isnull(OTotalNR,0)
		  ,isnull(TotalNR,0) + isnull(TotalOuNR,0)
		  ,LBudgetID
	from slices.BudgetLocalOld bl
	where OverCharge > 0 
		AND NOT EXISTS(select 1 from slices.LBudgetPositionCoefficients c where c.PositionId = bl.ID and c.[Type] = @NR) --НР
	
	-- Команда 6.
	-- 0 записей, выполняется уже более 3 часов, гоняя BudgetLocalOld по Nested Loops 3 млн раз
	-- а может и все 9 часов, учитывая, что последнее изменение файла журнала транзакций БД и файлов Temp_DB было 9 часов назад
	insert into slices.LBPosCoeffCorrections(
		   [PositionCoefficientId]
		  ,[Name]
		  ,[Value]
		  ,[Operation])
	select t.id1
		  ,''
		  ,overChargeCoef
		  ,0  --умножение 
	from slices.BudgetLocalOld B
	join @tb_temp t on t.id2 = B.ID
	where overChargeCoef <> 1 

	delete @tb_temp
	
	-- Команда 7.
	-- 3 млн записей
	insert into slices.LBudgetPositionCoefficients(
		   [PositionId]
		  ,[Type]
		  ,[Statuses]
		  ,[Name]
		  ,[Justification]
		  ,[Operation]
		  ,[Order]
		  ,[Level]
		  ,[Value]
		  ,[ValueWithCorrects]
		  ,[OriginalCharge]
		  ,[CalculatedCharge]
		  ,[OriginalCost]
		  ,[CalculatedCost]
		  ,[LBudgetId])
		OUTPUT	inserted.ID
			  , inserted.PositionId
		INTO	@tb_temp(id1, id2)     
	select [ID]
		  ,@SP as [Type]
		  ,2	--Рассчитывать от полных стоимостей
		  ,'СП' 
		  ,NULL as Justification
		  ,0  --умножение
		  ,(select COUNT(*) from  slices.LBudgetPositionCoefficients c where c.PositionId = bl.ID) + 1 as [Order]
		  ,0 as [Level]
		  ,BudgetProfit
		  ,CASE when BudgetProfitCoef <> 1 then BudgetProfitCoef * BudgetProfit
				else BudgetProfit
			END
		  ,0
		  ,0
		  ,isnull(OTotalSP,0)
		  ,isnull(TotalSP,0) + isnull(TotalOuSP,0)
		  ,LBudgetID
	from slices.BudgetLocalOld bl
	where BudgetProfit > 0
		AND NOT EXISTS(select 1 from slices.LBudgetPositionCoefficients c where c.PositionId = bl.ID and c.[Type] = @SP) --СП 
	
	-- Команда 8.
	-- 0 записей на тестовой БД, в реале может быть оч много
	insert into slices.LBPosCoeffCorrections(
		   [PositionCoefficientId]
		  ,[Name]
		  ,[Value]
		  ,[Operation])
	select t.id1
		  ,''
		  ,BudgetProfitCoef
		  ,0  --умножение 
	from slices.BudgetLocalOld B
	join @tb_temp t on t.id2 = B.ID
	where BudgetProfitCoef <> 1 


	if @@TRANCOUNT > 0 commit tran
END TRY
BEGIN CATCH
	if @@TRANCOUNT > 0 rollback tran
	exec dbo._ReRaise
END CATCH


выполняется уже более 14 часов (статистика sp_ whoisactive во вложении 1), причем во всех целевых таблицах, кроме slices.LBudgetPositionCosts, нет индексов и внешних ключей.
1 команда выполнbлась довольно быстро (менее 1 ч, возможно даже быстрее)
2 и 3 (изменение 23 млн строк и удаление 11.5 млн) выполнялось > 2 ч
4 команда очевидно выполнилась моментально
О команде 5 мне ничего, к сожалению, не известно, ее старт и финиш были ночью.
Команда 6
+
-- Команда 6.
        --гоняет BudgetLocalOld по Nested Loops 3 млн раз
	insert into slices.LBPosCoeffCorrections(
		   [PositionCoefficientId]
		  ,[Name]
		  ,[Value]
		  ,[Operation])
	select t.id1
		  ,''
		  ,overChargeCoef
		  ,0  --умножение 
	from slices.BudgetLocalOld B
	join @tb_temp t on t.id2 = B.ID
	where overChargeCoef <> 1

которая ничего не должна вставлять, но выполняется уже более 3 часов (возможно > 9), идет по неудачному плану.
Как ее ускорить я знаю (планирую использовать временную таблицу вместо переменной табличного типа).

Команды 7 и 8 еще даже не начались.

Вопросы:
1. Сильно ли тормозит процесс оборачивание скрипта в транзакцию (в принципе транзакционности требуют только команды 5+6 и 7+8 ?
2. Отчего могла так долго (> 2 часов) выполнятся команда 2 (count для нее выполняется считанные секунды )?
Хотя скорее всего это связано с тем, что приходится перестраивать кластеризованный индекс slices.LBudgetPositionCosts, т.к. изменяемое поле - одно из ключевых. Стоит ли здесь использовать подсказки, например табличную блокировку?
3. Команда 6. 3 млн seek по кластеризованному ключу это конечно плохо, но разве настолько, чтобы выполнятся > 3 часа?
Под sql-сервер выделено 20ГБ оперативки, он их использует полностью, место на жестком диске есть, CPU практически бездействует. Грешу на жесткий диск.
4. Как еще можно оптимизировать скрипт(bulk insert...)?

+ print @@version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) 
	Mar 19 2015 12:32:14 
	Copyright (c) Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )


К сообщению приложен файл. Размер - 12Kb
5 окт 15, 12:46    [18235826]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шамиль Фаридович
4. Как еще можно оптимизировать скрипт(bulk insert...)?

Откройте для себя Partitioned Tables
И не надо будет ничего никуда копировать
5 окт 15, 12:48    [18235840]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Уважаемый Glory, вы вообще не уловили суть вопроса.
Моя задача - это скорее рефакторинг.
5 окт 15, 12:54    [18235893]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шамиль Фаридович
Моя задача - это скорее рефакторинг.

Если это разовая задача, то о какой оптимизации вы говорите ?
5 окт 15, 12:56    [18235908]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Она будет выполняться 1 раз, но у разных заказчиков. Ее можно конечно же запустить вечером, но к утру она должна быть выполнена.
Вероятно, что в обозримом будущем будут похожие разовые задачи с похожими проблемами, описанными в вопросах 1- 4. И их нужно решить уже сейчас.
5 окт 15, 13:05    [18235979]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шамиль Фаридович
Ее можно конечно же запустить вечером, но к утру она должна быть выполнена.

И неважно, можно ли ее физически выполнить за отведенное время ?

Шамиль Фаридович
Вероятно, что в обозримом будущем будут похожие разовые задачи с похожими проблемами, описанными в вопросах 1- 4. И их нужно решить уже сейчас.

Ну так в чем проблема ?
Берете и делаете все тоже самое, но порциями, а не миллионами
5 окт 15, 13:09    [18236004]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8839
Наилучшим способом будет перевести базу в simple модель восстановления и использовать преимущества минимального журналирования (т.е. select into, включить флаг трассировки 610). при этом отказаться от операций удаления и обновления записей.
5 окт 15, 13:56    [18236366]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
0-0
Guest
Шамиль Фаридович
Вопросы:
1. Сильно ли тормозит процесс оборачивание скрипта в транзакцию (в принципе транзакционности требуют только команды 5+6 и 7+8 ?
2. Отчего могла так долго (> 2 часов) выполнятся команда 2 (count для нее выполняется считанные секунды )?
Хотя скорее всего это связано с тем, что приходится перестраивать кластеризованный индекс slices.LBudgetPositionCosts, т.к. изменяемое поле - одно из ключевых. Стоит ли здесь использовать подсказки, например табличную блокировку?
3. Команда 6. 3 млн seek по кластеризованному ключу это конечно плохо, но разве настолько, чтобы выполнятся > 3 часа?
Под sql-сервер выделено 20ГБ оперативки, он их использует полностью, место на жестком диске есть, CPU практически бездействует. Грешу на жесткий диск.
4. Как еще можно оптимизировать скрипт(bulk insert...)?


1. Да - у вас еще и лог будет писаться очень хорошо и пухнуть будет на протяжении всей транзакции
2. Нет ключей, не разбит update (соответственно запись лог + spill temp db и обратно в память)?
3. Сколько записей в @tb_temp?
4. Зависит от того что необходимо сделать, желания и умения. Я вот например не понимаю зачем update? delete можно заменить на truncate. Писать можно в таблицу-кучу, а потом создавать на ней кластерный и другие индексы. Можно вообще все ускорить с помощью сегментации скорее всего.
5 окт 15, 16:08    [18237119]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
o-o
Guest
0-0
Шамиль Фаридович
Вопросы:
1. Сильно ли тормозит процесс оборачивание скрипта в транзакцию (в принципе транзакционности требуют только команды 5+6 и 7+8 ?

1. Да - у вас еще и лог будет писаться очень хорошо и пухнуть будет на протяжении всей транзакции

а типа, если не оборачивать все в транзакцию, то транзакций не будет и "в лог не будет писаться"?
все то же самое и уйдет в лог, другое дело, запихать 8 инструкций в 1 транзакцию --
это лишить сервер возможности транкейтить этот самый лог на протяжении всех восьми инструкций [кучи часов],
а так после каждой из восьми cможет
5 окт 15, 16:26    [18237247]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
0-0
Guest
o-o,

ну, теоретически и пактически без транзакции после каждого запроса лог можно транкейтнуть.
В случае одной транзакции для всех 8 инструкции сделать этого будет нельзя
5 окт 15, 16:32    [18237292]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
o-o
Guest
Шамиль Фаридович
2. Отчего могла так долго (> 2 часов) выполнятся команда 2 (count для нее выполняется считанные секунды )?
Хотя скорее всего это связано с тем, что приходится перестраивать кластеризованный индекс slices.LBudgetPositionCosts, т.к. изменяемое поле - одно из ключевых.

0-0
2. Нет ключей, не разбит update (соответственно запись лог + spill temp db и обратно в память)?

как же нет ключей, у ТС как раз часть кластерного ключа обновляется.
а это означает, не только кластерный индекс обновляется, но и все некластерные.
а кластерный еще и наверняка сплитится, ведь апдэйт -- это гарантированное перемещение записи
5 окт 15, 16:53    [18237405]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
o-o
Guest
0-0
o-o,
ну, теоретически и пактически без транзакции после каждого запроса лог можно транкейтнуть.
В случае одной транзакции для всех 8 инструкции сделать этого будет нельзя

вы же в своем первом ответе совсем не это написали.
я обычно выделяю кусок, к к-ому относится мой комментарий.
в вашем посте выделено
0-0
1. Да - у вас еще и лог будет писаться очень хорошо и пухнуть будет на протяжении всей транзакции

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

но со своим же утверждением про усечение лога я соглашyсь
o-o
другое дело, запихать 8 инструкций в 1 транзакцию --
это лишить сервер возможности транкейтить этот самый лог на протяжении всех восьми инструкций
[кучи часов],
а так после каждой из восьми cможет
5 окт 15, 17:05    [18237454]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
0-0
Guest
o-o,

ну да, имел в виду я конечно не то, что написал.
Это хорошо, что вы меня поправили.
5 окт 15, 17:27    [18237541]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Владислав Колосов,
спасибо за ключ тассировки, нашел по нему полезную статью
https://msdn.microsoft.com/ru-ru/library/dd425070(en-us).aspx

0-0,
спасибо за инфу по транзакциям.
0-0
2. Нет ключей, не разбит update (соответственно запись лог + spill temp db и обратно в память)?

Как раз на этой таблице slices.LBudgetPositionCosts ключи и индексы есть. Вопрос был в том, стоит ли при обновлении данных в этой таблице (команда 2) указать tablock?
0-0
Я вот например не понимаю зачем update? delete можно заменить на truncate.

Мне нужен именно update.
0-0
Писать можно в таблицу-кучу, а потом создавать на ней кластерный и другие индексы.

Так и делается
0-0
Можно вообще все ускорить с помощью сегментации скорее всего.

Имеете в виду разбиение больших команд на порции по 10000 строк?
5 окт 15, 17:34    [18237573]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
0-0
Guest
Шамиль Фаридович
Как раз на этой таблице slices.LBudgetPositionCosts ключи и индексы есть. Вопрос был в том, стоит ли при обновлении данных в этой таблице (команда 2) указать tablock?

Конечно стоит.
А в зависимости от объема я бы вообще не делал update, а писал бы в новую таблицу с уже новыми значениями, потом старую таблицу затирал и удалял, а новую переименовывал (или еще лучше - менял бы ей схему).


Шамиль Фаридович
0-0
Можно вообще все ускорить с помощью сегментации скорее всего.

Имеете в виду разбиение больших команд на порции по 10000 строк?

Нет, имею в виду вот это:
https://msdn.microsoft.com/en-us/library/ms190787.aspx
5 окт 15, 17:48    [18237616]     Ответить | Цитировать Сообщить модератору
 Re: Перенос данных. Оптимизация  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Дык это секционирование таблиц, а вы писали про сегментацию.
Впрочем, понятия близкие. А зачем оно здесь?
5 окт 15, 18:14    [18237721]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить