Archive

Archive for May, 2013

T-SQL – Simulando o FN_SPLIT sem uso da função

Galera, boa tarde.

Para os meus amigos SQL Geeks, dedico esse post para Paula Patricia, uma super DBA SQL Server e uma grande amiga.

Hoje eu vim aqui falar de um assunto “inacabado” que apareceu em uma das turmas em que ministrava um treinamento sobre SQL Server. Falando sobre de como o SQL Server é otimizado para trabalhar com o que chamamos de “set-based” ao invés de processamento linha-a-linha.

Um aluno me disse: Thiago tem um cursor que executa um processamento “X” para um processo que processa algumas informações de estoque de forma hierárquica. A primeira sugestão que fiz foi: A alteração de um cursor para utilizar um While que lhe traria o mesmo resultado sem muita decodificação, porém, eu sabia que o resultado, poderia ser alcançado utilizando outro recurso foi que lhe pedi para que ele me trouxesse o código para analisarmos e mudar o While para uma instrução baseada em lote.

Depois de alguns minutos olhando o código cheguei à conclusão do que ele realmente precisava e de como poderíamos chegar ao resultado com uma CTE Recursiva (Para quem está lendo, irei levar em consideração que você conhece CTEs. No final desse post existem links para todos os assuntos tratados aqui).

Algum tempo de codificação por parte do aluno e… BAZINGA. A query estava retornando os valores, porem, a string que foi montada era utilizada como parâmetro de entrada para outra procedure. No que resultou em uma variável do tipo varchar(max)

Ex:

DECLARE @ListaParametrosEntrada varchar(max)= ‘18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853

Obs: Existem muitas outras soluções para resolver esse problema, para esse artigo apenas será abordado uma delas, mas, farei outros que abordam diversos resultados considerando performance.

Agora que começa a ficar interessante…

Imagine o cenário: Para cada Valor separado por vírgula dentro da variável @ListaParametrosEntrada deve ser desmembrado para que seja utilizado dentro de uma variável do tipo de dados inteiro em uma clausula WHERE com o operador de igualdade (=).

Ex:

where id_operacao_origem= @I

No código que encontrei, existia um While que verificava o seguinte: “enquanto houver valores separados por vírgula…quebra esse valor e coloque em uma variável,conforme demonstrado acima.

Então podemos avaliar que: Se eu tiver Um milhão de linhas, vou fazer isso Um milhão de vezes? A resposta é sim. Pode parecer estranho, mas, muitos programados têm uma forma de pensar serializada para resolver os problemas e essa é uma forma equivocada quando falamos de SGBD, pois, estamos nos referindo a um conjunto de dados (No fim do post tem um ótimo artigo do Fabiano Amorim sobre o assunto).

O código abaixo demonstra a lógica utilizada para o que foi visto até aqui:

DECLARE @ListaParametrosEntrada varchar(max)= ‘18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853’

DECLARE @I VARCHAR(MAX)

if right(@ListaParametrosEntrada, 1)<> ‘,’

begin

set @ListaParametrosEntrada= @ListaParametrosEntrada + ‘,’

end

while charindex(‘,’, @ListaParametrosEntrada)<> 0

BEGIN

set @I= ltrim(rtrim(substring(@ListaParametrosEntrada, 1 , charindex(‘,’, @ListaParametrosEntrada)- 1)))

set @ListaParametrosEntrada= ltrim(rtrim(right(@ListaParametrosEntrada,len(@ListaParametrosEntrada)- charindex(‘,’, @ListaParametrosEntrada))))

SELECT @I

END

Resultado:

Print2

Procurei na internet e achei ma função chamada fn_Split, que parece ser bem famosa quando estamos falando desse cenário. A função realmente atende, porém existem diversos problemas de performance relacionado a funções que não serão cobertos nesse post. Quem dizer da uma olhada de como pode ser feito com a função.

http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx

Para evitar esse processamento linha-a-linha que não é a melhor opção, o ideal seria que cada valor fosse um registro armazenado em uma coluna de uma tabela, para que possamos realizar um JOIN com a coluna id_operacao_origem.

O código abaixo atende a este requisito

DECLARE @T TABLE (Cod int NOT NULL)

DECLARE @ListaParametrosEntrada varchar(max), @Split char(1), @X xml

SELECT @ListaParametrosEntrada = ‘18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853’,

@Split= ‘,’

SELECT @X = CONVERT(xml,'<root><s>’+ REPLACE(@ListaParametrosEntrada,@Split,'</s><s>’)+ ‘</s></root>’)

insert into @T(Cod)

SELECT CAST(T.c.value(‘.’,’varchar(20)’)AS INT)

FROM @X.nodes(‘/root/s’) T(c)

SELECT * FROM @T

Print3Galera, ate o feriado eu edito algumas coisas, arrumo a formatação e vejo os erros de portugues, por enquanto o importante é que o código “funfa”.

Abs

Advertisements

Create Index não é Tuning II

Esse post é dedicado a um grande amigo que me ensinou muito sobre Tuning e Performance um dos melhores DBAs que conheci, Sergio Bonsague.

Esse artigo é continuação do último post lançado aqui no blog. A coisa mais divertida no SQL Server é saber, porque ele fez isso ou aquilo? Por que “tal” decisão foi tomada, entender um pouco desse mundo obscuro…rs. Vamos parar de tagarelar e colocar a mão na massa.

Se analisarmos o plano de execução do post print, o SQL Server escolheu utilizar um Clustered Index Seek para ler toda a tabela. Porém, fica a pergunta, Ele não poderia ter escolhido um Clustered Index Scan? Como vou ler tudo, um Scan não é melhor que um Seek? A resposta é: Depende.

Executando duas vezes a mesma query, uma com o HINT WITH(INDEX=0) que força a Não utiliza do Index da tabela e uma sem utilização de hint, para que possamos comparar o antes e depois.

 

Executando a query forçando um Clustered Index Scan Vs um Clustered Index Seek podemos ver que no plano de execução o custo de ambos é  de 50% (Atual e Estimado).

ambosPlano

 

Thiago, que dizer que é a mesma coisa? A resposta é não. Quando se está avaliando performance, o ideal também é utilizar como parâmetro os tempos de execução, para isso sempre uso o Profiler. A quantidade de leitura é muito menor quando utilizamos um Clustered Index Seek, Vejamos o resultado:

EvidenciaProfiler

 

O SQL Server realizou uma quantidade menor de leituras utilizando o Index Seek. Mas, porque?

No Seek temos duas propriedades que não aparecem no Scan que no caso é a “Direção da Leitura”, como pode ser visto utiliza o “FORWARD” e está marcada como “ORDERED” como True.

ClusteredIndexSeek

 

Já o Clustered Index Scan, essas propriedades não são apresentadas. É a única questão que acredito ser o motivo.

ClusteredIndexScan

 

 

Esperam que vocês tenham gostado.

 

Create Index não é Tuning!

Boa noite, galera, tudo bem?

Muita saudades de postar, mas, a correria esta demais. Prometo que vai ser mais frequente daqui pra frente. Gostaria de dedicar esse post para a equipe do Oracle (Carlos Carvalho, Anderson Ferreira, Alessandro Andrieta e Ricardo Argona) que trabalha comigo, estou aprendendo muito com eles, por exemplo, que produto Não utilizar…rs, brincadeira.

Hoje gostaria de falar sobre um assunto que é muito comum no mercado de trabalho. O DBA cria um índice  elimina um Key Lookup e se diz “especialista” em performance. Não quero julgar ninguém, mas, performance e tuning vai muito mais além do que isso, e eu gostaria de demonstrar isso nesse post.

Tenho uma procedure que tem uma instrução UPDATE que faz parte de 56,7% de custo do total da execução da procedure:

update A 

SET A.SPRD_LIQ_ACUM_M_ATU  = B.RES_SPREAD_LIQUIDACAO,     

A.RES_REF_LIQ_ACUM_M_ATU = B.RES_GAP_LIQUIDACAO    

FROM      #TB_DATA_BASE A     

INNER JOIN  

( SELECT ESTOQUE.ID_OPERACAO_DRCG,   ESTOQUE.NO_PARCELA,   ESTOQUE.IC_PAG_REC, 

RES_SPREAD_LIQUIDACAO = SUM(ESTOQUE.VL_RES_SPREAD_LIQUIDACAO),  

RES_GAP_LIQUIDACAO  = SUM(ESTOQUE.VL_RES_GAP_LIQUIDACAO) 

FROM  ESTOQUE_RES_RF ESTOQUE WITH(NOLOCK) 

INNER JOIN #TB_DATA_BASE A    ON ESTOQUE.ID_OPERACAO_DRCG = A.ID_OPERACAO_DRCG 

WHERE ESTOQUE.DT_ESTOQUE <= @PDT_BASE AND A.NO_PARCELA = ESTOQUE.NO_PARCELA     

AND A.IC_PAG_REC = ESTOQUE.IC_PAG_REC     

GROUP BY  ESTOQUE.ID_OPERACAO_DRCG,   ESTOQUE.NO_PARCELA,   ESTOQUE.IC_PAG_REC) AS B   

ON A.ID_OPERACAO_DRCG = B.ID_OPERACAO_DRCG     

AND A.NO_PARCELA = B.NO_PARCELA     

AND A.IC_PAG_REC = B.IC_PAG_REC

Analisando o plano de execução , podemos notar que a query que mais consomem mais recurso do plano. Vamos primeiro avaliar o maior vilão que no case é o update demonstrado anteriormente. Como pode ser visto no print, o SQL Server está realizando um Clustered Index Seek na tabela estoque_res_rf e essa operação está fazendo uma leitura de 711.152.000 registros. Porém, no final do processo, podemos perceber que o SQL Server apenas precisa de 48.558 registros. Esses registros são frutos do Join com a tabela temporária #TB_DATA_BASE. Thiago, que dizer que estou lendo mais registros do que preciso? A resposta é: SIM.

PlanoAtualSentry

Analisando o histograma de estatística do SQL Server, podemos ver que a estatística está Atualizada, mesmo que não seja FULLSCAN. Então provavelmente o problema não foi causado por estatísticas desatualizadas. O SQL Server está fazendo exatamente o que deveria ser feito. Vamos ver? Executo o comando que mostra as estatísticas do Índice pk_estoque_res_rf da Tabela ESTOQUE_RES_RF, que foi requisitada no print anterior.

 DBCC SHOW_STATISTICS (ESTOQUE_RES_RF,pk_estoque_res_rf)

O histograma abaixo, mostra a atualização das estatísticas e qual o seu último step no histograma do SQL Server que tem o RANGE_HI_KEY 2013-05-13 00:00:00.000, que é exatamente a Data Base utilizada como filtro para os comandos de UPDATE (descrito no inicio deste documento).

Estatistica

O filtro da query está utilizando um operador de “<=  @dataBase”, e  O SQL Serve tem que estimar TUDO menor que isso,nessa ocasião ele realiza a soma das colunas RANGE_ROWS e EQ_ROWS para chegar nesse valor. Por exemplo: 1403920 + 750,6796 + 1413610 até o primeiro step do histograma, assim , podendo abranger todos os valores.

Realizando o cálculo no excel  a soma foi algo em torno de 712.633.500 registros (O Valor não é o mesmo que o do plano acima, pois, ele foi estimado ontem e os dados já não são mais os mesmos do momento do plano). Nesse caso, apenas criar um indice não resolveria, pois, o SQL Server continuaria lendo todas as informações menor que a data especificada no parametro e, provavelmente o SLQ Sever optaria para ler o indice que contem mais páginas de dados, no caso o Clustered.

Nesse caso a sugestão seria Avaliar a alteração da query para adicionar filtros mais seletivos, evitando assim, a leitura total do Indice. O que poderia ser feito é adicionar um filtro para trazer valor “>= “ a partir de uma data “X”.  Realizando um teste para a data ‘2013-04-01 00:00.00.000’ e tive o seguinte resultado:

Evidencia

A pergunta é: no primeiro print o SQL Server fez um Clustered Index Seek para ler toda as informações da tabela, não seria mais fácil apenas realizar um Clustered Index Scan? Ess será assunto para o próximo post. Eu irei mostrar porque o SQL Server escolheu o Seek ao invés do Scan, uma coisa é fato: Para esse caso o Seek é mais eficiente, a pergunta é porque?

Categories: Performance