Pessoal, vamos pensar set based ? Parte II–retirando processamento Row-Based sem alterar regra de negócios.


Vamos lá.

Estou num projeto bem interessante. Puro tuning. Resolvi colocar este post  pois um dos vilões que tenho visto é o processamento Row-Based . Não digo cursores pois apesar de APARENTEMENTE em alguns casos trocar o cursor por while é mais rapido (em relação ao cursor), no meu ponto de vista é trocar 6 por meia dúzia.

Continua sendo processamento Row-Based da mesma maneira. E nós sabemos MUITO bem que o Engine do SQL Server não é feito para trabalhar linha a linha.

Bom, sem mais delongas vamos a coisa prática…quem quiser saber mais sobre cursores, processamento  Row-Based ou, na famosa frase de Jeff Moden, RBAR: ‘Row By Agonizing Row’, sugiro bingar. Tem muita coisa espalhada por ai.

Na verdade esta é uma das ténicas que eu utilizo, quando não temos problema com Tempdb, tem seu proprio disco (ou conjuntos), etc. Existem várias maneiras de se pensar SET-BASED.

Estou colocando uma proc com um cursor (poderia ser while também)  . Tem algumas condições  dentro do loop simulando regras de negócio.

Eu coloquei 50 mil linhas de clientes, com a tool Data Generator da Redgate, fantástica..recomendo a todos que precisam montar massa de dados.

CREATE TABLE Clientes (CODIGO INT IDENTITY (1,1),Nome VARCHAR(100),ValorTotal FLOAT)
go

CREATE TABLE RowBased (CODIGO INT ,Nome VARCHAR(100),Valor1 float, Valor2 float)
go

CREATE TABLE RowBased_1(CODIGO INT ,Nome VARCHAR(100),Valor1 float, Valor2 float)
go

 

Agora a proc

CREATE   PROCEDURE usp_ProcRowBased

AS

BEGIN

    SET NOCOUNT ON

    DECLARE @Valor1 float
    DECLARE @Valor2 FLOAT
    DECLARE @Valor3 float   
   

    DECLARE @Codigo INT
    DECLARE @Nome VARCHAR(100)
    DECLARE @ValorTotal FLOAT
   
    –SET @valor1 = 100
    –SET @valor2 = 200
   
    DECLARE CCursor CURSOR LOCAL STATIC FORWARD_ONLY FOR
   
    SELECT    codigo,
            nome,
            valorTotal
    FROM    Clientes
   
    OPEN CCursor
   
    FETCH NEXT FROM CCursor
    INTO @codigo, @Nome,@ValorTotal

    WHILE @@FETCH_STATUS = 0
    BEGIN
   
        SET @valor1 = 100
        SET @valor2 = 200
        SET @valor3 = 0
   
        IF (@ValorTotal > 200)
            SET @valor2 = (@valor1 * @valor3) – @ValorTotal
        ELSE
            SET @valor2 = (@valor1 +  @valor3) + @ValorTotal   
       
           
           
        IF @valor2 < 700
       
            INSERT INTO RowBased (codigo,nome,valor1,valor2)
            VALUES (@codigo,@nome,@valor1,@valor2)
               
        IF @@rowcount > 0
       
            INSERT INTO RowBased_1 (codigo,nome,valor1,valor2)
            VALUES (@codigo,@nome,@valor1,@valor2)
   
        FETCH NEXT FROM CCursor
        INTO @codigo, @Nome,@ValorTotal
       
    END

END
CLOSE ccursor
DEALLOCATE ccursor

Rodando ela :

“Tentei colocar a picture mas pra variar, o live da pau. Bom, processou em 1:07 segundos”

Agora  vamos alterar para SET-BASED ?..neste caso eu coloquei um temporaria com o select do cursor e as variaveis de regra de negócio dentro do loop viraram campos da temporária. Desta maneira eu consigo trabalhar com conjuntos de linhas sem alterar a regra. Na verdade se a logica ta indo pra Porto Alegre para ir pra Bahia, vai continuar indo.

Como consultor, na maioria das vezes não temos tempo de entender a regra de negócio e as vezes nem precisamos ou não faz parte do escopo.

Create   PROCEDURE usp_ProcSetBased

AS

BEGIN

    SET NOCOUNT ON
   
    CREATE TABLE #TempClientes (codigo INT PRIMARY KEY CLUSTERED,nome VARCHAR(100),ValorTotal FLOAT,Valor1 FLOAT,Valor2 FLOAT,Valor3 FLOAT)
    CREATE TABLE #Output (codigo INT,nome VARCHAR(100),Valor1 FLOAT,Valor2 FLOAT)
   
    INSERT INTO #TempClientes (codigo,Nome,ValorTotal,Valor1,Valor2,Valor3)
    SELECT    codigo,
            Nome,
            ValorTotal,
            100,
            200,
            0
    FROM    clientes

   
    UPDATE    A SET A.Valor2 = CASE WHEN A.Valortotal > 200 THEN (A.Valor1 * A.Valor3) – A.valorTotal ELSE (A.valor1 +  A.valor3) + A.ValorTotal    end
    FROM    #TempClientes A
   

    INSERT INTO RowBased (codigo,nome,valor1,valor2)
    OUTPUT INSERTED.codigo,INSERTED.nome,INSERTED.valor1,INSERTED.valor2
    INTO #output
    select    codigo,
            nome,
            valor1,
            valor2
    FROM    #TempClientes
    WHERE    valor2 < 700   
   
    INSERT INTO RowBased_1 (codigo,nome,valor1,valor2)
    select    codigo,
            nome,
            valor1,
            valor2
    FROM    #output
   
end        
  

Percebam que tem uma parte do codigo em row based que é feito um insert na tabela RowBased e logo após visto se o @@rowcount > 0 (se foi inserido). Na verdade o exemplo que eu coloquei nao tem muita logica pegar o @@rowcount pois tudo vai ser inserido, mas imagine a situaçao que vc faz um not exists com alguma tabela para inserir somente esta condiçao (ou alguma outra regra). Você poderá ter linhas inseridas ou não.

Na soluçao Set-Based, eu usei a clausula output para pegar somente os caras que foram inseridos.

Vejama diferença de tempo na soluçao final

“Tentei colocar a foto novamente e pra variar, de novo, deu pau. Processou em 2 segundos”

De 1:07 minutos para 2 segundos. Gritante.

E não alteramos a regra bde negócio.

Bom galera, é isso ai. Claro que como sempre temos que ter cuidado com as temporarias que vamos criar .

Mas sempre…sempre pensem set based.

Um grande abraço !!!

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Algo que Esqueci de Categorizar. Bookmark the permalink.

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