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

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

  1. May 28, 2013 at 5:48 pm

    Faltou um espaço onde tem “@Xxml” para separar o nome da variável do tipo de dados!

    No mais, ótima solução!

    • May 28, 2013 at 6:19 pm

      Muito Obrigado magalhaesv. Eu ainda pretendo editar um pouco esse post, coloquei meio na correria pra “salvar” um amigo. Valeu pela dica.

  2. May 28, 2013 at 9:40 pm

    Quando o assunto é performance o TC destroi sem piedade!!!

  3. Alex Félx
    May 30, 2013 at 8:33 pm

    Tiago, primeiro que Deus abenções, pessoas como você, estou iniciando este buraco negro que é o mundo do SQL, estou adorando, sei que o caminho é longo, muito longo, tenho muito que aprender, de cara comecei a ver seu blog sei que será um input valioso em minha carrreira, muito bom valeu mesmo, segunda feira dia 03 de de junho de 2013 estarei presente em sua aula do 6232 e quero aprender muito, valeu brow!!!

  1. April 28, 2014 at 3:31 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: