Estou tentando otimizar um procedimento. Existem 3 consultas de atualização diferentes presentes no procedimento.
update #ResultSet
set MajorSector = case
when charindex(' ', Sector) > 2 then rtrim(ltrim(substring(Sector, 0, charindex(' ', Sector))))
else ltrim(rtrim(sector))
end
update #ResultSet
set MajorSector = substring(MajorSector, 5, len(MajorSector)-4)
where left(MajorSector,4) in ('(00)','(01)','(02)','(03)','(04)','(05)','(06)','(07)','(08)','(09)')
update #ResultSet
set MajorSector = substring(MajorSector, 4, len(MajorSector)-3)
where left(MajorSector,3) in ('(A)','(B)','(C)','(D)','(E)','(F)','(G)','(H)','(I)','(J)','(K)','(L)','(M)','(N)','(O)','(P)','(Q)','(R)','(S)','(T)','(U)','(V)','(W)','(X)','(Y)','(Z)')
Para concluir todas as três consultas de atualização, leva menos de 10 segundos .
Plano de execução para todas as três consultas de atualização.
https://www.brentozar.com/pastetheplan/?id=r11BLfq7b
O que planejei é mudar as três consultas de atualização diferentes em uma única consulta de atualização, para que a E/S possa ser reduzida.
;WITH ResultSet
AS (SELECT CASE
WHEN LEFT(temp_MajorSector, 4) IN ( '(00)', '(01)', '(02)', '(03)', '(04)', '(05)', '(06)', '(07)', '(08)', '(09)' )
THEN Substring(temp_MajorSector, 5, Len(temp_MajorSector) - 4)
WHEN LEFT(temp_MajorSector, 3) IN ( '(A)', '(B)', '(C)', '(D)','(E)', '(F)', '(G)', '(H)','(I)', '(J)', '(K)', '(L)','(M)', '(N)', '(O)', '(P)','(Q)', '(R)', '(S)', '(T)','(U)', '(V)', '(W)', '(X)','(Y)', '(Z)' )
THEN Substring(temp_MajorSector, 4, Len(temp_MajorSector) - 3)
ELSE temp_MajorSector
END AS temp_MajorSector,
MajorSector
FROM (SELECT temp_MajorSector = CASE
WHEN Charindex(' ', Sector) > 2 THEN Rtrim(Ltrim(Substring(Sector, 0, Charindex(' ', Sector))))
ELSE Ltrim(Rtrim(sector))
END,
MajorSector
FROM #ResultSet)a)
UPDATE ResultSet
SET MajorSector = temp_MajorSector
Mas isso leva cerca de 1 minuto para ser concluído. Verifiquei o plano de execução, é idêntico à primeira consulta de atualização .
Plano de execução para a consulta acima:
https://www.brentozar.com/pastetheplan/?id=SJvttz9QW
Alguém pode explicar porque é lento?
Dados fictícios para teste:
If object_id('tempdb..#ResultSet') is not null
drop table #ResultSet
;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT CONVERT(varchar(255), NEWID()) as Sector,cast('' as varchar(1000)) as MajorSector
into #ResultSet
FROM Tally
where n <= 242906 -- my original table record count
ORDER BY n;
Nota: Como não são meus dados originais, os horários que mencionei acima podem ser um pouco diferentes. Ainda assim, a consulta de atualização única é muito mais lenta que as três primeiras.
Tentei executar as consultas mais de 10 vezes para garantir que fatores externos não afetem o desempenho. Todas as 10 primeiras três atualizações foram executadas muito mais rapidamente do que a última atualização única.
A primeira única atualização lê e grava todas as linhas da tabela. A segunda e a terceira leem e reescrevem um subconjunto dessas linhas. Olhe para o
Actual Number of Rows. Quando as três instruções são combinadas em uma, o otimizador calcula que, se ele tiver que ler tudo para satisfazer a primeira alteração, ele poderá aproveitar isso para a segunda e a terceira alteração.Dê uma olhada na versão XML dos planos de consulta, especificamente os
<ComputeScalar>operadores e<ScalarOperator ScalarString="">partes. No plano original, você verá que cada um é relativamente simples e mapeia muito de perto o SQL. Para o plano tudo-em-um é um monstro. Este é o otimizador reescrevendo o SQL em uma forma logicamente equivalente. Um plano funciona 1 passando cada linha pelos operadores uma vez. O otimizador está fazendo todo o trabalho necessário para satisfazer todas as três alterações à medida que essa linha passa uma vez.Eu esperaria que a segunda consulta fosse mais rápida porque os dados são lidos e gravados apenas uma vez, enquanto na primeira são tocados três vezes.
Como a segunda consulta não tem predicados (sem cláusula WHERE), o otimizador não tem escolha a não ser ler cada linha e processá-la. Estou surpreso que a segunda forma demore mais do que a primeira. Ambos estão começando a partir de buffers limpos? Há outro trabalho acontecendo no sistema? Como está lendo e gravando em uma tabela temporária, o IO está acontecendo no tempdb. Existe crescimento de arquivo ou algo assim acontecendo?
Por uma medida, você alcançou o resultado desejado. Você diz que deseja fazer alterações "para que o IO possa ser reduzido". O all-in-one faz menos IO do que as três instruções separadas no total. Eu suspeito que o que você realmente quer, no entanto, é reduzir o tempo decorrido, e isso obviamente não está acontecendo.
1 mais ou menos, muitos detalhes omitidos.
Executei sua rotina para gerar dados de teste e executei as três instruções de atualização única e a instrução tudo-em-um. Embora existam algumas diferenças (sem índice clusterizado, sem paralelismo), recebo mais ou menos os mesmos resultados. Especificamente, os planos têm aproximadamente a mesma forma e as três consultas individuais são concluídas em cerca de dois segundos e a única consulta grande em cerca de trinta a trinta e cinco segundos.
eu coloco
Com o plano em cache e os dados na memória, recebo:
Eu removi alguns bits que não são relevantes. Como
physical readsé zero para todos os três, a tabela cabe na memória.logical readsé o mesmo para os três, o que faz sentido. Como não há índices, a única abordagem é varrer todas as linhas da tabela. A segunda e a terceira consulta afetam zero linhas porque eu já as executei algumas vezes. O tempo de CPU e o trabalho decorrido são de 2500ms.Para a consulta maior é
O mesmo número de páginas é lido, o mesmo número de linhas é atualizado. A grande diferença é o tempo de CPU. Isso se reflete na observação casual do Gerenciador de Tarefas, que mostra 30% de utilização durante a execução da consulta. A questão é, por que é preciso tanto?
As consultas individuais separadamente têm cálculos simples e duas das instruções têm predicados que reduzem bastante o número de linhas tocadas. O otimizador tem boas heurísticas para processá-las e encontra um plano rápido. A consulta tudo-em-um aplica o monstro
Compute Scalarcontra cada linha. Minha sugestão é que, por qualquer motivo, o otimizador não pode desvendar a lógica em um plano que seja rápido de executar e acabe usando muita CPU. O otimizador tem que trabalhar com o que é fornecido, que no segundo caso é SQL complexo e aninhado. Talvez refatorando o SQL o otimizador siga diferentes heurísticas e alcance um resultado melhor? Talvez alguns índices (filtrados) ou estatísticas (filtradas) o convençam a escrever um plano diferente. Talvez colunas computadas persistentes ajudassem. Talvez você só precise dar ao otimizador o que ele precisa e sua primeira tentativa realmente é a melhor que pode ser alcançada e você precisa encontrar uma maneira de executar esses três em paralelo. Desculpe, eu não posso ser mais científico.Por favor, tenha mais cuidado com seus dados de teste no futuro. Os planos de consulta indicam que você tem um índice clusterizado em sua tabela, mas sua tabela temporária não possui um índice clusterizado. Isso pode fazer uma grande diferença em alguns casos. Na minha máquina, as três
UPDATEabordagens são executadas em 3 segundos e aUPDATEabordagem única é executada em 5 segundos. Não perto da diferença que você vê, mas ainda parece um pouco contra-intuitivo. O single não deveriaUPDATEser mais rápido?Como Michael Green apontou em sua resposta , o problema aqui é com o operador escalar de computação. O otimizador de consulta não é muito bom em estimar custos para escalares de computação. Os planos de consulta para a primeira atualização do conjunto de três e a segunda atualização solo podem parecer idênticos, mas há uma grande diferença em quanto trabalho o escalar de computação faz. Podemos realmente pegar o código e fazer algumas alterações para transformá-lo em uma
SELECTconsulta válida. A consulta é enorme e o código completo está aqui . Abaixo está uma versão bastante abreviada:Todos esses cálculos repetidos nas
CASEdeclarações não são bons. Como parte da computação escalar, o SQL Server pode executar os mesmos cálculos repetidamente. Se eu executar isso como apenas umSELECT, leva cerca de 3 segundos, que é o tempo para o primeiro conjunto deUPDATEconsultas.Colocar cálculos escalares repetidos em uma
APPLYtabela derivada geralmente pode melhorar a legibilidade das consultas. Em alguns casos, também pode melhorar drasticamente o desempenho. Peguei essa consulta grande e a simplifiquei um pouco movendo expressões repetidas para umaAPPLYtabela derivada. Outras simplificações são possíveis, mas isso deve lhe dar a ideia básica:Agora, a
SELECTconsulta é executada em menos de 1 segundo. Eu useiOUTER APPLYpara que o SQL Server calculasse tudo naAPPLYtabela derivada uma vez para cada linha, em vez de reduzi-la no escalar de computação. A computação escalar ainda está no plano de consulta, mas faz muito menos trabalho do que antes:Se eu conectar esse código ao CTE para sua
UPDATEconsulta, recebo os seguintes números de desempenho:Isso é um pouco mais rápido que o conjunto original de três consultas:
Pode ser possível otimizar ainda mais a consulta individual, mas deixarei isso para você.
Parece-me que a segunda e a terceira consultas podem ser reescritas usando estas fórmulas:
No entanto, isso não ajuda muito se
MajorSectornão for indexado (e em uma tabela temporária, não for indexado) ou se estes constituírem todas as linhas da tabela.No entanto:
Se o termo ( = dígito) ou ( = letra) ocorrer apenas no início da string, você poderá operar em all :
'(0n)'n'(n)'nMajorSectorMas com muito mais REPLACE (36).
No entanto, isso se transformará
'(00)A(01)B(02)C'em'ABC'- não no que é desejado. Se esses dados não ocorrerem, considere-os.Se cada
MajorSectorcomeça com'(0n)'ou'(n)'a ser removido - ou não contém nada')'- então realmente você só precisa,onde
maxLengthé o comprimento definido de,MajorSectorpor exemplovarchar(255). Se o parâmetro de comprimento emSUBSTRING()for maior que os dados reais,SUBSTRING()contenta-se em retornar os dados do deslocamento indicado até o final da string.