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

Откуда: Порт пяти морей
Сообщений: 439
Операция MERGE позволяет объединить три операции insert, update, delete в одну.
Есть типовой сценарий: на вход процедуры приходит ID из Master таблицы и XML со списком из Detail таблицы, нужно заменить то, что есть на то, что пришло.
В примере в качестве Detail таблицы развязочная таблица связи много-ко-многим.

Проблема: как только в merge добавляем when not matched by source and ... then delete, так сразу в плане выходит скан целевой таблицы.
Если делать сначала delete лишних, потом merge с insert/update, то сканов нет, нагрузка ниже во много раз, отрабатывает быстрее.

Почему так?

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

create table _dlm(id int identity primary key, x int null);
insert _dlm(x)
select top 50000 rn = row_number() over (order by(select null)) from master..spt_values v1, master..spt_values v2

create table _itm(id int identity primary key, x int null);
insert _itm(x)
select id from _dlm
go

create table _dlm_itm(dlm_id int, itm_id int, constraint [_dlm_itm.pk] primary key clustered (dlm_id, itm_id ))
alter table _dlm_itm
  add constraint [_dlm_itm.fkDlm]
  foreign key (dlm_id) references _dlm (id)
alter table _dlm_itm
  add constraint [_dlm_itm.fkItm]
  foreign key (itm_id) references _itm (id)  
  
insert _dlm_itm(dlm_id, itm_id)
select a.id, b.id from _dlm as a cross apply(select top 20 id from _itm as b where b.id > a.id -20 order by b.id) as b  
select count(*) from _dlm_itm  
go


  declare @dlm_id int =20000
  declare @t table(itm_id int)
  insert into @t (itm_id)
  select top 20 id from _itm where id>200

-- DELETE + MERGE ( INSERT)
  delete d from    
    _dlm_itm as d
    outer apply( select top 1 itm_id from @t as t where t.itm_id = d.itm_id ) as t
  where d.dlm_id = @dlm_id
    and t.itm_id is null

  merge _dlm_itm as trg
    using(
      select 
         itm_id
      from @t
    ) as src( itm_id )
    on (     trg.dlm_id     = @dlm_id )
         and trg.itm_id     = src.itm_id
    when not matched by target then
      insert (  
         dlm_id
        ,itm_id)
      values (
         @dlm_id
        ,src.itm_id)
    ;
      
  delete from @t
  insert into @t (itm_id)
  select top 20 id from _itm where id>2000

-- MERGE ( INSERT + DELETE )
  merge _dlm_itm as trg
    using(
      select 
         itm_id
      from @t
    ) as src( itm_id )
    on (     trg.dlm_id     = @dlm_id )
         and trg.itm_id     = src.itm_id
    when not matched by target then
      insert (  
         dlm_id
        ,itm_id)
      values (
         @dlm_id
        ,src.itm_id)
        
    when not matched by source
        and trg.dlm_id     = @dlm_id          
      then delete
    ;  


К сообщению приложен файл (plan_merge_delete.zip - 15Kb) cкачать
23 апр 14, 15:23    [15923266]     Ответить | Цитировать Сообщить модератору
 Re: Операция MERGE: почему плохой DELETE?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Bator,

Потому, что условие ON в Merge это не то же самое что и ON в джойне. Это не фильтр, по данному условию строка возвращается всегда, при этом, устанавливается некоторое probe значение, которое говорит о том, было ли соответствие. В зависимости от этого выполняется то или другое.

В первом случае, когда у вас просто not matched by target - оптимизатору фактически дается задание найти что совпадает по trg.dlm_id = @dlm_id and and trg.itm_id = src.itm_id, и если такого нет, то выполнить вставку. Тут можно использовать поиск.

Во втором случае, для not matched by target то же самое. И в плане обратите внимание есть поиск. Но вот условие not matched by source фактически говорит, найди мне все которые не равны условию: (trg.dlm_id = @dlm_id and and trg.itm_id = src.itm_id). Для этого используется для этого Right Anti Semi Join, т.е. возвращает справа все что не совпало по критериям указанным в предикатах, т.е. по вашим условиям (логически получается такой поиск trg.dlm_id <> @dlm_id, trg.itm_id <> src.itm_id, а как в таком случае осуществить поиск по trg.dlm_id, если оно первое в индексе и там неравенство).

Разница с явным делет в том, что там говорится: найди мне по @dlm_id, а потом удали тех что нет по itm_id.
В случае delete в merge говорится: найди мне все что не @dlm_id <> dlm_id или itm_id<> itm_id, потом отфильтруй по @dlm_id (это где вы пишите when not matched by source and trg.dlm_id = @dlm_idб попробуйте, кстати, в тестовом закоментировать там /*and trg.dlm_id = @dlm_id*/ - увидите что будет и почему столько сканируется)

Вот так перепишите.
with 
	trg as (select * from _dlm_itm where dlm_id = @dlm_id),
	src as (select itm_id from @t)
merge trg
using src
on 
	trg.itm_id = src.itm_id
when 
	not matched by target then
	insert (dlm_id, itm_id)
	values (@dlm_id, src.itm_id)        
when 
	not matched by source then delete
;  
23 апр 14, 16:26    [15923807]     Ответить | Цитировать Сообщить модератору
 Re: Операция MERGE: почему плохой DELETE?  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
спасибо!
23 апр 14, 17:26    [15924294]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить