T-SQL MERGE Desempenho no contexto típico de publicação
Tenho uma situação em que um aplicativo "publicador" mantém essencialmente um modelo de visualização atualizado consultando uma visualização MUITO complexa e depois mesclando os resultados em uma tabela de modelo de visualização desnormalizada, usando operações de inserção, atualização e exclusão separada
Agora que atualizamos para o SQL 2008, achei que seria um ótimo momento para atualizá-los com a instrução SQL MERGE. No entanto, depois de escrever a consulta, o custo da subárvore da instrução MERGE é 1214.54! Com a maneira antiga, a soma do Insert / Update / Delete era de apenas 0,104 !!
Não consigo descobrir como uma maneira mais direta de descrever a mesma operação exata poderia ser tão ruim. Talvez você possa ver o erro dos meus caminhos onde eu não poss
Algumas estatísticas na tabela: Possui 1,9 milhão de linhas e cada operação MERGE insere, atualiza ou exclui mais de 100 delas. No meu caso de teste, apenas 1 é afetado.
-- This table variable has the EXACT same structure as the published table
-- Yes, I've tried a temp table instead of a table variable, and it makes no difference
declare @tSource table
(
Key1 uniqueidentifier NOT NULL,
Key2 int NOT NULL,
Data1 datetime NOT NULL,
Data2 datetime,
Data3 varchar(255) NOT NULL,
PRIMARY KEY
(
Key1,
Key2
)
)
-- Fill the temp table with the desired current state of the view model, for
-- only those rows affected by @Key1. I'm not really concerned about the
-- performance of this. The result of this; it's already good. This results
-- in very few rows in the table var, in fact, only 1 in my test case
insert into @tSource
select *
from vw_Source_View with (nolock)
where Key1 = @Key1
-- Now it's time to merge @tSource into TargetTable
;MERGE TargetTable as T
USING tSource S
on S.Key1 = T.Key1 and S.Key2 = T.Key2
-- Only update if the Data columns do not match
WHEN MATCHED AND T.Data1 <> S.Data1 OR T.Data2 <> S.Data2 OR T.Data3 <> S.Data3 THEN
UPDATE SET
T.Data1 = S.Data1,
T.Data2 = S.Data2,
T.Data3 = S.Data3
-- Insert when missing in the target
WHEN NOT MATCHED BY TARGET THEN
INSERT (Key1, Key2, Data1, Data2, Data3)
VALUES (Key1, Key2, Data1, Data2, Data3)
-- Delete when missing in the source, being careful not to delete the REST
-- of the table by applying the T.Key1 = @id condition
WHEN NOT MATCHED BY SOURCE AND T.Key1 = @id THEN
DELETE
;
Então, como isso chega a 1200 subárvores? O acesso a dados das próprias tabelas parece ser bastante eficiente. De fato, 87% do custo do MERGE parece ser de uma operação de classificação próxima ao final da cadeia:
MERGE (0%) <- Atualização do índice (12%) <- Classificar (87%) <- (...)
E esse tipo tem 0 linhas entrando e saindo dele. Por que são necessários 87% dos recursos para classificar 0 linhas?
ATUALIZA
Publiquei o real (não estimado) plano de execução apenas para a operação MERGE em uma essência.