Carregando ...
Desculpe, ocorreu um erro ao carregar o conteúdo.
 

Melhores práticas - melhorando o script

Expandir mensagens
  • Vitor Bueno
    Olá Pessoal, gostaria da opinião de vocês sobre uma maneira mais elegante de se fazer duas operações no SQL Server, sempre visando a questão do
    Mensagem 1 de 6 , 5 de dez de 2016
      Olá Pessoal, 

      gostaria da opinião de vocês sobre uma maneira mais elegante de se fazer duas operações no SQL Server, sempre visando a questão do desempenho e também de manutenção futura:  



      1. validação de INSERT em tabela com chave composta :

      o seguinte insert em uma tabela de detalhe na qual a chave é composta de 3 colunas:


      TProposta (CodProposta, Cliente, ...)
      TPropostaDT (CodProposta, CodProduto, CodLocal, ...)

      A operação em questão é para a movimentação de itens de dentro de uma proposta para outra  (usada para criar cenários de vendas), de maneira a permitir alterações posteriores na confecção de orçamento, porém, ao inserir itens dentro da proposta destino, este não pode existir dentro da mesma. A solução abaixo está em operação, mas me parece uma enorme engenharia alternativa. 


      Estive analisando algo referente a trocar esta validação para o uso do WHERE NOT EXISTS mas não cheguei num consenso.





      2. Registro de Log:

      Esta operação foi concebida desta forma devido a alguns problemas com triggers no passado e foi ficando, ficando e está assim até hoje (é do tempo do SQL 6.5). Que opção mais interessante pode ser usada para registros de logs destas operações? Como vocês fazem isto atualmente?




      Segue abaixo a rotina em questão, negritado as partes referenciadas nos itens acima.

      Versão atual do banco: Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

      Grato pela atenção.


      Att











      CREATE PROCEDURE spPropostaCenario 
      @CodPropostaOrigem int,
      @CodPropostaDestino int,
      @ID int,
      @Retorno varchar(500) output
      AS
      BEGIN

              DECLARE @QtdeColunas int
              SET QtdeColunas = 0

      INSERT INTO TPropostaDT (
      CodProposta, 
      CodProduto, 
      CodLocal, 
      QtdeIncluso, 
      Renovar, 
      VrBase, 
      VrIncluso, 
      VrExtra, 
      QtdeParcelas, 
      DescricaoProduto, 
      Precificador, 
      ID, 
      GrupoConsumo, 
      VrPrecificador, 
      InstrucaoOperacional
      SELECT 
      @CodPropostaDestino, 
      CodProduto, 
      CodLocal, 
      QtdeIncluso, 
      Renovar, 
      VrBase, 
      VrIncluso, 
      VrExtra, 
      QtdeParcelas, 
      DescricaoProduto, 
      Precificador, 
      @ID, 
      GrupoConsumo, 
      VrPrecificador, 
      InstrucaoOperacional
      FROM TPropostaDT 
      WHERE 
      CodProposta = @CodPropostaOrigem
      AND CONVERT(varchar, CodProduto) + '|' + convert(varchar, CodLocal) NOT IN (
      SELECT 
      CONVERT(varchar, CodProduto) + '|' + convert(varchar, CodLocal)
      FROM TPropostaDT
      WHERE 
      CodProposta = @CodPropostaDestino
      )

              SET QtdeColunas = @@ROWCOUNT


      -- Registro de Log
      INSRT INTO TLog (ID, Modulo, Obs)
      VALUES (
      @ID,
      'PropostaCenario',
      convert(varchar, ISNULL(@CodPropostaOrigem,0) )         
      +'|'+ convert(varchar, ISNULL(@CodPropostaDestino,0) )
      +'|'+ convert(varchar, ISNULL(@QtdeColunas,0) )
      )

      SET @Retorno = '[' + convert(varchar, @QtdeColunas) + '] Operação realizada com sucesso.'

      END
      GO



    • Andre Luiz Reis Marques
      Vitor, Bom dia. Entendi que voce quer inserir dados na tabela TPropostaDT (CodProposta, CodProduto, CodLocal, ...) que possui essa chave composta. voce pode
      Mensagem 2 de 6 , 6 de dez de 2016
        Vitor,

        Bom dia.

        Entendi que voce quer inserir dados na tabela TPropostaDT (CodProposta, CodProduto, CodLocal, ...) que possui essa chave composta.

        voce pode fazer da seguinte forma:

        insert into TPropostaDT ((CodProposta, CodProduto, CodLocal, ...)
          select coluna1,coluna2,coluna3.......
             from TPropostaDT dt
               where CodProposta = @CodPropostaOrigem
               and not exists (select * from TPropostaDT dt2
                                      where
                                             dt2.CodProposta = dt.CodProposta
                                      and dt2.CodProduto   = dt.CopProduto
                                      and dt2.CodLocal,      = dt.CodLocal
                                      and dt2.CodProposta = @CodPropostaDestino)

          Esse script garante que somente serão incluidos itens que nao existem na tabela proposta
          retire o Convert, acredito que as colunas ja sao do mesmo tipo de dado.

        Caso tenha entendido errado favor retornar o e-mail.


        Atenciosamente,
        André Luiz R. Marques
        Administrador de Banco de Dados - SQL Server/Oracle
        Tel: (21) 99978-4564
         
        Evite imprimir. Colabore com o Meio Ambiente!
         
        "Embora ninguém possa voltar atrás e fazer um novo começo, qualquer um pode
        começar agora e fazer um novo fim."
                       Chico Xavier



        Em Segunda-feira, 5 de Dezembro de 2016 17:58, "Vitor Bueno vycthor@... [mssql-l]" <mssql-l@...> escreveu:


         
        Olá Pessoal, 

        gostaria da opinião de vocês sobre uma maneira mais elegante de se fazer duas operações no SQL Server, sempre visando a questão do desempenho e também de manutenção futura:  



        1. validação de INSERT em tabela com chave composta :

        o seguinte insert em uma tabela de detalhe na qual a chave é composta de 3 colunas:


        TProposta (CodProposta, Cliente, ...)
        TPropostaDT (CodProposta, CodProduto, CodLocal, ...)

        A operação em questão é para a movimentação de itens de dentro de uma proposta para outra  (usada para criar cenários de vendas), de maneira a permitir alterações posteriores na confecção de orçamento, porém, ao inserir itens dentro da proposta destino, este não pode existir dentro da mesma. A solução abaixo está em operação, mas me parece uma enorme engenharia alternativa. 


        Estive analisando algo referente a trocar esta validação para o uso do WHERE NOT EXISTS mas não cheguei num consenso.





        2. Registro de Log:

        Esta operação foi concebida desta forma devido a alguns problemas com triggers no passado e foi ficando, ficando e está assim até hoje (é do tempo do SQL 6.5). Que opção mais interessante pode ser usada para registros de logs destas operações? Como vocês fazem isto atualmente?




        Segue abaixo a rotina em questão, negritado as partes referenciadas nos itens acima.

        Versão atual do banco: Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

        Grato pela atenção.


        Att











        CREATE PROCEDURE spPropostaCenario 
        @CodPropostaOrigem int,
        @CodPropostaDestino int,
        @ID int,
        @Retorno varchar(500) output
        AS
        BEGIN

                DECLARE @QtdeColunas int
                SET QtdeColunas = 0

        INSERT INTO TPropostaDT (
        CodProposta, 
        CodProduto, 
        CodLocal, 
        QtdeIncluso, 
        Renovar, 
        VrBase, 
        VrIncluso, 
        VrExtra, 
        QtdeParcelas, 
        DescricaoProduto, 
        Precificador, 
        ID, 
        GrupoConsumo, 
        VrPrecificador, 
        InstrucaoOperacional
        SELECT 
        @CodPropostaDestino, 
        CodProduto, 
        CodLocal, 
        QtdeIncluso, 
        Renovar, 
        VrBase, 
        VrIncluso, 
        VrExtra, 
        QtdeParcelas, 
        DescricaoProduto, 
        Precificador, 
        @ID, 
        GrupoConsumo, 
        VrPrecificador, 
        InstrucaoOperacional
        FROM TPropostaDT 
        WHERE 
        CodProposta = @CodPropostaOrigem
        AND CONVERT(varchar, CodProduto) + '|' + convert(varchar, CodLocal) NOT IN (
        SELECT 
        CONVERT(varchar, CodProduto) + '|' + convert(varchar, CodLocal)
        FROM TPropostaDT
        WHERE 
        CodProposta = @CodPropostaDestino
        )

                SET QtdeColunas = @@ROWCOUNT


        -- Registro de Log
        INSRT INTO TLog (ID, Modulo, Obs)
        VALUES (
        @ID,
        'PropostaCenario',
        convert(varchar, ISNULL(@CodPropostaOrigem,0) )         
        +'|'+ convert(varchar, ISNULL(@CodPropostaDestino,0) )
        +'|'+ convert(varchar, ISNULL(@QtdeColunas,0) )
        )

        SET @Retorno = '[' + convert(varchar, @QtdeColunas) + '] Operação realizada com sucesso.'

        END
        GO





      • Wolney Marconi Maia
        Olá Vitor, bom dia. Pelo que percebi, as colunas CodProduto e CodLocal são de uma tipo que não é varchar. Por isso o CONVERT na clausula WHERE. Mas do
        Mensagem 3 de 6 , 6 de dez de 2016

          Olá Vitor, bom dia.

           

          Pelo que percebi, as colunas CodProduto e CodLocal são de uma tipo que não é varchar. Por isso o CONVERT na clausula WHERE.

          Mas do jeito que foi colocado, você poderá ter problemas.

          Falta definir o tamanho do varchar no CONVERTE. Boa pratica.

           

          Outro ponto é colocando o CONVERT na clausula WHERE do jeito que você fez, a consulta vai fazer SCAN TABLE/INDEX. Mesmo que você tenha um índice com essas colunas, o fato de incluir uma função com as colunas de pesquisa, faz com que o SQL Server faça um SCAN para pesquisar. Solução : Tire o CONVERT.

           

          Uma solução :

           

          use TempDB

          go

          drop table TPropostaDT 

          go

          create table TPropostaDT  (CodProposta int , CodProduto int , CodLocal int  )

          go

          /*

          Proposta 1 terá os produtos 1,2 e 3

          Proposta 2 terá os produtos 3,4 e 5

          Ambas para o mesmo local (1)

           

          Quando executar a procedure, a proposta 2 terá os produtos 1,2,3 e mais os produtos 4 e 5

           

          */

           

          insert into TPropostaDT (CodProposta , CodProduto , CodLocal )

          values (1,1,),(1,2,1),(1,3,1)

           

          insert into TPropostaDT (CodProposta , CodProduto , CodLocal )

          values (2,3,1),(2,4,1),(2,5,1)

           

          insert into TPropostaDT (CodProposta , CodProduto , CodLocal  )

          values (3,3,1),(3,4,1),(3,5,1)

           

          select * from TPropostaDT

          where CodProposta = 1

           

           

           

          Declare @CodPropostaOrigem int = 2

          Declare @CodPropostaDestino int = 1

           

          ;

          with

             cteOrigem as (

                select * from TPropostaDT

                where CodProposta = @CodPropostaOrigem

             ),

             cteDestino as (

                select * from TPropostaDT

                where CodProposta = @CodPropostaDestino

          )     

          INSERT INTO TPropostaDT (

                 CodProposta ,

                 CodProduto  ,

                 CodLocal

                 )

          SELECT

                 @CodPropostaDestino ,

                 Origem.CodProduto  ,

                 Origem.CodLocal 

                 FROM cteOrigem as Origem

                 left JOIN cteDestino  Destino

                   on Origem.CodProduto = Destino.CodProduto

                  and Origem.CodLocal   = Origem.CodLocal

                 where Destino.CodProposta is null

           

           

           

           

           

          Wolney Marconi Maia
          http://imagem.imprensaoficial.com.br/mailing/email_spacer.pngRede e Infraestrutura - TI
          http://imagem.imprensaoficial.com.br/mailing/email_spacer.png11-2799-9892

          Só imprima se realmente for necessário, pense em sua responsabilidade com o meio ambiente.
O conteúdo desse e-mail ou de seus anexos é confidencial e restrito ao destinatário da mensagem.
Se, por engano, você recebeu esse e-mail, por favor, notifique o remetente, não faça cópias nem distribua seu conteúdo.

           

           

          De: mssql-l@... [mailto:mssql-l@...]
          Enviada em: segunda-feira, 5 de dezembro de 2016 17:58
          Para: mssql-l@...
          Assunto: [mssql-l] Melhores práticas - melhorando o script

           

           

          Olá Pessoal, 

           

          gostaria da opinião de vocês sobre uma maneira mais elegante de se fazer duas operações no SQL Server, sempre visando a questão do desempenho e também de manutenção futura:  

           

           

           

          1. validação de INSERT em tabela com chave composta :

           

          o seguinte insert em uma tabela de detalhe na qual a chave é composta de 3 colunas:

           

           

          TProposta (CodProposta, Cliente, ...)

          TPropostaDT (CodProposta, CodProduto, CodLocal, ...)

           

          A operação em questão é para a movimentação de itens de dentro de uma proposta para outra  (usada para criar cenários de vendas), de maneira a permitir alterações posteriores na confecção de orçamento, porém, ao inserir itens dentro da proposta destino, este não pode existir dentro da mesma. A solução abaixo está em operação, mas me parece uma enorme engenharia alternativa. 

           

           

          Estive analisando algo referente a trocar esta validação para o uso do WHERE NOT EXISTS mas não cheguei num consenso.

           

           

           

           

           

          2. Registro de Log:

           

          Esta operação foi concebida desta forma devido a alguns problemas com triggers no passado e foi ficando, ficando e está assim até hoje (é do tempo do SQL 6.5). Que opção mais interessante pode ser usada para registros de logs destas operações? Como vocês fazem isto atualmente?

           

           

           

           

          Segue abaixo a rotina em questão, negritado as partes referenciadas nos itens acima.

           

          Versão atual do banco: Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

           

          Grato pela atenção.

           

           

          Att

           

           

           

           

           

           

           

           

           

           

           

          CREATE PROCEDURE spPropostaCenario 

                      @CodPropostaOrigem int,

                      @CodPropostaDestino int,

                      @ID int,

                      @Retorno varchar(500) output

          AS

          BEGIN

           

                  DECLARE @QtdeColunas int

                  SET QtdeColunas = 0

           

                      INSERT INTO TPropostaDT (

                                  CodProposta, 

                                  CodProduto, 

                                  CodLocal, 

                                  QtdeIncluso, 

                                  Renovar, 

                                  VrBase, 

                                  VrIncluso, 

                                  VrExtra, 

                                  QtdeParcelas, 

                                  DescricaoProduto, 

                                  Precificador, 

                                  ID, 

                                  GrupoConsumo, 

                                  VrPrecificador, 

                                  InstrucaoOperacional

                                 

                      SELECT 

                                  @CodPropostaDestino, 

                                  CodProduto, 

                                  CodLocal, 

                                  QtdeIncluso, 

                                  Renovar, 

                                  VrBase, 

                                  VrIncluso, 

                                  VrExtra, 

                                  QtdeParcelas, 

                                  DescricaoProduto, 

                                  Precificador, 

                                  @ID, 

                                  GrupoConsumo, 

                                  VrPrecificador, 

                                  InstrucaoOperacional

                      FROM TPropostaDT 

                      WHERE 

                                  CodProposta = @CodPropostaOrigem

                                  AND CONVERT(varchar, CodProduto) + '|' + convert(varchar, CodLocal) NOT IN (

                                             SELECT 

                                                         CONVERT(varchar, CodProduto) + '|' + convert(varchar, CodLocal)

                                             FROM TPropostaDT

                                             WHERE 

                                                         CodProposta = @CodPropostaDestino

                                             )

           

                  SET QtdeColunas = @@ROWCOUNT

           

           

                      -- Registro de Log

                      INSRT INTO TLog (ID, Modulo, Obs)

                      VALUES (

                                  @ID,

                                  'PropostaCenario',

                                  convert(varchar, ISNULL(@CodPropostaOrigem,0) )         

                                  +'|'+ convert(varchar, ISNULL(@CodPropostaDestino,0) )

                                  +'|'+ convert(varchar, ISNULL(@QtdeColunas,0) )

                             �

          (Message over 64 KB, truncated)

        • Vitor Bueno
          Wolney/André, agradeço muito a atenção. A ideia é esta mesmo, inserção de novos dados na tabela (com chave composta) de maneira que não insira dados
          Mensagem 4 de 6 , 6 de dez de 2016
            Wolney/André, agradeço muito a atenção.



            A ideia é esta mesmo, inserção de novos dados na tabela (com chave composta) de maneira que não insira dados já existentes.

            Só fazendo um clareamento de idéias, o uso do convert foi uma engenharia alternativa para montar uma chave única e pesquisar na tabela detalhe, feita de bate-pronto pois não estava conseguindo fazer uso do exists.

            Estou fazendo outro teste aqui que é a criação de uma coluna calculada automaticamente montando o que atualmente está com o convert. Isto me eliminaria o SEEK e permitiria, sendo uma coluna física, criar índice para melhorar a pesquisa. Vou traçar num banco de teste e comparar as soluções para ver o que melhor traz de desempenho.

            Aproveitando a deixa, vamos por partes:



            1. Fiz alguns testes para a solução do Wolney, com plano de execução, o seguinte retorno:

            Imagem inline 1
            Tempo de Execução do Cliente 15:44:03
            Estatísticas do Perfil da Consulta
              Número de instruções INSERT, DELETE e UPDATE 2 2.0000
              Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE 2 2.0000
              Número de instruções SELECT 3 3.0000
              Linhas retornadas pelas instruções SELECT 3 3.0000
              Número de transações 2 2.0000
            Estatísticas de Rede
              Numero de viagens de ida e volta ao servidor 3 3.0000
              Pacotes TDS enviados do cliente 3 3.0000
              Pacotes TDS recebidos do servidor 9 9.0000
              Bytes enviados do cliente 1684 1684.0000
              Bytes recebidos do servidor 23184 23184.0000
            Estatísticas de Tempo
              Tempo de processamento do cliente 31 31.0000
              Tempo total de execução 62 62.0000
              Tempo de espera em respostas do servidor 31 31.0000






            2. Já a lógica do André, adaptada ao mesmo banco de teste, retornou o seguinte:


            Imagem inline 3


            Tempo de Execução do Cliente 15:49:28 15:49:22 15:49:03 15:48:55
            Estatísticas do Perfil da Consulta
              Número de instruções INSERT, DELETE e UPDATE 2 0 0 0 0.5000
              Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE 5 0 0 0 1.2500
              Número de instruções SELECT 3 0 2 0 1.2500
              Linhas retornadas pelas instruções SELECT 3 0 2 0 1.2500
              Número de transações 2 0 0 0 0.5000
            Estatísticas de Rede
              Numero de viagens de ida e volta ao servidor 3 3 3 3 3.0000
              Pacotes TDS enviados do cliente 3 3 3 3 3.0000
              Pacotes TDS recebidos do servidor 7 3 3 3 4.0000
              Bytes enviados do cliente 1270 1270 1294 1286 1280.0000
              Bytes recebidos do servidor 19752 174 202 164 5073.0000
            Estatísticas de Tempo
              Tempo de processamento do cliente 31 0 15 0 11.5000
              Tempo total de execução 46 15 15 15 22.7500
              Tempo de espera em respostas do servidor 15 15 0 15 11.2500



            Esta solução do me economizou 2 filtros e 1 compute escalar, mas me retornou mais avaliações.






            3. Fiz mais um teste com a proposta do wolney, removendo os withs e executando no mesmo select, o que aumentou uma avaliação, usou 1 filtro a menos no plano de execução e retornou:


            Imagem inline 2


            Tempo de Execução do Cliente 16:06:54 15:44:03
            Estatísticas do Perfil da Consulta
              Número de instruções INSERT, DELETE e UPDATE 2 2 2.0000
              Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE 0 2 1.0000
              Número de instruções SELECT 3 3 3.0000
              Linhas retornadas pelas instruções SELECT 3 3 3.0000
              Número de transações 2 2 2.0000
            Estatísticas de Rede
              Numero de viagens de ida e volta ao servidor 5 3 4.0000
              Pacotes TDS enviados do cliente 5 3 4.0000
              Pacotes TDS recebidos do servidor 10 9 9.5000
              Bytes enviados do cliente 1816 1684 1750.0000
              Bytes recebidos do servidor 21977 23184 22580.5000
            Estatísticas de Tempo
              Tempo de processamento do cliente 15 31 23.0000
              Tempo total de execução 46 62 54.0000
              Tempo de espera em respostas do servidor 31 31 31.0000


            Para efeito de leitura de código, me parece melhor fazer o uso do with, embora, se considerar tempo de execução, este foi ligeiramente menor, certo?








            4. Este é o plano de execução da rotina como ela está (com o convert)

            Imagem inline 4
            Tempo de Execução do Cliente 16:15:58 16:15:50
            Estatísticas do Perfil da Consulta
              Número de instruções INSERT, DELETE e UPDATE 2 0 1.0000
              Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE 0 0 0.0000
              Número de instruções SELECT 2 1 1.5000
              Linhas retornadas pelas instruções SELECT 2 1 1.5000
              Número de transações 2 0 1.0000
            Estatísticas de Rede
              Numero de viagens de ida e volta ao servidor 3 3 3.0000
              Pacotes TDS enviados do cliente 3 3 3.0000
              Pacotes TDS recebidos do servidor 9 3 6.0000
              Bytes enviados do cliente 1170 1178 1174.0000
              Bytes recebidos do servidor 24737 189 12463.0000
            Estatísticas de Tempo
              Tempo de processamento do cliente 16 0 8.0000
              Tempo total de execução 31 0 15.5000
              Tempo de espera em respostas do servidor 15 0 7.5000












            Gostei bastante desta lógica do andré, eu estava bobeando na relação do where da subquery.




            Como que eu posso ponderar qual é melhor em relação a desempenho, considero o tempo total de execução <11.5 da lógica do andré> ?


             



            Grato pelas elucidações.

            Vitor


          • André Luiz
            Vítor, Assim como Wolney mencionou, Não utiliza convenções na cláusula where Esse tipo de procedimento faz com que o índice não seja utilizado. e acaba
            Mensagem 5 de 6 , 6 de dez de 2016
            Vítor,  

            Assim como Wolney mencionou,
            Não utiliza convenções na cláusula where
            Esse tipo de procedimento faz com que o índice não seja utilizado.
            e acaba degradando a sua consulta.

            Enviado do meu iPhone

            Em 6 de dez de 2016, às 16:22, Vitor Bueno <vitor@...> escreveu:

            Wolney/André, agradeço muito a atenção.



            A ideia é esta mesmo, inserção de novos dados na tabela (com chave composta) de maneira que não insira dados já existentes.

            Só fazendo um clareamento de idéias, o uso do convert foi uma engenharia alternativa para montar uma chave única e pesquisar na tabela detalhe, feita de bate-pronto pois não estava conseguindo fazer uso do exists.

            Estou fazendo outro teste aqui que é a criação de uma coluna calculada automaticamente montando o que atualmente está com o convert. Isto me eliminaria o SEEK e permitiria, sendo uma coluna física, criar índice para melhorar a pesquisa. Vou traçar num banco de teste e comparar as soluções para ver o que melhor traz de desempenho.

            Aproveitando a deixa, vamos por partes:



            1. Fiz alguns testes para a solução do Wolney, com plano de execução, o seguinte retorno:

            <image.png>
            Tempo de Execução do Cliente 15:44:03
            Estatísticas do Perfil da Consulta
              Número de instruções INSERT, DELETE e UPDATE 2 2.0000
              Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE 2 2.0000
              Número de instruções SELECT 3 3.0000
              Linhas retornadas pelas instruções SELECT 3 3.0000
              Número de transações 2 2.0000
            Estatísticas de Rede
              Numero de viagens de ida e volta ao servidor 3 3.0000
              Pacotes TDS enviados do cliente 3 3.0000
              Pacotes TDS recebidos do servidor 9 9.0000
              Bytes enviados do cliente 1684 1684.0000
              Bytes recebidos do servidor 23184 23184.0000
            Estatísticas de Tempo
              Tempo de processamento do cliente 31 31.0000
              Tempo total de execução 62 62.0000
              Tempo de espera em respostas do servidor 31 31.0000






            2. Já a lógica do André, adaptada ao mesmo banco de teste, retornou o seguinte:


            Imagem inline 3


            Tempo de Execução do Cliente 15:49:28 15:49:22 15:49:03 15:48:55
            Estatísticas do Perfil da Consulta
              Número de instruções INSERT, DELETE e UPDATE 2 0 0 0 0.5000
              Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE 5 0 0 0 1.2500
              Número de instruções SELECT 3 0 2 0 1.2500
              Linhas retornadas pelas instruções SELECT 3 0 2 0 1.2500
              Número de transações 2 0 0 0 0.5000
            Estatísticas de Rede
              Numero de viagens de ida e volta ao servidor 3 3 3 3 3.0000
              Pacotes TDS enviados do cliente 3 3 3 3 3.0000
              Pacotes TDS recebidos do servidor 7 3 3 3 4.0000
              Bytes enviados do cliente 1270 1270 1294 1286 1280.0000
              Bytes recebidos do servidor 19752 174 202 164 5073.0000
            Estatísticas de Tempo
              Tempo de processamento do cliente 31 0 15 0 11.5000
              Tempo total de execução 46 15 15 15 22.7500
              Tempo de espera em respostas do servidor 15 15 0 15 11.2500



            Esta solução do me economizou 2 filtros e 1 compute escalar, mas me retornou mais avaliações.






            3. Fiz mais um teste com a proposta do wolney, removendo os withs e executando no mesmo select, o que aumentou uma avaliação, usou 1 filtro a menos no plano de execução e retornou:


            <image.png>


            Tempo de Execução do Cliente 16:06:54 15:44:03
            Estatísticas do Perfil da Consulta
              Número de instruções INSERT, DELETE e UPDATE 2 2 2.0000
              Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE 0 2 1.0000
              Número de instruções SELECT 3 3 3.0000
              Linhas retornadas pelas instruções SELECT 3 3 3.0000
              Número de transações 2 2 2.0000
            Estatísticas de Rede
              Numero de viagens de ida e volta ao servidor 5 3 4.0000
              Pacotes TDS enviados do cliente 5 3 4.0000
              Pacotes TDS recebidos do servidor 10 9 9.5000
              Bytes enviados do cliente 1816 1684 1750.0000
              Bytes recebidos do servidor 21977 23184 22580.5000
            Estatísticas de Tempo
              Tempo de processamento do cliente 15 31 23.0000
              Tempo total de execução 46 62 54.0000
              Tempo de espera em respostas do servidor 31 31 31.0000


            Para efeito de leitura de código, me parece melhor fazer o uso do with, embora, se considerar tempo de execução, este foi ligeiramente menor, certo?








            4. Este é o plano de execução da rotina como ela está (com o convert)

            Imagem inline 4
            Tempo de Execução do Cliente 16:15:58 16:15:50
            Estatísticas do Perfil da Consulta
              Número de instruções INSERT, DELETE e UPDATE 2 0 1.0000
              Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE 0 0 0.0000
              Número de instruções SELECT 2 1 1.5000
              Linhas retornadas pelas instruções SELECT 2 1 1.5000
              Número de transações 2 0 1.0000
            Estatísticas de Rede
              Numero de viagens de ida e volta ao servidor 3 3 3.0000
              Pacotes TDS enviados do cliente 3 3 3.0000
              Pacotes TDS recebidos do servidor 9 3 6.0000
              Bytes enviados do cliente 1170 1178 1174.0000
              Bytes recebidos do servidor 24737 189 12463.0000
            Estatísticas de Tempo
              Tempo de processamento do cliente 16 0 8.0000
              Tempo total de execução 31 0 15.5000
              Tempo de espera em respostas do servidor 15 0 7.5000












            Gostei bastante desta lógica do andré, eu estava bobeando na relação do where da subquery.




            Como que eu posso ponderar qual é melhor em relação a desempenho, considero o tempo total de execução <11.5 da lógica do andré> ?


             



            Grato pelas elucidações.

            Vitor


          • Wolney Marconi Maia
            Vitor, Das duas soluções que vc aplicou e demonstrou pelos planos de execução, todas fizeram table scan ( se eu não estou enganado)!! O custo estimado
            Mensagem 6 de 6 , 6 de dez de 2016

              Vitor,

               

              Das duas soluções que vc aplicou e demonstrou pelos planos de execução, todas fizeram table scan ( se eu não estou enganado)!!

              O custo estimado para eles está em 11%

              Para ponderar, você terá que ter os índices necessários para eliminar esses tables scan.

              Tem um Table Spool que consome 44%. Esse cara tá gerando carga no TempDB durante o processamento de consulta.

               

              Voce tem como informar o SP_HELPINDEX  dessa tabela?

               

              Wolney Marconi Maia
              http://imagem.imprensaoficial.com.br/mailing/email_spacer.pngRede e Infraestrutura - TI
              http://imagem.imprensaoficial.com.br/mailing/email_spacer.png11-2799-9892

              Só imprima se realmente for necessário, pense em sua responsabilidade com o meio ambiente.
O conteúdo desse e-mail ou de seus anexos é confidencial e restrito ao destinatário da mensagem.
Se, por engano, você recebeu esse e-mail, por favor, notifique o remetente, não faça cópias nem distribua seu conteúdo.

               

               

              De: Vitor Bueno [mailto:vitor@...]
              Enviada em: terça-feira, 6 de dezembro de 2016 16:23
              Para: mssql-l@...
              Assunto: Re: [mssql-l] Melhores práticas - melhorando o script

               

              Wolney/André, agradeço muito a atenção.

               

               

               

              A ideia é esta mesmo, inserção de novos dados na tabela (com chave composta) de maneira que não insira dados já existentes.

               

              Só fazendo um clareamento de idéias, o uso do convert foi uma engenharia alternativa para montar uma chave única e pesquisar na tabela detalhe, feita de bate-pronto pois não estava conseguindo fazer uso do exists.

               

              Estou fazendo outro teste aqui que é a criação de uma coluna calculada automaticamente montando o que atualmente está com o convert. Isto me eliminaria o SEEK e permitiria, sendo uma coluna física, criar índice para melhorar a pesquisa. Vou traçar num banco de teste e comparar as soluções para ver o que melhor traz de desempenho.

               

              Aproveitando a deixa, vamos por partes:

               

               

               

              1. Fiz alguns testes para a solução do Wolney, com plano de execução, o seguinte retorno:

               

              Imagem inline 1

              Tempo de Execução do Cliente 15:44:03     

              Estatísticas do Perfil da Consulta            

                Número de instruções INSERT, DELETE e UPDATE 2        2.0000

                Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE  2         2.0000

                Número de instruções SELECT    3        3.0000

                Linhas retornadas pelas instruções SELECT    3        3.0000

                Número de transações 2        2.0000

              Estatísticas de Rede            

                Numero de viagens de ida e volta ao servidor 3        3.0000

                Pacotes TDS enviados do cliente     3        3.0000

                Pacotes TDS recebidos do servidor   9        9.0000

                Bytes enviados do cliente 1684     1684.0000

                Bytes recebidos do servidor    23184         23184.0000

              Estatísticas de Tempo           

                Tempo de processamento do cliente   31       31.0000

                Tempo total de execução   62       62.0000

                Tempo de espera em respostas do servidor 31       31.0000

               

               

               

               

               

               

              2. Já a lógica do André, adaptada ao mesmo banco de teste, retornou o seguinte:

               

               

              Imagem inline 3

               

               

              Tempo de Execução do Cliente 15:49:28      15:49:22      15:49:03      15:48:55     

              Estatísticas do Perfil da Consulta                                         

                Número de instruções INSERT, DELETE e UPDATE 2        0        0         0        0.5000

                Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE  5        0         0        0        1.2500

                Número de instruções SELECT    3        0        2        0         1.2500

                Linhas retornadas pelas instruções SELECT    3        0        2         0        1.2500

                Número de transações 2        0        0        0        0.5000

              Estatísticas de Rede                                        

                Numero de viagens de ida e volta ao servidor 3        3        3         3        3.0000

                Pacotes TDS enviados do cliente     3        3        3        3         3.0000

                Pacotes TDS recebidos do servidor   7        3        3        3         4.0000

                Bytes enviados do cliente 1270     1270     1294     1286     1280.0000

                Bytes recebidos do servidor    19752         174      202      164       5073.0000

              Estatísticas de Tempo                                       

                Tempo de processamento do cliente   31       0        15       0         11.5000

                Tempo total de execução   46       15       15       15       22.7500

                Tempo de espera em respostas do servidor 15       15       0        15         11.2500

               

               

               

              Esta solução do me economizou 2 filtros e 1 compute escalar, mas me retornou mais avaliações.

               

               

               

               

               

               

              3. Fiz mais um teste com a proposta do wolney, removendo os withs e executando no mesmo select, o que aumentou uma avaliação, usou 1 filtro a menos no plano de execução e retornou:

               

               

              Imagem inline 2

               

               

              Tempo de Execução do Cliente                        16:06:54       15:44:03        

              Estatísticas do Perfil da Consulta                     

                Número de instruções INSERT, DELETE e UPDATE 2        2        2.0000

                Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE  0        2         1.0000

                Número de instruções SELECT    3        3        3.0000

                Linhas retornadas pelas instruções SELECT    3        3        3.0000

                Número de transações 2        2        2.0000

              Estatísticas de Rede                     

                Numero de viagens de ida e volta ao servidor 5        3        4.0000

                Pacotes TDS enviados do cliente     5        3        4.0000

                Pacotes TDS recebidos do servidor   10       9        9.5000

                Bytes enviados do cliente 1816     1684     1750.0000

                Bytes recebidos do servidor    21977         23184         22580.5000

              Estatísticas de Tempo                    

                Tempo de processamento do cliente   15       31       23.0000

                Tempo total de execução   46       62       54.0000

                Tempo de espera em respostas do servidor 31       31       31.0000

               

               

              Para efeito de leitura de código, me parece melhor fazer o uso do with, embora, se considerar tempo de execução, este foi ligeiramente menor, certo?

               

               

               

               

               

               

               

               

              4. Este é o plano de execução da rotina como ela está (com o convert)

               

              Imagem inline 4

              Tempo de Execução do Cliente 16:15:58      16:15:50     

              Estatísticas do Perfil da Consulta                     

                Número de instruções INSERT, DELETE e UPDATE 2        0        1.0000

                Linhas afetadas pelas instruções INSERT, DELETE ou UPDATE  0        0         0.0000

                Número de instruções SELECT    2        1        1.5000

                Linhas retornadas pelas instruções SELECT    2        1        1.5000

                Número de transações 2        0        1.0000

              Estatísticas de Rede                     

                Numero de viagens de ida e volta ao servidor 3        3        3.0000

                Pacotes TDS enviados do cliente     3        3        3.0000

                Pacotes TDS recebidos do servidor   9        3        6.0000

                Bytes enviados do cliente 1170     1178     1174.0000

                Bytes recebidos do servidor    24737         189      12463.0000

              Estatísticas de Tempo                    

                Tempo de processamento do cliente   16       0        8.0000

                Tempo total de execução   31       0        15.5000

                Tempo de espera em respostas do servidor 15       0        7.5000

               

               

               

               

               

               

               

               

               

               

               

               

              Gostei bastante desta lógica do andré, eu estava bobeando na relação do where da subquery.

               

               

               

               

              Como que eu posso ponderar qual é melhor em relação a desempenho, considero o tempo total de execução <11.5 da lógica do andré> ?

               

               

               

               

               

              Grato pelas elucidações.

               

              Vitor

               

               

            Sua mensagem foi enviada com êxito e será entregue aos destinatários em breve.