Archive

Archive for the ‘Performance’ Category

Conhecendo um pouco de performance

Fala Galera, boa noite.

Tudo bem? Faz tempo neh!? Hoje não colocarei o desafio de T-SQL (não ainda..rs). Hoje vim pra divulgar um assunto que também estava ficando um pouco desatualizado ,mas, como algumas pessoas não conseguiram comparecer e algumas não ficaram sabendo, estou divulgando aqui no blog.

No dia 03/12 tivemos a última reunião, do ano de 2015, do SQLManiacs  organizada pelo Vitor Fava, onde eu falei um pouco de performance e tuning (novidade..) utilizando alguns cenários práticos do dia-a-dia.

Para quem tiver interesse em reproduzir todos os cenários descritos na reunião, basta fazer o download dos scripts através do link abaixo:

Reunião do SQLManiacs – Conhecendo um pouco de performance

Ou ir até o blog do Fava, pois, lá possui mais algumas informações, fotos e etc.

https://vfava.wordpress.com/2015/12/08/reuniao-do-sqlmaniacs-conhecendo-um-pouco-de-performance-3/

Muito Obrigado e até a próxima

 

 

Criando Relatório de Performance e Tuning

Olá pessoal.

Durante o evento do SQL Saturday gostaria de ter mostrado como nós criamos relatórios de performance utilizando arquivos .blg gerados pelo Perfmon. Como não foi possível, estou escrevendo essa dica aqui no blog.

Primeiramente é muito comum em uma análise de performance nós utilizarmos o perfmon para coletar contadores de performance, pois, o mesmo não gera um alto “overhead” para o ambiente e pode lhe mostrar de forma clara aonde está o problema (se você souber relacionar os dados do Perfmon, é claro), no entanto, não mostra qual é a causa raiz do problema. Para esse artigo, não temos a intenção de mostrar como é criado a coleta e o PORQUE dos contadores (ver as referências no fim do artigo), isso será feito em artigos adiantes que irei escrever aqui. O principal objetivo deste post é mostrar: COMO CRIAR UM RELATÓRIO em uma coleta já realizada com o Perfmon.

No meu exemplo utilizarei o arquivo TracePerfmon.blg que deixei realizando uma coleta na minha máquina, conforme pode ser visto abaixo:

 

Figure 1 – Gráfico do Perfmon padrão

 

Agora imagine o presidente da sua empresa ou o seu gerente olhando para esse gráfico para tentar relacionar os dados! Você não verá uma cena muito agradável…rs.

Como posso transformar essas informações de uma maneira que seja fácil para ler? Para quem ainda não conhece, existe uma ferramenta via linha de comando chamada “relog” a qual utilizaremos nesse artigo.

 

 

Utilizando o relog.

O relog extrai informações dos contadores de performance do perfmon e pode transformá-los em outros formatos como text-TSV, text-CSV, SQL e etc.

Abra o prompt de comando do SQL Server e digite o seguinte comando: relog/?

 

Figure 2 – Instrução “Help” do relog

Aperte “Enter“. Será exibido um help dos parametros do relog e algumas sintaxes de como você pode utilizar o comando.

 

Figure 3 – Exemplos de utilização do relog.

Obs: Nesse post não entraremos no mérito de explicar o que significa cada parâmetro, no entanto, existe uma referência do relog no fim do artigo.

Transformando o binário em CSV

Agora que sabemos utilizar o relog, precisamos ir até a pasta onde encontra-se nosso arquivo .blg para poder transformar o arquivo em .CSV.

Figure 4 – Caminho de onde está o arquivo .blg

 

Agora que naveguei até a pasta basta apenas digitar o comando abaixo e apertar o “Enter” para que o arquivo .CSV seja gerado.

 

Figure 5 – Transformação do .blg para .csv, após o relog

 

A hora da Mágica

Com o arquivo .CSV gerado nós iremos importá-lo no excel, para isso, abra o excel e clique em “Data” e “From Text“.

 

Figure 6 – Importando dados no excel através de fontes de texto.

 

Localize o arquivo .CSV que foi gerado através do comando relog e clique em “Importar“. Aparecerá uma tela com o primeiro passo da importação do arquivo, mude para “Delimited” e clique em next, sua tela deve ficar como a imagem abaixo:

 

Figure 7 – Passo 1 da importação

 

A tela seguir irá definir quais caracteres são os seus “delimitadores“, deixe a tela como a tela a seguir:

 

Figure 8 – Passo 2 da importação

O ultimo passo apenas determina a formatação das colunas, deixe como está, no caso “General” e clique em “Advanced“. Uma tela irá se abrir:

 

Figure 9 – Definindo separadores.

Esse é um ponto extremamente importante, pois, dependendo do idioma utilizado essas infomrações podem vir divergentes, causando uma pequena mudança no momento da formatação dos dados. Deixe o separador de decimal como “Ponto” e o separador de milhares como “virgula” e clique em “OK” e “Finish“.

Criando os Gráficos

Após o arquivo ser importado para o excel, remova a segunda linha da planilha, conforme pode ser visto na imagem abaixo:

Figure 10 – Removendo a segunda linha da planilha

 

Após a linha ser removida, formate a primeira coluna da planilha excel para data utilizando o “Custom Format” das células. Sua data deve ficar algo parecido como no “Sample“.

 

Figure 11 – Formatação Excel da coluna de Data.

 

No momento que a nossa data estiver formatada, apenas precisamos adicionar uma nova coluna no arquivo excel, que será chamada de “Hora“.

Nós iremos extrair da coluna da data formatada anteriormente apenas as horas para que possamos fazer nosso relatório de “hora-em-hora“. Poderíamos fazer também de “minutos-em-minutos“. Lembrando que as colunas que serão transformadas em “Horas” deve estar como “General“, conform imagem:

Figure 12 – Aplicando a função de hora para a nova coluna

 

Após o passo anterior realizado, seu arquivo de excel deverá ficar mais o menos com o arquivo abaixo:

 

Figure 13 – Arquivo Excel “pronto” com a coluna de minutos incluso.

 

No momento que as colunas estão criadas, apenas precisamos criar um “Pivot Chart e Pivot Table” para montar os gráficos:

 

Figure 14 – Criando a Pivot Table e o Gráfico

A seguinte tela aparecerá, apenas informe o “Range” das informações que farão parte do gráfico, no nosso caso, todas as células. Deixe como a seguir e clique em “OK“.

 

Figure 15 – Determinando quais informações farão parte do gráfico e da tabela.

 

Agora os Mestres em Excel podem utilizar a sua imaginação para fazer relatórios necessários com os contadores de performance. Como não faço parte deste grupo seleto de mestres, apenas criei um relatório básico que: mostra as informações da “Média de Batch Requests/sec por hora” do período da coleta.

 


Espero que tenham gostado!!!

Até a próxima

Thiago Carlos [TC] de Alencar

 

 

Referências

Relog:

http://technet.microsoft.com/en-us/library/bb490958.aspx

Performance Counters PDF

http://www.quest.com/techbrief/sql-server-perfmon-counters-poster811635.aspx

Criando coletores de performance

http://technet.microsoft.com/en-us/library/cc749337.aspx


 

Web Cast – Estatísticas no SQL 2012/2014- Arquivos e Video

Pessoal boa tarde.

Para quem não pode comparecer no Web Cast sobre estatísticas que apresentamos ontem, estou disponibilizando o link do video no Youtube que foi feito upload pelo Marcos Freecia.

https://www.youtube.com/watch?v=7jSPo6HKMog&feature=youtu.be&list=UUI4WHxHE8xRAnhCbCBiGbzQ

Espero que gostem.

WebCast_Estatisticas2014 – PPT

Bom fim de semana a todos.

Categories: Performance

Erro – STATISTICS IO em Planos Paralelos.

Fala pessoal, beleza?

Estava otimizando uma query nessa tarde, onde a mesma utiliza uma clausula TOP e o seu respective plano era um plano paralelo. Visando identificar onde estavam as maiores leituras dos objetos dessa query utilizei o comando SET STATISTICS IO ON.

Quando fui avaliar na guia “messages” do SSMS (SQL Server Management Studio) percebi que a quantidade de leituras lógicas retornadas pelo comando SET STATISTICS IO ON era um pouco menor do que eu realmente esperava, simplificando: A minha query retornava “X” registros de uma tabela “Y”, no entanto, a quantidade de leituras eram muito menor do que eu tinha no meu retorno do SSMS.

Nesse meio tempo ouvi uma voz me dizendo: “Thiago tenta colocar o MAXDOP(1) pra ver se a query executa mais rápido”. Levando em consideração o conselho, executei a mesma query com o MAXDOP(1), portanto, esperaria ver a mesma quantidade de leituras lógicas que vi anteriormente, correto? Errado.

Quando executei a query utilizando o MAXDOP(1) percebi que a quantidade de registros eram a mesma, no entanto, as leituras lógicas eram diferentes. Pesquisando sobre esse comportamento no BING, encontrei um “Connect Item” da Microsoft que realmente isso é uma comportamento inesperado dentro do produto, conforme abaixo:

https://www.beta.microsoft.com/SQLServer/feedback/details/767250/statistics-io-under-reports-logical-reads-for-parallel-plans

Execute os comandos abaixo para poder similar o mesmo problema que encontrei:

USE AdventureWorks2012;

GO

 

SET NOCOUNT ON;

SET STATISTICS IO ON; –Liga as Estatisticas de IO

DBCC SETCPUWEIGHT(1000) WITH NO_INFOMSGS –O comando garante que o plano paralelo seja utilizado.

GO

SELECT TOP 15000 * FROM Sales.SalesOrderHeader WHERE OrderDate < ‘20080101’;

SELECT TOP 15000 * FROM Sales.SalesOrderHeader WHERE OrderDate < ‘20080101’ OPTION (MAXDOP 1);

DBCC SETCPUWEIGHT(1) WITH NO_INFOMSGS;

Mais porque esse comportamento acontece?

Quando um plano paralelo tem uma clásusula TOP, por algum motive o SQL Server pode encerrar “threads” anteriormente se existem linhas suficientes (não possuo o valor desse threshold) para serem retornadas para o usuário. Esse encerramento interfere com a coleta de estatisticas do comando SET STATISTICS IO, ao contrário, do que acontece com plano sem paralelismo. Quando isso acontece, possivelmente nós veremos a quantidade reportada de IO para um plano paralelo menor do que a quantidade de IO reportado para um plano serial

Obs: Esse comportamento foi corrigido no SQL Server 2014.

Espero que tenham gostado.

Categories: Performance

Parallel SELECT… INTO – SQL Server 2014

Hoje apareci aqui no blog apenas para dar uma dica bem básica. Atualmente o Microsoft SQL Server está na versão 2014. Essa nova versão trouxe muitas features FANTÁSTICAS que possivelmente justifica uma migração da versão anterior (SQL 2012) para a versão atual (SQL 2014).

Uma das deficências das versões anteriores é que não era possível realizar operações de SELECT… INTO em paralelo e em algumas situações pode degradar um pouco do desempenho dessa operação.

Imagine um cenário onde você deve realizar uma operação de inserção de milhões de linhas em uma tabela de “stage” toda noite. E o resultado dessa tabela será trasnformado para um ambiente de data warehouse. Provavelmente essa operação de INSERT seria mais eficiente se fosse feito de forma paralela. Á partir do SQL 2014 esse tipo de operação é possivel, como podemos ver no link abaixo:

http://blogs.technet.com/b/italian_premier_center_for_sql_server/archive/2013/07/24/sneak-peek-on-sql-2014.aspx

A pergunta é: O paralelismo sempre vai me ajudar? A principio eu diria que DEPENDE, pois, pode haver alguns problemas se o paralelismo for utilizado de forma incorreta. Na internet existem diversas “threads” sobre o assunto,mas, recentemente li um artigo que explica de forma muito clara a questão do paralelismo dentro do SQL Server conforme podemos ver no link abaixo:

http://blogs.msdn.com/b/pfebrasilsql/archive/2014/08/21/max-degree-of-parallelism-cxpacket-maxdop.aspx

O principal ponto que gostaria de mostrar aqui é que, caso, exista um cenário onde possivelmente a “feature” de Parallel SELECT… INTO pode estar me prejudicando eu posso “desligar” esse comportamento.

O trace flag 9492 pode ser utilizado para o próposito de desabilitar essa nova feature dentro do SQL Server, lembrando que apenas utilize essa opção avançada se você REALMENTE SABE o que está fazendo. Pois, novas features geralmente sempre trazem melhorias e não o comportamento contrário. Lembre-se, TESTEs são VITAIS.

Obs: Para banco de dados de nível de compatibilidade 110 e superiores o Parallel SELECT … INTO podem ser automaticamente paralelizada se necessário.

Espero que tenham gostado.

Até a próxima.

Categories: Performance

Problemas de Performance com Tabelas variáveis?

Tabelas variavéis apareceram no SQL Server com a intenção de reduzir compilações tornando-se bem popular com sua frequente utilização e em alguns casos mais do que as tabelas temporárias.

A sua famosa utilização deu-se devido ao mito que: “Tabela variável fica na memória e tabela temporária fica no tempdb”. Execute o código abaixo e verifique que mesmo para a tabela variável o SQL Server aloca espaço no tempdb fisicamente.

USE tempdb

go

declare @Tabela table (cod int not null primary key clustered (cod))

insert into @Tabela values(1),(2)

select sys.fn_PhysLocFormatter(%%physloc%%) FROM @Tabela

No entanto, ambos tipos de tabela possuem suas particularidades, pós e contras. Existem diferença entre tabela variável e tabela temporária? Sim várias.

O objetivo desse post não é falar sobre a diferença de ambas, mas, no final do post tem algumas referências sobre o assunto. Recomendo a leitura

O principal problema que já tive com tabelas variáveis foi devido a criação de planos de execução ineficiente, pois, quando uma query é compilada no SQL Server o número de linhas da tabela variável é “desconhecido”. Esse comportamento pode fazer com que o otimizador de consulta realize uma estimativa que pode não ser a melhor trazendo problemas no momento da geração do plano de execução já que a cardinalidade para tabelas variáveis são sempre 1. Eu escrevi sobre isso neste artigo.

A partir do SQL Server 2012 SP2 uma nova trace flag foi adicionada para ajudar nesse ponto.A trace flag 2453 faz com que o SQL Server detecta “linhas suficientes” sendo inseridas em uma tabela variável e faz com que seja disparado uma recompilação da instrução T-SQL para produzir um plano de execução mais eficiente.

Em que cenário a utilização dessa trace flag se aplicaria? Imagine em um ambiente que existem diversos objetos de programação criados e muitos deles apresentam problemas de desempenho devido a utilização da tabela variável. Para ter que alterar todos, isso pode demandar uma boa quantidade de tempo, nesse caso, seria interessante avaliar a utilização do Trace Flag 2453. Ou até mesmo em um ambiente onde o código fonte é disponibilizados por fábricas de softwares terceira e você não possui permissão para alteração do código fonte para mudar as tabelas variáveis para tabelas temporárias

 

Referências:

http://blogs.msdn.com/b/psssql/archive/2014/08/11/if-you-have-queries-that-use-table-variables-sql-server-2012-sp2-can-help.aspx

 

http://epmxperts.wordpress.com/2010/06/23/myth-sql-server-table-variables-vs-temp-tables/

 

http://blogs.msdn.com/b/psssql/archive/2012/08/22/10053781.aspx

Até mais.

 

Categories: Performance

WebCast sobre Estatísticas – Convite

Pessoal, bom dia.

Gostaria de convidá-los para participar do WebCast sobre estatísticas no dia 4 de Setembro ás 21 horas. Esse WebCast tem como objetivo demonstrar de como o SQL Server estima a quantidade de registro para uma determinada query e o que problemas podem ocorrer quando as estimativas não são feitas de forma correta. Vem muita coisa legal por aí. Nos vemos lá, basta apenas se escrever no link abaixo:

https://attendee.gotowebinar.com/register/8985229173189713410

Se houver algum problema, por favor, me avisem!!!

 

Categories: Performance

Sort Warnning e agora?

No ultimo evento SQL SAT 284 nós citamos sobre alguns pontos importantes que podem comprometer a performance das nossas queries, sendo eles: cache bloat, indexação e Sort Warnning. No post de hoje, mostrarei como identificar um sort Warning e como corrigi-lo.

O que é um Sort Warnning ?

Simplificando um sort warnning ocorre quando o SQL Server realiza uma operação de ordenação e a memória estimada para o operador de ordenação não é o suficiente fazendo com que o SQL Server realizae um “spill” para o tempdb. Esse tipo de operação é extremamente custoso para o SQL Server.

Abaixo vamos montar o nosso cenário:

Imagem

O script acima para gerar os 4 bilhões  – Itzik Ben Gan.

Atualizaremos as estatísticas da tabela com Full Scan e então executaremos o T-SQL abaixo para sabermos qual a média de registros que existe com um Id menor que 3500.

Imagem

Agora como identificar um Sort Warnning?

Até o SQL Server 2008 R2 é possivel identificar a operação de Sort Warnning pelo profiler, porém, ele apenas mostra que uma operação de Sort Warnning ocorreu, mas, não mostra qual foi a instrução que levou o evento à acontecer. A partir do SQL Server 2012 é possível identificar a instrução que disparou o evento realizando a coleta com os XEvents.

Irei abrir um SQL Profiler e coletarei apenas o evento de Sort Warning antes de executar a query para analisarmos se o mesmo será gerado ou não.

Imagem

Agora irei executar a query abaixo:

Imagem

E podemos ver que o plano de execução da query possui um operador de sort, porém, o mesmo não gerou o evento de Warnning.

Plano de Execução:
Imagem

 

Não existe eventos no Profiler:
Imagem

Obs: Se executaros a query acima seguido de um select na DMV sys.dm_io_virtual_file_stats, podemos perceber que os bytes de reads/writes da tempdb não sofrem qual mudança.

Imagem

Podemos observar que o tempdb não sofreu alterações no momento da operação de Sort, isso significa que a operação de Sort está sem feira na memória:
Imagem

 

Se modificarmos o código de 3000 para 3500 podemos ver que o SQL Server gerou um evento de Sort Warnning no SQL Profiler.Imagem

Profiler:
Imagem
Para quem está acostumado a olhar planos de execução, pode perceber que desde o SQL Server 2012 tivemos uma pequena mudança, porém, muito importante já que através do plano de execução podemos ver que a operação de Sort disparou um evento.

Imagem

Dando uma olhada no evento detalhadamente, podemos perceber que a mensagem é clara dizendo que o SQL Server gerou o evento na tempdb:
Imagem

Agora porque o problema ocorreu?

Como podemos perceber na primeira query o SQL Server estimou o “Memory Grant” de 8040 para executar uma ordenação em 2999.94 registros (estimado).

Imagem

Obs: Não entraremos no mérito do porque da estimativa e etc. Essa abordagem faremos em um futuro artigo de Estatísticas.

Para a segunda query quando mudamos o ID do predicado da consulta, temos um “Memory Grant” de 9296 para executar uma ordenação de 3499.93 registros. Como o Memory Grant não foi suficiente o SQL Server decide fazer um “Spill” para o tempdb, pois, realmente é melhor do que ele retornar a seguinte mensagem para a aplicação: “Não consegui Ordenar por falta de recurso”.

Imagem

Como podemos resolver  esse problema? De uma maneira bem básica é criar um índice na coluna e ai o problema se acabar. Infelizmente esse não é o caso, vamos ver? Crie o índice abaixo e execute novamente a query:

Create Index:
Imagem

Resultado da Query
Imagem

O real problema aqui é que a quantidade memória não é suficiente para ordenação mesmo criando o indice. Thiago qual a solução para esse caso? Pense o seguinte: Que bom seria se o SQL Server estimasse uma quantidade maior de recurso se a linha retornada tivesse um tamanho “maior” no número total de bytes.  O que precisamos fazer é dizer ao SQL Server que o tamanho da linha é retornada é maior em bytes do que realmente é, sendo assim, é necessário que o SQL Server estime uma quantidade de recurso maior do que o normal.

Imagem

Agora veja o plano gerado:

Imagem

E de quanto o SQL Server estimou agora:

Imagem

Por enquanto é só. Em alguns outros artigos entramos em alguns conceitos importantes, nesse artigo apenas quis mostrar o evento de Sort Warnning e como identifica-lo e resolve-lo. Segue algumas referências que me ajudaram na criação desse artigo:

http://www.sqlpassion.at/archive/2011/10/19/query-memory-spills/

http://amihalj.wordpress.com/2011/12/05/sql-server-2012-detecting-sort-warnings-with-extended-events-sort_warning-event/

http://www.solidq.com/identifying-solving-sort-warnings-problems-sql-server/

 

 

 

 

 

 

 

Categories: Performance

O SQL Server estimou errado?

Olá Galera, boa noite!?

Hoje venho falar sobre um assunto que até agora pouco eu desconhecia, então decidir compartilhar esse conhecimento com a comunidade SQL Server.

Como de costume dedico esse post a um amigo e Super DBA SQL Server, Igor Antônio. O cara é um Guru de TI, tudo que perguntei ele soube me responder ele é um SQL Geek NATO.

Começando a “brincadeira”, A pergunta inicial é a seguinte: O que é melhor uma tabela temporária ou uma tabela variável? Na área de exatas e falando de SQL Server a resposta sempre vai ser DEPENDE..rs. Particularmente gosto muito de tabelas temporárias, mas, gostaria de falar aqui sobre uma diferença entre elas, um dos principais motivos que sempre utilizei as tabelas temporárias foi por causa das estatísticas que influenciam bastante no plano de execução. O que sempre ouvimos é que tabela variável sempre estima um registro, certo? ERRADO. Como assim Thiago?

Execute o script abaixo com o plano de execução habilitado (Ctrl + M)

USE tempdb

go

DECLARE @T TABLE (COD INT IDENTITY PRIMARY KEY CLUSTERED, VALOR INT NOT NULL)

INSERT INTO @T (VALOR) VALUES(1),(2),(3)

SELECT * FROM @T

 

Plano1

 

Como o SQL Server estimou apenas uma linha, o otimizador de consulta pode estimar um plano ineficiente em caso de JOINs com outras tabelas e a primeira sugestão seria: mudar para uma tabela temporária. Porém, e possível fazer com que uma tabela variável estime mais que uma linha com a utilização do OPTION (RECOMPILE), será? Vamos novamente executar o script com o HINT e ver o resultado:

USE tempdb

go

DECLARE @T TABLE (COD INT IDENTITY PRIMARY KEY CLUSTERED, VALOR INT NOT NULL)

INSERT INTO @T(VALOR) VALUES(1),(2),(3)

SELECT * FROM @T OPTION (RECOMPILE)

Plano2

Essa é nova pra mim Thiago? Confesso que pra mim também… O que aconteceu?

O SQL Server estima a quantidade de linhas que pode ser retornada em tempo de execução e sabe que na nossa tabela variável vai retornar três registros. Que dizer que toda vez que existir uma tabela variável em uma query sempre vou utilizar o hint? Galera sempre façam testes, pois, dependo da situação pode valer a pena ou não. Em um caso que você sabe que a tabela variável sempre vai retornar uma quantidade de registros exata para uma query o plano é “bom o suficiente” talvez seja uma boa que você paga menos recompilação ao contrário da tabela temporária, porém, isso é assunto para outro post.

Até mais espero que tenham gostado

 

Categories: Performance

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