none
Estrutura do cte dentro while RRS feed

  • Pergunta

  • Pessoal

          Estou com um problema relacionado com a estrutura do cte, onde já fiz várias mudanças na questão da estrutura mas até o momento não obtive sucesso, onde por mais que a minha rotina pudesse ser outra, gostaria de entender neste caso como seria a estrutura para atender o que gostaria de fazer, conforme abaixo.

          Onde na opção número (3) ele não reconhece a referencia da tabela do cte, ai tentei por questão de entender a estrutura colocar na opção (2) onde também não deu certo, já na opção (1) deu certo, então por mais que tenha um rotina melhor para executar o processo na questão de estrutura porque não deu certo e no caso o que eu precisaria fazer para dar certo, pois na opção (1) ai da certo.

          

    CREATE procedure [dbo].[usp_jobs_dw_portaria_del]
    (
    @pp_dtEntrada as date
    )
    as begin
      
         set nocount on
    
    	 declare @qtSelectReg as bigint,@qtSelectSeq as bigint
    	        ,@qtSelectMod as bigint,@qtSelectTop as bigint
    
    	 select @qtSelectReg = 0,@qtSelectSeq =0,@qtSelectMod = 0,@qtSelectTop = 0
    
            ;with cte_tb_portaria as
    	(
    	select 
    	'S' as icDeleteReg
    	,tb_p_seg.*
    	from dw_portaria.dbo.tb_portao_seg tb_p_seg with (nolock)
    	where tb_p_seg.dt_entrada < @pp_dtEntrada
    	)
    	
    	-- (opção 3) delete top(50) from cte_tb_portaria where icDeleteReg = 'S' 
    
    	select @qtSelectReg = count(*) from cte_tb_portaria where icDeleteReg = 'S' 
    
    	select @qtSelectReg = isnull(@qtSelectReg,0)
    	select @qtSelectReg = iif(@qtSelectReg = 0,1,@qtSelectReg)
    	select @qtSelectSeq = @qtSelectReg / 50
    	select @qtSelectMod = iif(@qtSelectReg % 50 > 0,1,0)
    	select @qtSelectTop = @qtSelectSeq + @qtSelectMod
    
    	--select @qtSelectReg as qtGeral,@qtSelectSeq as qtSequencia
    	--      ,@qtSelectMod as qtResto,@qtSelectTop as qtSelectTop
     
            -- (opção 2) delete top(50) from cte_tb_portaria where icDeleteReg = 'S' 
    	
    	while (@qtSelectTop > 0)
    	begin
    	  
    		-- (opção 1) delete top(50) from cte_tb_portaria where icDeleteReg = 'S' 
    
    	    --select concat('hoje ',@qtSelectTop)  as DataHoje
    		set @qtSelectTop = @qtSelectTop - 1
    
    	end
    	
    
    	set nocount off
    
    end
    
    
    GO
    
    
    
    

    • Editado neibala sábado, 20 de julho de 2019 00:08 erro de digitação 2vez
    sexta-feira, 19 de julho de 2019 12:39

Respostas

  • "neibala", o objetivo do procedimento é apagar linhas da tabela dw_portaria.dbo.tb_portao_seg cuja data seja anterior ao valor do parâmetro, em blocos de 50 linhas por vez? 

    Como está declarada a coluna dt_entrada da tabela tb_portao_seg?

    ---

    Se o objetivo é apagar em blocos de 50 linhas, eis sugestão de código:

    -- código #1
    -- Autor: José Diz/BH
    
    CREATE procedure dbo.usp_jobs_dw_portaria_del
         @pp_dtEntrada as date
    as
    begin
    
    set nocount on;
    
    declare @Apagados int;
    set @Apagados= 1;
    
    while @Apagados > 0
      begin
      DELETE top (50) 
        from dw_portaria.dbo.tb_portao_seg
        where dt_entrada < @pp_dtEntrada;
    
      set @Apagados= @@rowcount;
      end;
    
    end;
    go

     @@ROWCOUNT retorna quantas linhas foram apagadas na execução do DELETE. Quando não houver mais linhas a apagar, retorna 0.

    Outras opções no artigo  “Apagar conjunto de linhas em tabelas enormes”.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Marcado como Resposta neibala segunda-feira, 22 de julho de 2019 18:01
    • Editado José Diz quinta-feira, 17 de outubro de 2019 01:43
    sábado, 20 de julho de 2019 12:56
  • Neibala,

    O que você pode fazer, é transformar a sua CTE em uma View e fazer uso dela no Delete.

    Não conheço a volumetria dos seus dados, talvez se você for pensar em utilizar um View, o uso de um índice aplicado a view poderá lhe ajudar no que se refere a melhorias de performance no momento da processamento do Delete.

    Mas nem tudo são flores, por outro lado, quando utilizamos um View Indexada conhecida como View Materializada, o índice vinculado a view pode gerar impactos em processos na tabela a qual o view esta vinculada, pois o índice da view também será atualizado.

    Temos que analisar e validar os seus 3 Vs:

    - Volumetria;

    - Velocidade; e

    - Variedade dos dados.



    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marcado como Resposta neibala segunda-feira, 22 de julho de 2019 18:02
    domingo, 21 de julho de 2019 00:39

Todas as Respostas

  • O objeto tb_dcenter_acordo não faz parte da cláusula FROM; então não pode constar na lista de colunas.

       ;with cte_tb_portaria as   
        (
        select
        'S' as icDeleteReg
        ,tb_dcenter_acordo.*
        from dw_portaria.dbo.tb_portao_seg tb_p_seq with (nolock)
        where tb_p_seq.dt_entrada < @pp_dtEntrada
        )

     


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz terça-feira, 23 de julho de 2019 11:56
    sexta-feira, 19 de julho de 2019 14:32
  • José Diz / Grupo

         Como tive de refazer a query para poder ficar mais didática para a minha explicação e dúvida, acabei informando coisa que não estava presente na query, fiz o ajuste na query novamente e acredito que agora esteja correta.

        E caso possa verificar novamente verificarei muito grato, pois pesquisei e li em vários locais, mais não conseguiu identificar como seria a estrutura correta.

    sábado, 20 de julho de 2019 00:16
  • "neibala", o objetivo do procedimento é apagar linhas da tabela dw_portaria.dbo.tb_portao_seg cuja data seja anterior ao valor do parâmetro, em blocos de 50 linhas por vez? 

    Como está declarada a coluna dt_entrada da tabela tb_portao_seg?

    ---

    Se o objetivo é apagar em blocos de 50 linhas, eis sugestão de código:

    -- código #1
    -- Autor: José Diz/BH
    
    CREATE procedure dbo.usp_jobs_dw_portaria_del
         @pp_dtEntrada as date
    as
    begin
    
    set nocount on;
    
    declare @Apagados int;
    set @Apagados= 1;
    
    while @Apagados > 0
      begin
      DELETE top (50) 
        from dw_portaria.dbo.tb_portao_seg
        where dt_entrada < @pp_dtEntrada;
    
      set @Apagados= @@rowcount;
      end;
    
    end;
    go

     @@ROWCOUNT retorna quantas linhas foram apagadas na execução do DELETE. Quando não houver mais linhas a apagar, retorna 0.

    Outras opções no artigo  “Apagar conjunto de linhas em tabelas enormes”.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Marcado como Resposta neibala segunda-feira, 22 de julho de 2019 18:01
    • Editado José Diz quinta-feira, 17 de outubro de 2019 01:43
    sábado, 20 de julho de 2019 12:56
  • José Diz

         Como poderia dizer, mandou bem mesmo utilizando o @@ROWCOUNT, isto como sempre, só que a minha dúvida maior conforme comentei no início, a estrutura que enviei utilizando cte teria como ela funcionar, porque mais que tivesse  uma outra com maior performance ou recurso, pois dando certo a estrutura que enviei estaria utilizando para outras atividades, devido a sua estrutura, teria como verificar o que precisaria fazer para que a estrutura que enviei funciona-se, tem como ? 

    sábado, 20 de julho de 2019 23:07
  • (..) a estrutura que enviei utilizando cte teria como ela funcionar

    Uma CTE somente pode ser utilizada pelo comando INSERT/UPDATE/DELETE/MERGE imediatamente a seguir à declaração da CTE:

    with CTE as (
    ... código da CTE
    )
    INSERT/UPDATE/DELETE/MERGE referenciando a CTE

    A CTE não pode ser referenciada em outros trechos do código. Por isso não é possível utilizar a CTE cte_tb_portaria no bloco de código

       while (@qtSelectTop > 0)
           begin
           delete top(50) from cte_tb_portaria where icDeleteReg = 'S'
           set @qtSelectTop = @qtSelectTop - 1
          end

    ---

    Há uma frase atribuída a Leonardo da Vinci que diz o seguinte: "Simplicidade é o último grau de sofisticação". Para este seu caso específico, - apagar um conjunto de linhas, em blocos -, me parece que usar CTE é tornar o processo complicado, de manutenção mais complexa e provavelmente mais lento.

      


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quinta-feira, 12 de setembro de 2019 22:39
    sábado, 20 de julho de 2019 23:25
  • Neibala,

    O que você pode fazer, é transformar a sua CTE em uma View e fazer uso dela no Delete.

    Não conheço a volumetria dos seus dados, talvez se você for pensar em utilizar um View, o uso de um índice aplicado a view poderá lhe ajudar no que se refere a melhorias de performance no momento da processamento do Delete.

    Mas nem tudo são flores, por outro lado, quando utilizamos um View Indexada conhecida como View Materializada, o índice vinculado a view pode gerar impactos em processos na tabela a qual o view esta vinculada, pois o índice da view também será atualizado.

    Temos que analisar e validar os seus 3 Vs:

    - Volumetria;

    - Velocidade; e

    - Variedade dos dados.



    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marcado como Resposta neibala segunda-feira, 22 de julho de 2019 18:02
    domingo, 21 de julho de 2019 00:39
  •     José Diz / Junior / Grupo

           Valeu pelas dicas.

    segunda-feira, 22 de julho de 2019 17:54
  •     José Diz / Junior / Grupo

           Valeu pelas dicas.

    Neibala,

    Nós que agradecemos.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 23 de julho de 2019 09:31
  • José Diz

         Como poderia dizer, mandou bem mesmo utilizando o @@ROWCOUNT, isto como sempre,

    "neibala", a estratégia de apagar linhas em blocos é benvinda, pois reduz a contenção que pode ocorrer no banco de dados, evitando travar os demais processos. No caso tratado neste tópico a forma sugerida foi:

    ...
    DELETE top (n)
        from dw_portaria.dbo.tb_portao_seg
        where dt_entrada < @pp_dtEntrada;
    ...


    Mas tenho uma dúvida: existe índice na tabela tb_portao_seg pela coluna dt_entrada?

    ---

    O apagamento em blocos de linhas pode ser eficiente se houver índice na tabela tb_portao_seg pela coluna dt_entrada. Mas se não houver índice, pode ser que esteja a ocorrer leitura sequencial da tabela, à procura de n linhas para apagar. A análise das estatísticas de IO bem como do plano de execução são úteis para detectar se o código está ou não eficiente, considerando-se as características dos dados.

    Eis outro código de apagamento de blocos de linhas, indicado somente para os casos em que não há índice pela coluna que é filtro na cláusula WHERE.

    -- código #2 v3
    -- Autor: José Diz/BH

    CREATE PROCEDURE dbo.usp_jobs_dw_portaria_del
         @pp_dtEntrada as date,
         @QtdDel smallint = 500
    as
    begin

    set nocount on;

    -- cria lista com ID das linhas que serão apagadas
    IF Object_ID ('tempDB..#Lista_ID', 'U') is not null
      DROP TABLE #Lista_ID;
    CREATE TABLE #Lista_ID (seq smallint identity primary key, ID_Del int not null);

    INSERT into #Lista_ID (ID_Del)
      SELECT ID
        from dbo.tb_portao_seg
        where dt_entrada < @pp_dtEntrada;
        
    -- apagamento de blocos
    declare @InicioBloco smallint, @FimBloco smallint, @Lido tinyint;
    set @InicioBloco= 1;
    set @FimBloco= @QtdDel;

    set @Lido= 1;
    while @Lido > 0
      begin
      DELETE
        from dbo.tb_portao_seg
        where ID in (SELECT L.ID_Del
                       from #Lista_ID as L
                       where L.seq between @InicioBloco and @FimBloco);
     
      set @Lido= @@rowcount;
      set @InicioBloco+= @QtdDel;
      set @FimBloco+= @QtdDel;
      end;
            
    end;
    go

    Substitua ID pelo nome da coluna que é a chave primária da tabela dbo.tb_portao_seg.

    A coluna ID_Del da tabela #Lista_ID deve ser declarada da mesma forma que a chave primária da tabela  dbo.tb_portao_seg.

     

    Outras opções no artigo  “Apagar conjunto de linhas em tabelas enormes”.

     


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quinta-feira, 17 de outubro de 2019 16:21
    terça-feira, 23 de julho de 2019 11:52