none
Técnica para "substituição" de Cursores RRS feed

  • Pergunta

  • Caros, experts; bom dia!

    Em algumas buscas em diversos fóruns, há uma grande pedra no sapato dos desenvolvedores que são os CURSORES.

    A minha pergunta é: Existe alguma técnica, troubleshooting, ou algo do gênero para a substituição de Cursores.

    Pergunto isso, pois em algumas consultorias que a minha empresa contratou, a primeira coisa que os especialistas em Tunning fizeram foram uma "Caça aos Cursores"; onde o desempenho aumentou absurdos 80%; e talvez terei que aprender isso para que possamos internalizar esse processo; bem como aplicar em nossos clientes.

    Um exemplo bem claro de cursores que temos:

    --Atualizar Centro de Custo na conta de despesa
    
    DECLARE custo_cursor CURSOR FOR
    
    SELECT 
    T0.LINENUM, 
    T0.OCRCODE, 
    T1.EXPENSESAC FROM RPC1 T0 INNER JOIN OITW T1 
    ON T0.ITEMCODE = T1.ITEMCODE AND T0.WHSCODE = T1.WHSCODE
    WHERE T0.DOCENTRY = @list_of_cols_val_tab_del 
    
     OPEN custo_cursor
      FETCH NEXT FROM custo_cursor
       INTO @LN, @CentroCusto, @ContaDespesa
    
       WHILE @@FETCH_STATUS = 0
        BEGIN
         IF(@ContaDespesa is not NULL or @ContaDespesa <> '')
          BEGIN
           IF(@CentroCusto is NULL OR @CentroCusto = '')
            BEGIN
              SELECT @error = 1
              SELECT @error_message = 'Preencher Centro de Custo para todas as linhas'
            END
            
     --Atualizar a linha do lançamento contábil com o centro de custo
            UPDATE JDT1 SET ProfitCode = @CentroCusto WHERE Transtype = '19' AND CreatedBy = @list_of_cols_val_tab_del and account = @ContaDespesa
          END  
          FETCH NEXT FROM custo_cursor
          INTO @LN, @CentroCusto, @ContaDespesa
        END
        CLOSE custo_cursor
        DEALLOCATE custo_cursor
      END
    END
    
    

    Em uma lista de itens pequena (até 15 linhas) o desempenho é razoável, agora nesse atual cenário estamos com uma demanda de mais de 7000 linhas isso fica praticamente inviável (inclusive deixamos rodando ontem a noite, e espero que segunda de manhã esteja pronto).

    Como seria uma técnica de substituir esse cursor?

    Desde já obrigado!

    Abs[]'s

    sábado, 23 de julho de 2011 13:43

Respostas

  • A Microsoft chama esse tipo de abordagem, de CURSOR BASED, que, como você mesmo citou, deve ser evitada no SQL Server. A Microsoft recomenda que seja usada a abordagem SET BASED. O que é isso? Nada mais do que evitar os cursores e utilizar os comandos que utilizam um conjunto de resultados, como o update ou o delete.

    Talvez citando um exemplo seja mais fácil de você enteder. Vamos imaginar que você precisa atualizar 5000 registros que tiveram os preços aumentados em 10%. Usando um cursor, você iria recuperar os 5000 registros, fazer um loop por cada um dos registros e um update em cada um dos registros. Usando um comando SET BASED, bastaria que você fizesse o update com a cláusula WHERE adequada que recuperasse os 5000 registros e fizesse a alteração de uma vez só. E porque a performance é melhor? Porque no caso do cursor, você irá fazer 5000 updates, gerando 5000 (no mínimo) bloqueios na sua tabela, e esses bloqueios inevitavelmente iriam impactar na sua performance. No uso de um único update, você só irá fazer 1 bloqueio (que seria escolhido pelo SQL levando-se em consideração o melhor bloqueio para aquele comando).

    Bem, porque eu falei isso tudo pra você? Porque efetivamente eu não conheço uma técnica específica para o assunto, não existe um "conversor" de cursor para set based. Você terá que analisar caso a caso e ir modificando, sempre pensando em termos gerais em como substituir o cursor por um (ou alguns) updates ao invés do cursor.

    Espero ter ajudado.


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008
    sábado, 23 de julho de 2011 14:31
    Moderador
  • Bom Dia,

    Há algumas formas de visualizar um ResultSet oriundo de um SELECT.

    • Alguns irão ver esse ResultSet como uma lista de linhas e colunas
    • Outros irão ver o ResultSet como um conjunto composto de várias linhas individuais onde cada linha é o item de maior importância
    • Outros irão ver o ResultSet apenas como um conjunto onde todas as suas linhas tem características em comum e por isso foram o conjunto

    Aqueles que fazem consultas tendo a primeira visão são mais familiarizados com planilhas e normalmente estão dando os primeiros passos no banco de dados e descobrindo o que a SQL pode fazer. A linguagem SQL não é orientada a trabalhar com células como fazem as planilhas eletrônicas e outras linguagens (MDX).

    Aqueles que fazem consultas tendo a segunda visão normalmente são programadores acostumados com listas, varreduras (ForEach) e Loops. Para que um resultado final seja obtido é preciso trabalhar cada item individualmente. Esses são a grande maioria dos desenvolvedores e tem como premissa a filosofia RowBased, ou seja, programa-se orientada a cada linha individualmente

    Aqueles que fazem consultas tendo a terceira visão são os que realmente entendem o próposito da SQL em lidar com um conjunto que possui propriedades e deve ser trabalhado como tal e não suas linhas de forma individual. É mais fácil dizer "Retorne os clientes que ganham mais de 10.000 reais" do que dizer "Avalie cada cliente e caso ele tenha mais de 10.000 reais retorne-o". A primeira visão é SET Based enquanto que a segunda visão é Row Based. Ambas trazem os mesmos resultados, mas sobre uma forma de pensar diferente e na quase que totalidade das vezes, a visão SET Based é muito mais eficiente. Como as linguagens de programação são procedurais e não declarativas, é comum que os desenvolvedores tenham uma preferência Row Based (infelizmente).

    Agora vamos à dúvida:

    - Há um conjunto de resultados (SELECT)
    - Esse conjunto será avaliado com base em três critérios
      Um filtro T0.DOCENTRY = @list_of_cols_val_tab_del 
      (@ContaDespesa is not NULL or @ContaDespesa <> '')
      (@CentroCusto is NULL OR @CentroCusto = '')

    - Um critério só é avaliado se passar no primeiro critério
      Os registros avaliado pelo segundo critério são um subconjunto do primeiro
      Os registros avaliados pelo terceiro critério são um subconjunto do segundo

    -- Temos aqui dois grupos de interesse
       Os registros que passaram no primeiro e no segundo critério, e terceiro não obedecem à regra
       Os registros que passaram no primeiro e no segundo critério, e terceiro e obedecem à regra
       Nesse caso é preciso além de atualizar mostrar um RESULT SET com os erros
     
    -- Com base nisso é mais fácil e performático reescrever dois UPDATEs (um para cada situação) do que submeter todos os registro para reavaliação de todos os critérios

    declare @list_of_cols_val_tab_del int

    -- Monta uma CTE (Registros do Primeiro Critério)
    ;WITH Res As (
     SELECT
     T0.LINENUM As LN,
     T0.OCRCODE As CentroCusto,
     T1.EXPENSESAC As ContaDespesa
     FROM RPC1 T0 INNER JOIN OITW T1
     ON T0.ITEMCODE = T1.ITEMCODE AND T0.WHSCODE = T1.WHSCODE
     WHERE T0.DOCENTRY = @list_of_cols_val_tab_del)

    -- Faz a atualização (Update 1) combinando com a CTE
    UPDATE JDT1 SET ProfitCode = RES.CentroCusto
    FROM JDT1
    INNER JOIN Res ON JDT1.account = Res.ContaDespesa
    WHERE JDT1.Transtype = '19' AND JDT1.CreatedBy = @list_of_cols_val_tab_del
     -- Força o segundo critério
     AND (Res.ContaDespesa IS NOT NULL OR Res.ContaDespesa <> '')
     -- Força o terceiro critério (não obedece NOT)
        AND NOT (Res.CentroCusto IS NULL OR Res.CentroCusto = '')

    -- Declare uma variável do tipo Tabela
    DECLARE @Res TABLE (
     ContaDespesa VARCHAR(50),
     CreatedBy VARCHAR(100),
     Erro INT DEFAULT 1,
     Mensagem VARCHAR(100) DEFAULT 'Preencher Centro de Custo para todas as linhas')

    -- Monta uma CTE (Registros do Primeiro Critério)
    ;WITH Res As (
     SELECT
     T0.LINENUM As LN,
     T0.OCRCODE As CentroCusto,
     T1.EXPENSESAC As ContaDespesa
     FROM RPC1 T0 INNER JOIN OITW T1
     ON T0.ITEMCODE = T1.ITEMCODE AND T0.WHSCODE = T1.WHSCODE
     WHERE T0.DOCENTRY = @list_of_cols_val_tab_del)

    -- Faz a atualização (Update 1) combinando com a CTE
    UPDATE JDT1 SET ProfitCode = RES.CentroCusto
    -- Força a saída para a variável @Res
    OUTPUT INSERTED.ContaDespesa, INSERTED.CreatedBy INTO @Res (ContaDespesa, CreateBy)
    FROM JDT1
    INNER JOIN Res ON JDT1.account = Res.ContaDespesa
    WHERE JDT1.Transtype = '19' AND JDT1.CreatedBy = @list_of_cols_val_tab_del
     -- Força o segundo critério
     AND (Res.ContaDespesa IS NOT NULL OR Res.ContaDespesa <> '')
     -- Força o terceiro critério (obedece)
        AND (Res.CentroCusto IS NULL OR Res.CentroCusto = '')

    -- Mostra o resultado de @Res
    SELECT ContaDespesa, CreateBy, Erro, Mensagem FROM @Res

    Não possuo os dados e nem as tabelas para testar, mas o caminho é esse. Como você disse com 15 linhas o desempenho é razoável, mas aumente um pouco essas linhas para ver como a solução SET Based terá um desempenho muito mais surpreendente. É mais fácil dar uma mensagem para mil pessoas uma única vez do que repetir a mesma mensagem para cada uma das mil pessoas. Juntar as mil pessoas dará mais trabalho que achar uma pessoa individualmente, mas o esforço será feito uma única vez. É a diferença entre pensar Set Based e Row Based.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com

     


    Classifique as respostas. O seu feedback é imprescindível
    sábado, 23 de julho de 2011 14:46

Todas as Respostas

  • A Microsoft chama esse tipo de abordagem, de CURSOR BASED, que, como você mesmo citou, deve ser evitada no SQL Server. A Microsoft recomenda que seja usada a abordagem SET BASED. O que é isso? Nada mais do que evitar os cursores e utilizar os comandos que utilizam um conjunto de resultados, como o update ou o delete.

    Talvez citando um exemplo seja mais fácil de você enteder. Vamos imaginar que você precisa atualizar 5000 registros que tiveram os preços aumentados em 10%. Usando um cursor, você iria recuperar os 5000 registros, fazer um loop por cada um dos registros e um update em cada um dos registros. Usando um comando SET BASED, bastaria que você fizesse o update com a cláusula WHERE adequada que recuperasse os 5000 registros e fizesse a alteração de uma vez só. E porque a performance é melhor? Porque no caso do cursor, você irá fazer 5000 updates, gerando 5000 (no mínimo) bloqueios na sua tabela, e esses bloqueios inevitavelmente iriam impactar na sua performance. No uso de um único update, você só irá fazer 1 bloqueio (que seria escolhido pelo SQL levando-se em consideração o melhor bloqueio para aquele comando).

    Bem, porque eu falei isso tudo pra você? Porque efetivamente eu não conheço uma técnica específica para o assunto, não existe um "conversor" de cursor para set based. Você terá que analisar caso a caso e ir modificando, sempre pensando em termos gerais em como substituir o cursor por um (ou alguns) updates ao invés do cursor.

    Espero ter ajudado.


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008
    sábado, 23 de julho de 2011 14:31
    Moderador
  • Bom Dia,

    Há algumas formas de visualizar um ResultSet oriundo de um SELECT.

    • Alguns irão ver esse ResultSet como uma lista de linhas e colunas
    • Outros irão ver o ResultSet como um conjunto composto de várias linhas individuais onde cada linha é o item de maior importância
    • Outros irão ver o ResultSet apenas como um conjunto onde todas as suas linhas tem características em comum e por isso foram o conjunto

    Aqueles que fazem consultas tendo a primeira visão são mais familiarizados com planilhas e normalmente estão dando os primeiros passos no banco de dados e descobrindo o que a SQL pode fazer. A linguagem SQL não é orientada a trabalhar com células como fazem as planilhas eletrônicas e outras linguagens (MDX).

    Aqueles que fazem consultas tendo a segunda visão normalmente são programadores acostumados com listas, varreduras (ForEach) e Loops. Para que um resultado final seja obtido é preciso trabalhar cada item individualmente. Esses são a grande maioria dos desenvolvedores e tem como premissa a filosofia RowBased, ou seja, programa-se orientada a cada linha individualmente

    Aqueles que fazem consultas tendo a terceira visão são os que realmente entendem o próposito da SQL em lidar com um conjunto que possui propriedades e deve ser trabalhado como tal e não suas linhas de forma individual. É mais fácil dizer "Retorne os clientes que ganham mais de 10.000 reais" do que dizer "Avalie cada cliente e caso ele tenha mais de 10.000 reais retorne-o". A primeira visão é SET Based enquanto que a segunda visão é Row Based. Ambas trazem os mesmos resultados, mas sobre uma forma de pensar diferente e na quase que totalidade das vezes, a visão SET Based é muito mais eficiente. Como as linguagens de programação são procedurais e não declarativas, é comum que os desenvolvedores tenham uma preferência Row Based (infelizmente).

    Agora vamos à dúvida:

    - Há um conjunto de resultados (SELECT)
    - Esse conjunto será avaliado com base em três critérios
      Um filtro T0.DOCENTRY = @list_of_cols_val_tab_del 
      (@ContaDespesa is not NULL or @ContaDespesa <> '')
      (@CentroCusto is NULL OR @CentroCusto = '')

    - Um critério só é avaliado se passar no primeiro critério
      Os registros avaliado pelo segundo critério são um subconjunto do primeiro
      Os registros avaliados pelo terceiro critério são um subconjunto do segundo

    -- Temos aqui dois grupos de interesse
       Os registros que passaram no primeiro e no segundo critério, e terceiro não obedecem à regra
       Os registros que passaram no primeiro e no segundo critério, e terceiro e obedecem à regra
       Nesse caso é preciso além de atualizar mostrar um RESULT SET com os erros
     
    -- Com base nisso é mais fácil e performático reescrever dois UPDATEs (um para cada situação) do que submeter todos os registro para reavaliação de todos os critérios

    declare @list_of_cols_val_tab_del int

    -- Monta uma CTE (Registros do Primeiro Critério)
    ;WITH Res As (
     SELECT
     T0.LINENUM As LN,
     T0.OCRCODE As CentroCusto,
     T1.EXPENSESAC As ContaDespesa
     FROM RPC1 T0 INNER JOIN OITW T1
     ON T0.ITEMCODE = T1.ITEMCODE AND T0.WHSCODE = T1.WHSCODE
     WHERE T0.DOCENTRY = @list_of_cols_val_tab_del)

    -- Faz a atualização (Update 1) combinando com a CTE
    UPDATE JDT1 SET ProfitCode = RES.CentroCusto
    FROM JDT1
    INNER JOIN Res ON JDT1.account = Res.ContaDespesa
    WHERE JDT1.Transtype = '19' AND JDT1.CreatedBy = @list_of_cols_val_tab_del
     -- Força o segundo critério
     AND (Res.ContaDespesa IS NOT NULL OR Res.ContaDespesa <> '')
     -- Força o terceiro critério (não obedece NOT)
        AND NOT (Res.CentroCusto IS NULL OR Res.CentroCusto = '')

    -- Declare uma variável do tipo Tabela
    DECLARE @Res TABLE (
     ContaDespesa VARCHAR(50),
     CreatedBy VARCHAR(100),
     Erro INT DEFAULT 1,
     Mensagem VARCHAR(100) DEFAULT 'Preencher Centro de Custo para todas as linhas')

    -- Monta uma CTE (Registros do Primeiro Critério)
    ;WITH Res As (
     SELECT
     T0.LINENUM As LN,
     T0.OCRCODE As CentroCusto,
     T1.EXPENSESAC As ContaDespesa
     FROM RPC1 T0 INNER JOIN OITW T1
     ON T0.ITEMCODE = T1.ITEMCODE AND T0.WHSCODE = T1.WHSCODE
     WHERE T0.DOCENTRY = @list_of_cols_val_tab_del)

    -- Faz a atualização (Update 1) combinando com a CTE
    UPDATE JDT1 SET ProfitCode = RES.CentroCusto
    -- Força a saída para a variável @Res
    OUTPUT INSERTED.ContaDespesa, INSERTED.CreatedBy INTO @Res (ContaDespesa, CreateBy)
    FROM JDT1
    INNER JOIN Res ON JDT1.account = Res.ContaDespesa
    WHERE JDT1.Transtype = '19' AND JDT1.CreatedBy = @list_of_cols_val_tab_del
     -- Força o segundo critério
     AND (Res.ContaDespesa IS NOT NULL OR Res.ContaDespesa <> '')
     -- Força o terceiro critério (obedece)
        AND (Res.CentroCusto IS NULL OR Res.CentroCusto = '')

    -- Mostra o resultado de @Res
    SELECT ContaDespesa, CreateBy, Erro, Mensagem FROM @Res

    Não possuo os dados e nem as tabelas para testar, mas o caminho é esse. Como você disse com 15 linhas o desempenho é razoável, mas aumente um pouco essas linhas para ver como a solução SET Based terá um desempenho muito mais surpreendente. É mais fácil dar uma mensagem para mil pessoas uma única vez do que repetir a mesma mensagem para cada uma das mil pessoas. Juntar as mil pessoas dará mais trabalho que achar uma pessoa individualmente, mas o esforço será feito uma única vez. É a diferença entre pensar Set Based e Row Based.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com

     


    Classifique as respostas. O seu feedback é imprescindível
    sábado, 23 de julho de 2011 14:46
  • Olá Roberto!

    Obrigado pela resposta, eu sinceramente não conhecia essa abordagem SET BASED; e vou tentar pensar em uma lógica para eliminar esses cursores.

    Abs []'s

    sábado, 23 de julho de 2011 15:26
  • Gustavo, bom dia!

    Bastante interessante esses 3 paradigmas apresentados do ROW, SET e CONJUNTO.

    Consegui realizar essa adaptação, e vou subir uma base de testes para aplicar. Mas o mais importante sem dúvidas foram as técnicas apresentadas para a substituição do CURSOR como:

    1 - Analisar conjunto de resultados (SELECT);

    2 - Critérios de Modificação (FILTROS);

    3 - Dados obtidos, inclusos ou não inclusos no fitro e o devido tratamento (RESULT SET)

    4 - Montagem de CTE para executar os filtros das regras mencionadas (UPDATE).

    Obrigado pela resposta, e penso que vai ajudar bastante outros Developers que buscam esse tipo de informação.  

    Abs[]'s

    sábado, 23 de julho de 2011 15:37
  • Bom Dia Iniciante,

    Essa é uma das técnicas que utilizo para "traduzir para SET Based". Nunca vi escrita em lugar nenhum, mas costumo utilizar bastante justamente em "tuning" e consultorias. Há alguns livros que ensinam a pensar SET Based como alguns do Itzik e do Joe Celko.

    Não conhecia os links que você enviou. São interessantes...

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    domingo, 24 de julho de 2011 13:30