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

Откуда:
Сообщений: 658
Есть запрос:
MERGE ek.dbo.ls_Uslugi_Obem as Target
    USING (
			select	ls_Uslugi.id as id_ls_Uslugi,
					--(select id from dbo.ls_Uslugi where ls_Uslugi.[id_ls_Stepen_Blag]=(select ls_Stepen_Blag.id from ls inner join Ls_Stepen_Blag on ls.id=Ls_Stepen_Blag.id_ls where num_ls = t1.lsnum and Ls_Stepen_Blag.id_Stepen_Blag =t1.id_Stepen_Blag) and id_Uslugi= t1.uslref) as id_ls_Uslugi,
					t1.Obem_Uslugi as Obem_Uslugi,
					cast('19991231' as date) as Date_Change_Obem,
					1 as ld_Prichina_Smeny_Obem_Uslugi_Ls
					-- Здесь вынесен отдельный селект, т.к. oreacle возвращает дробную часть числа sum(LSUSLDOPKOL) через запятую - единственный вариант как удалось победить - разорвать union`ы внутри оракла и вынести их в SQL
				from openquery(HOEL, 'select		
													ls.lsnum,
													100+lsusldop.usldopnormaref as id_Stepen_Blag,
													lsusldop.uslref,
													cast(case when lsusldop.usldopnormaref in (2, 3)  -- Бани центральный водопровод / бани колонка
																then 1
																else sum(LSUSLDOPKOL)
															end
															 as numeric(15,3)) as Obem_Uslugi
											from ls inner join lsusldop on ls.lsref=lsusldop.lsref
											where LSUSLDOPKOL <> 0
											group by ls.lsnum, 
													lsusldop.usldopnormaref,
													lsusldop.uslref') t1
						left join ls on t1.lsnum=ls.num_ls
						left join Ls_Stepen_Blag on ls.id=Ls_Stepen_Blag.id_ls and Ls_Stepen_Blag.id_Stepen_Blag =t1.id_Stepen_Blag
						left join dbo.ls_Uslugi on ls_Uslugi.[id_ls_Stepen_Blag] = ls_Stepen_Blag.id and ls_Uslugi.id_Uslugi= t1.uslref
						union all
			select	ls_Uslugi.id as id_ls_Uslugi,
					--(select id from dbo.ls_Uslugi where ls_Uslugi.[id_ls_Stepen_Blag]=(select ls_Stepen_Blag.id from ls inner join Ls_Stepen_Blag on ls.id=Ls_Stepen_Blag.id_ls where num_ls = t1.lsnum and Ls_Stepen_Blag.id_Stepen_Blag =t1.id_Stepen_Blag) and id_Uslugi= t1.uslref) as id_ls_Uslugi,
					t1.Obem_Uslugi as Obem_Uslugi,
					cast('19991231' as date) as Date_Change_Obem,
					1 as ld_Prichina_Smeny_Obem_Uslugi_Ls
				from openquery(HOEL, '	select distinct
												ls.lsnum,
												nvl(GROUNDCOMFORT.comfortref, objcomfort.comfortref) as id_Stepen_Blag,
												usl.uslref,
												cast(1 as numeric (15,3)) as Obem_Uslugi
												from ls inner join ground on ls.groundref=ground.groundref
														inner join admin.pipeground on ls.groundref=pipeground.groundref
														inner join usl on pipeground.uslref=usl.uslref
														inner join obj on ground.objref=obj.objref
														left join GROUNDCOMFORT on ground.groundref = GROUNDCOMFORT.groundref
														left join objcomfort on obj.objref=objcomfort.objref
												where nvl(GROUNDCOMFORT.comfortref, objcomfort.comfortref)  is not null
													and nvl(GROUNDCOMFORT.comfortref, objcomfort.comfortref) <> 13
													and usl.USLVID =''Вода''
		
										union all
										select distinct
												ls.lsnum,
												case when usl.uslref=11
														then 13
														when usl.uslref=14
														then 23
													end as id_Stepen_Blag,
												usl.uslref,
												1 as Obem_Uslugi
												from ls inner join pipeground on ls.groundref=pipeground.groundref
														inner join usl on pipeground.uslref=usl.uslref
												where usl.USLVID =''ТБО''
										union all
										select 		ls.lsnum,
													case pipeground.uslref
														when 94 then 14
														when 96 then 15
														when 98 then 16
														when 100 then 17
														when 102 then 18
													end,
											pipeground.uslref,
											count(*) as obem_Uslugi
												from ls inner join pipeground on ls.groundref=pipeground.groundref
														inner join usl on pipeground.uslref=usl.uslref
												where usl.USLVID =''ОГО''
												group by ls.lsnum,
															case pipeground.uslref
																when 94 then 14
																when 96 then 15
																when 98 then 16
																when 100 then 17
																when 102 then 18
															end,
													pipeground.uslref
										
																	') t1
						left join ls on t1.lsnum=ls.num_ls
						left join Ls_Stepen_Blag on ls.id=Ls_Stepen_Blag.id_ls and Ls_Stepen_Blag.id_Stepen_Blag =t1.id_Stepen_Blag
						left join dbo.ls_Uslugi on ls_Uslugi.[id_ls_Stepen_Blag] = ls_Stepen_Blag.id and ls_Uslugi.id_Uslugi= t1.uslref

					) as Source
	ON Target.id_ls_Uslugi=Source.id_ls_Uslugi
	WHEN NOT MATCHED THEN 
		INSERT (	id_ls_Uslugi,
					Obem_Uslugi,
					Date_Change_Obem,
					ld_Prichina_Smeny_Obem_Uslugi_Ls
				)
			values (Source.id_ls_Uslugi,
					Source.Obem_Uslugi,
					Source.Date_Change_Obem,
					Source.ld_Prichina_Smeny_Obem_Uslugi_Ls
					)
	WHEN MATCHED and (
						Target.Obem_Uslugi <> Source.Obem_Uslugi
						or Target.Date_Change_Obem <> Source.Date_Change_Obem
						or Target.ld_Prichina_Smeny_Obem_Uslugi_Ls <> Source.ld_Prichina_Smeny_Obem_Uslugi_Ls
					)
		THEN
			UPDATE set Obem_Uslugi = Source.Obem_Uslugi,
						Date_Change_Obem = Source.Date_Change_Obem,
						ld_Prichina_Smeny_Obem_Uslugi_Ls = Source.ld_Prichina_Smeny_Obem_Uslugi_Ls
	WHEN NOT MATCHED BY SOURCE
		THEN
			DELETE;


Окончание выполнения которого я так и не смог дождаться. Выполнялся более семи часов. В это время нагрузки на сервера не было.
HOEL - это связаный сервер ORACLE, с которого вся выборка SOURCE занимает около минуты.

Если из этого запроса я убираю
WHEN NOT MATCHED BY SOURCE
		THEN
			DELETE

Запрос отрабатывает за две минуты

Если убираю
WHEN NOT MATCHED THEN 
		INSERT (	id_ls_Uslugi,
					Obem_Uslugi,
					Date_Change_Obem,
					ld_Prichina_Smeny_Obem_Uslugi_Ls
				)
			values (Source.id_ls_Uslugi,
					Source.Obem_Uslugi,
					Source.Date_Change_Obem,
					Source.ld_Prichina_Smeny_Obem_Uslugi_Ls
					)
	WHEN MATCHED and (
						Target.Obem_Uslugi <> Source.Obem_Uslugi
						or Target.Date_Change_Obem <> Source.Date_Change_Obem
						or Target.ld_Prichina_Smeny_Obem_Uslugi_Ls <> Source.ld_Prichina_Smeny_Obem_Uslugi_Ls
					)
		THEN
			UPDATE set Obem_Uslugi = Source.Obem_Uslugi,
						Date_Change_Obem = Source.Date_Change_Obem,
						ld_Prichina_Smeny_Obem_Uslugi_Ls = Source.ld_Prichina_Smeny_Obem_Uslugi_Ls

Запрос так же отрабатывает две минуты.


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

Подскажите что не так? Другие MERGE с того же прилинкованного сервера проходят нормально.
31 май 13, 06:34    [14373307]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Количество записей в SOURCE / TARGET около 800`000.
31 май 13, 07:23    [14373350]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
При этом MERGE со всеми директивами при пустом TARGET `е отрабатывает в течении 3-х минут.
Т.е., когда директива when not matched by source не проверяется в связи с отсутствием необходимости.
31 май 13, 08:30    [14373459]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Galyamov Rinat,

MERGE напрямую без OPENQUERY при линкованном сервере не работает. Глубоко не могу сказать, но предполагаю, один из вариантов, возможно именно с линкованным сервером в мерже может быть затык.
Я бы для начала Ваш весь запрос "большой и специфический" залил в темповую таблицу и эту таблицу использовал в качестве соурса для мержа - select * into #src from (большой запрос в который врядли кроме Вас кто-то вникнет) as t
31 май 13, 08:33    [14373468]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Алексей Куренков,

Затык в том, что MERGE разделенный на две команды работает - а одной командой со всеми директивами не хочет.

Другие MERGE с обращением к тому же линкованному серверу работают без проблем, правда там кол-во строк меньше - порядка 200`000 против 800`000 в данном.

Может кто сталкивался с подобным?

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

Еще раз оговорюсь, что запрос к прилинкованному серверу отрабатывает за минуту.
31 май 13, 08:40    [14373495]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
aleks2
Guest
Galyamov Rinat
Попробовать через временную таблицу, конечно можно. Но хочу понять, что в данном запросе не так.

Еще раз оговорюсь, что запрос к прилинкованному серверу отрабатывает за минуту.


1. Ну и чо, што "за минуту". Он возвращает "за минуту" херову тучу записей и складывает их в НЕУПОРЯДОЧЕННУЮ кучу.
2. А ты своим WHEN NOT MATCHED BY SOURCE заставляешь сервер РЫТЬСЯ в этой куче (сканировать ее то бишь) стока раз, скока у тя записей в Target.
3. Естественно, затраты времени растут как число_строк_Target*число_строк_Source.
4. Подожди денек-другой и сервер справится.
31 май 13, 08:52    [14373549]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Galyamov Rinat
Алексей Куренков,

Затык в том, что MERGE разделенный на две команды работает - а одной командой со всеми директивами не хочет.

Другие MERGE с обращением к тому же линкованному серверу работают без проблем, правда там кол-во строк меньше - порядка 200`000 против 800`000 в данном.

Может кто сталкивался с подобным?

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

Еще раз оговорюсь, что запрос к прилинкованному серверу отрабатывает за минуту.


Выложите для сравнения планы запросов:
1. Где работает быстро без какой либо дерективы.
2. Где работает медленно.
31 май 13, 09:07    [14373630]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
aleks2
Guest
Алексей Куренков

Выложите для сравнения планы запросов:
1. Где работает быстро без какой либо дерективы.
2. Где работает медленно.


Планы - это костыль для слепых.
31 май 13, 09:36    [14373729]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
По поводу планов - где медленно - я так и не дождался окончания выполнения.
А приводить "быстрый" без "медленного", наверное нет смысла.

Сейчас попробую прикрутить запрос во временную таблицу, и проиндексировать ее.
31 май 13, 10:49    [14374159]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
aleks2
Galyamov Rinat
Попробовать через временную таблицу, конечно можно. Но хочу понять, что в данном запросе не так.

Еще раз оговорюсь, что запрос к прилинкованному серверу отрабатывает за минуту.


1. Ну и чо, што "за минуту". Он возвращает "за минуту" херову тучу записей и складывает их в НЕУПОРЯДОЧЕННУЮ кучу.
2. А ты своим WHEN NOT MATCHED BY SOURCE заставляешь сервер РЫТЬСЯ в этой куче (сканировать ее то бишь) стока раз, скока у тя записей в Target.
3. Естественно, затраты времени растут как число_строк_Target*число_строк_Source.
4. Подожди денек-другой и сервер справится.



Я понимаю, что увеличение количества задействованных строк приводит к геометрическому увеличению времени на обработку.
Но вопрос в том, почему по отдельности MERGE ... WHEN NOT MATCHED BY SOURCE без других директив отрабатывает быстро?
Если предположить, что проблема в WHEN MATCHED или WHEN NOT MATCHED, то MERGE с этими инструкциями, но без WHEN NOT MATCHED BY SOURCE тоже отрабатывает довольно таки быстро.
31 май 13, 11:05    [14374291]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Galyamov Rinat
Но вопрос в том, почему по отдельности MERGE ... WHEN NOT MATCHED BY SOURCE без других директив отрабатывает быстро?

Потому что разные запросы работают по-разному

Galyamov Rinat
Если предположить, что проблема в WHEN MATCHED или WHEN NOT MATCHED, то MERGE с этими инструкциями, но без WHEN NOT MATCHED BY SOURCE тоже отрабатывает довольно таки быстро.

В TSQL вы указываете что надо сделать, а не как надо сделать.
Как будет выполняться запрос решает сервер.
31 май 13, 11:38    [14374532]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
aleks2
Планы - это костыль для слепых.


Улыбнуло ))). Вспомнилась поговорка - "с тормозами ездят только трусы".

А на тему плана можно "прикинуть" не по актуальному плану а по estimated plan.
31 май 13, 13:03    [14375288]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Переделал на заполнение временной таблицы, индексирование по полю "сцепки".
Затем мердж, где источником выступает select из этой временной таблицы.

Заполнение таблицы заняло 1:37
Сам MERGE 0:41


Будем считать, что оптимальное быстродействие достигнуто - оставляю так.

Но вот вопрос остается открытым - более 7-ми часов. И то прервал выполнение. Результата так и не дождался.

Я понимаю, что "куча", что неиндексированная.

Но во-первых, другие подобные слияния работают достаточно быстро - есть одно, где идет слияние данных с того же линкованного Оракла и кол-во строк 510`000 (правда не 800`000). Время - 7 минут.

Во-вторых, эту кучу SQL Server ведь тоже хранит не весть как, а в структуре...

В-третьих, когда в MERGE убираешь одну из директив (NOT MATCHED BY SOURCE/NOT MATCHED) - все работает на ура (по скорости).

В общем, для меня это загадка. Будем считать, что оптимизатор встал в тупик из-за моих join`ов в SOURCE и выбрал самый неоптимальный план.
31 май 13, 13:47    [14375653]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Galyamov Rinat
Но во-первых, другие подобные слияния работают достаточно быстро - есть одно, где идет слияние данных с того же линкованного Оракла и кол-во строк 510`000 (правда не 800`000). Время - 7 минут.

Во-вторых, эту кучу SQL Server ведь тоже хранит не весть как, а в структуре...
В-третьих, когда в MERGE убираешь одну из директив (NOT MATCHED BY SOURCE/NOT MATCHED) - все работает на ура (по скорости).

Вы что серьезно считаете, что все запросы MERGE должны выполняться одинаково ?
31 май 13, 14:51    [14376147]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Троль детектед?

Или попытка сарказма?

Нет. Я не считаю, что все запросы должны выполняться одинаково.

Меня смущает МНОГОКРАТНАЯ (даже не на один порядок, а на несколько) разница во времени между выполнением
MERGE
...
WHEN NOT MATCHED
...
WHEN NOT MATHED BY SOURCE


и

MERGE
...
WHEN NOT MATCHED
...
;

MERGE
...
WHEN NOT MATHED BY SOURCE
...


Вам еще что-то нужно уточнить, чтобы ответить на первоначальный вопрос?
31 май 13, 16:48    [14376958]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Galyamov Rinat
Нет. Я не считаю, что все запросы должны выполняться одинаково.
Меня смущает МНОГОКРАТНАЯ (даже не на один порядок, а на несколько) разница во времени между выполнением

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

Galyamov Rinat
Вам еще что-то нужно уточнить, чтобы ответить на первоначальный вопрос?

Мне и так все понятно. Это для вас "В общем, для меня это загадка"
31 май 13, 16:56    [14377016]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется merge. Если его разбить на два - выполняется кратно быстрее.  [new]
aleks2
Guest
Galyamov Rinat
Вам еще что-то нужно уточнить, чтобы ответить на первоначальный вопрос?

Нафега вам ваще MERGE?

WHEN NOT MATCHED THEN 
		INSERT (	id_ls_Uslugi,
					Obem_Uslugi,
					Date_Change_Obem,
					ld_Prichina_Smeny_Obem_Uslugi_Ls
				)
			values (Source.id_ls_Uslugi,
					Source.Obem_Uslugi,
					Source.Date_Change_Obem,
					Source.ld_Prichina_Smeny_Obem_Uslugi_Ls
					)
	WHEN MATCHED and (
						Target.Obem_Uslugi <> Source.Obem_Uslugi
						or Target.Date_Change_Obem <> Source.Date_Change_Obem
						or Target.ld_Prichina_Smeny_Obem_Uslugi_Ls <> Source.ld_Prichina_Smeny_Obem_Uslugi_Ls
					)
		THEN
			UPDATE set Obem_Uslugi = Source.Obem_Uslugi,
						Date_Change_Obem = Source.Date_Change_Obem,
						ld_Prichina_Smeny_Obem_Uslugi_Ls = Source.ld_Prichina_Smeny_Obem_Uslugi_Ls
	WHEN NOT MATCHED BY SOURCE
		THEN
			DELETE;


Означают "перезапись ВСЕГО" => проще TRUNCATE/INSERT задействовать и не парить серверу и форуму мозги.
1 июн 13, 07:16    [14378530]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить