Acelerando o AdvPL – Importação de tabelas

Introdução

Existem muitas situações onde existe a necessidade de alimentar ou importar tabelas para uso do ERP Microsiga / Protheus. Quando esta necessidade envolve um grande número de registros, e um curto espaço de tempo, precisamos fazer esta operação ser o mais rápida possível. Nesse post vamos abordar algumas técnicas para realizar este tipo de operação.

  • Crie os índices da tabela apenas após a importação dos dados. 

Se a tabela está vazia, e os indices de trabalho desta tabela já existem, cada novo registro acrescentado faz o banco de dados atualizar todos os índices durante a inserção dos dados. Sem os índices, o banco apenas insere os dados. A inserção vai ser muito mais rápida, e os índices também são criados mais rápido no final do processo de importação, quando você já inseriu todos os registros na tabela. Se você precisa ter um índice na tabela, no processo de importação, por exemplo para fazer alguma validação ou query na tabela enquanto ela está sendo importada, crie apenas este índice.

  • Trabalhe com blocos de inserções transacionadas

Os bancos de dados relacionais garantem a integridade dos dados através do registro de um “LOG Transacional”. Cada operação de inserção primeiro é gravada no LOG do SGDB, e depois é “efetivada” — o Banco de Dados faz o COMMIT da informação. Quando não estamos usando uma transação explícita, cada instrução executada no Banco de Dados faz COMMIT automaticamente no final da instrução. É mais eficiente abrir uma transação — BEGIN TRANSACTION — pois enquanto a transação está aberta, cada instrução gera apenas o LOG Transacional, e depois de fazer por exemplo 1000 inserções na base de dados, você encerra a transação — END TRANSACTION no AdvPL — e neste momento o Banco de dados faz o COMMIT de todas as linhas do LOG de uma vez.

  • A nova tabela que vai receber os dados deve ser criada pelo DBAccess 

Mesmo que você saiba quais são os tipos dos campos da estrutura da tabela final, onde os dados serão gravados, quem sabe criar a tabela nos padrões e moldes necessários para o correto funcionamento da aplicação é o Protheus, que passa para o DBAccess a estrutura da tabela a ser criada. Internamente, o DBAccess cria constraints e outros elementos, além de alimentar algumas tabelas de controle interno. Deixe o DBAccess fazer a criação da tabela. Você pode até apagar os índices de dados para fazer a importação da tabela após eles terem sido criados, porém não apague o índice da primary key (R_E_C_N_O_), e se a tabela possui índice único (sufixo _UNQ), mantenha ele também, caso você queira que o próprio banco de dados aborte a operação no caso de haver uma chave única duplicada nos dados sendo importados.

  • Usando as funções de baixo nível de tabelas do AdvPL 

Caso a importação dos dados seja realizada por uma aplicação AdvPL, que foi criada para esta finalidade, que não vai concorrer com os processos do ERP, podemos usar diretamente as funções de baixo nível de tabelas do AdvPL. As funções de mais alto nível — como RECLOCK e MSUNLOCK devem ser usadas dentro dos programas do ERP, no ambiente do ERP, pois elas têm tratamentos adicionais ligados ao FrameWork do ERP. Se, ao invés disso, usarmos diretamente as funções diretas de manutenção de dados, em um cenário controlado, podemos obter mais ganhos de desempenho — DBAppend() para acrescentar um novo registro, DBRUnlock() — sem nenhum parâmetro — para soltar o bloqueio (lock) de todos os registros obtidos durante aquele processo (lembrando que eu não posso soltar o lock dentro de transação), abrir e fechar transação sem usar BEGIN e END TRANSACTION — Usando diretamente a função TCCommit().

IMPORTANTE

O Uso das funções básicas do AdvPL para a manutenção de tabelas NÃO DEVE SER MISTURADO com o uso das funções do Framework. Dentro de um processo preparado para o contexto de execução do ERP, BEGIN TRANSACTION trabalha junto com END TRANSACTION, Reclock() e MSUnlock() trabalham juntas, ambas possuem tratamentos diferenciados e automáticos quando você programa dentro do ambiente do ERP e/ou dentro de um bloco transacionado, e todas elas internamente fazem uso das funções de baixo nível do AdvPL. Se você vai fazer uma aplicação que não vai rodar dentro do contexto do ERP, como por exemplo as aplicações que eu publico no BLOG — CRUD em AdvPL por exemplo — você deve usar apenas as funções de baixo nível. Misturar chamadas diretas das funções de Framework com chamadas das funções de baixo nível NO MESMO PROCESSO pode causar efeitos imprevisíveis.

  • Abra a tabela de destino em modo exclusivo

Se a sua aplicação vai usar o ambiente do ERP — Prepare Environment, Reclock e afins, abra a tabela de destino em modo exclusivo — veja como na documentação da função ChkFile() do Framework AdvPL. Caso você vá utilizar as funções de baixo nível de acesso a tabelas, veja como abrir uma tabela em modo exclusivo na documentação da função DBUseArea() da TDN ou do comando USE da TDN. Caso a tabela esteja aberta em modo compartilhado, cada inserção de um novo registro faz o DBAccess registrar um bloqueio desse registro na inserção, e cada desbloqueio gera uma requisição a mais ao DBAccess para soltar o bloqueio. Quando usamos a tabela em modo EXCLUSIVO, somente o meu processo está usando a tabela, e não é feito nenhum lock no DBAccess.

  • Use um BULK INSERT ou ferramenta de apoio do próprio Banco de Dados

Não há forma mais rápida de inserir dados em uma tabela senão usando uma ferramenta do próprio Banco de Dados. Porém, nem sempre é possível fazer isso de forma automática, e alguns cuidados são necessários. Primeiro, a tabela já deve ter sido previamente criada pelo DBAccess. Depois, a gravação dos dados pelo Banco deve ser feita da mesma forma que o DBAccess faria — Qualquer campo do tipo caractere deve estar preenchido com espaços em branco até o final do tamanho do campo, nenhum campo pode ter valor NULL — exceto campo MEMO –, um campo “D” Data em AdvPL em uma tabela do DBAccess é gravado como “C” Caractere com 8 posições, no formato AAAAMMDD, uma data vazia são 8 espaços em branco, um campo “L” lógico do AdvPL é criado pelo DBAccess no SGDB como um campo de 1 Caractere, contento a letra “T” para valor verdadeiro e “F” para falso, um campo numérico têm o valor default = 0 (zero).

  • Diminua a distância entre as informações de origem e destino 

Normalmente uma ferramenta do próprio banco de dados que faz inserção deve ser executada na própria máquina onde está o SGDB. Isto a torna ainda mais rápida. Se você está fazendo uma importação de dados que estão sendo lidos pelo Protheus de alguma fonte de dados, e depois repassadas ao Banco de Dados através do DBAccess, e cada um destes componentes (Protheus, DBAccess e o SGDB) estão em máquinas distintas, onde existe uma interface de rede entre cada um destes componentes, fatalmente a rede vai influenciar no desempenho desta operação. Se for possível eliminar pelo menos uma camada de rede entre as aplicações, pelo menos na hora de fazer a importação, ela tende a ser mais rápida.

  • Se posssível, leia as informações da origem com Query

Se a origem dos dados for uma tabela da base de dados, ao invés de abrir a tabela no modo ISAM — com ChkFile() ou DBUseArea() — leia os dados da tabela com um SELECT — O DBAccess a partir de 2017 passou a trafegar blocos de linhas entre o DBAccess e o Protheus, o que é muito mais performático principalmente quando ambos estão em máquinas diferentes. No acesso ISAM emulado, cada DBSkip() na tabela traz apenas um registro. Quando usamos a Query, várias linhas são trafegadas em uma unica requisição, e cada DBSkip() na Query consome a próxima linha em cache sem usar a rede. Quando as linhas do cache terminarem, o DBAccess envia mais um bloco de linhas.

Inserção direta via TCSqlExec()

Da mesma forma que é possível usar uma aplicação externa para inserir dados direto no banco, é possível também realizar a inserção direta no banco em AdvPL usando TCSqlEXec() — MAS, POREM, TODAVIA, CONTUDO, ENTRETANTO, você precisa tomar os mesmos cuidados como se você estivesse alimentando estes dados por fora, E, você somente vai ter algum ganho de desempenho se você montar uma string de parâmetro com mais de uma inserção — por exemplo, inserindo múltiplas linhas na mesma instrução de insert — caso o banco de dados tenha suporte para isso — ou concatenando mais de uma instrução de inserção na mesma requisição, separando as instruções com “;” — ponto e vírgula.

cStmt := "INSERT INTO TABELA(CPO1,CPO2,R_E_C_N_O_) VALUES ('00001','Nome',1);"
cStmt += "INSERT INTO TABELA(CPO1,CPO2,R_E_C_N_O_) VALUES ('00002','Endereço',2);"
(...)
nRet := TCSqlExec(cStmt)

No exemplo acima, você pode por exemplo montar uma string de inserção com por exemplo 32 KB de dados, e enviar isso de uma vez para o banco de dados. Mas, inserir dados desta forma possui vantagens e desvantagens:

  • Uma requisição insere vários registros na mesma instrução. Usando DBAppend() / Reclock(), é inserido um registro por vez.
  • Uma requisição maior no lunar de várias requisições menores aproveita muito mais a banda de rede disponível.

Por outro lado…

  • Para montar a inserção dos valores de forma literal, você deve tratar manualmente a existência de caracteres especiais, aspas simples como conteúdo de campo, e para alguns bancos tratar “escape sequences” e caracteres especiais.
  • Campos MEMO (BLOB/CLOB/LONGVARBINARY) normalmente não são suportados com envio literal pela instrução de INSERT feita desta forma. Você acaba fazendo a inserção primeiro dos dados dos campos de tamanho fixo, e depois tem que usar o modo ISAM de acesso para alterar o registro em AdvPL, para então alimentar o conteúdo do campo.

IMPORTANTE

Nenhum dos métodos acima é recomendável de ser feito em processos concorrentes, em modo compartilhado, com outros programas inserindo informações na tabela em questão. As sugestões deste post mostram apenas jeitos diferentes e alternativas de importação de dados puros direto para tabelas de um banco de dados. Como nestes exemplos os dados são praticamente escritos “direto” nas tabelas, a aplicação que está importando os dados deve ser a responsável por criticar os dados de entrada, e garantir que a sua escrita esteja em conformidade com as regras do ERP. Mesmo uma operação de cadastro feita no ERP pode disparar integrações e processos auxiliares que alimentam outras tabelas. Mexer diretamente na base de dados sem respeitar as regras de negócio e/ou as validações do produto podem gerar comportamentos inesperados e indesejáveis no produto e nas rotinas que consomem estes dados. A forma mais segura de importar dados em tabelas do ERP é a utilização de rotinas automáticas, onde fatalmente vai existir um pênalti de desempenho para validar todos os valores de todos os campos informados, mas desta forma evita-se a alimentação incompleta ou inconsistente de dados que pode prejudicar o comportamento do produto.

Conclusão

Não existe “almoço grátis”, alguém está sempre pagando a conta. Uma inserção de dados é um processo deveras simples, você obtêm maior desempenho basicamente realizando requisições em blocos, e pode ganhar mais reduzindo as operações intermediárias intrínsecas ao processo — porém este ganho normalmente abre mão de validações que visam garantir a integridade das informações imputadas, ou acabam exigindo mais trabalho e etapas adicionais para a conclusão do processo.

Novamente agradeço pela audiência, curtidas e compartilhamentos, e desejo a todos TERABYTES DE SUCESSO !!! 😀

Referências

 

10 comentários sobre “Acelerando o AdvPL – Importação de tabelas

  1. Excelente información!!!

    Una duda, ¿Cómo puedes apagar los indices (R_E_C_N_O_) y (sufixo _UNQ)? ¿Hay manera de hacerlo por código?

    Saludos.

    Curtido por 1 pessoa

  2. A parte melhor da leitura dos posts q vc tem escrito são as particularidades envolvidas na forma como o TOP ou o client ou o Server se comportam.
    Muito bom !!! Muito bom.
    Vc tá com uma produtividade impressionante de Setembro pra cá. São tantas informações que dá gosto de ver !!!!
    Parabéns pelo trabalho e muito obrigado por dividir tudo isso conosco.

    Curtido por 1 pessoa

  3. Julio,

    Tengo una duda, en caso de tener que crear una tabla por medio de FWTemporaryTable, es posible tener la tabla en modo exclusivo?

    Saludos y muchas gracias.

    Curtido por 1 pessoa

      • Gracias Julio,

        Trataba de seguir las recomendaciones de abrir la tabla en modo exclusivo para hacer una carga masiva, pues debido a la cantidad de registros (3,000,000 aproximadamente) la carga es muy lenta y no he encontrado solución a eso pues he intentado con el DbAppend() pero al ser registro a registro es muy lento, para la lectura de datos utilicé tu clase ZFWReadTXT y le hice una modificación para regresar el array directamente, en vez de usar un separa después de la lectura de la línea, esto mejoró el tiempo de lectura de 24 minutos a 6 minutos :), sin embargo la carga a la tabla sigue siendo un problema.

        METHOD ReadArray( /*@*/ aArray, cSeparador ) CLASS ZFWReadTXT
        Local cTmp := ”
        Local cBuffer
        Local nRPos
        Local nRead
        // Incrementa o contador da posição do Buffer
        ::_PosBuffer++
        If ( ::_PosBuffer <= len(::_Buffer) )
        // A proxima linha já está no Buffer …
        // recupera e retorna
        aArray := StrTokArr2(::_Buffer[::_PosBuffer],cSeparador)
        Return .T.
        Endif

        If ( ::nFReaded < ::nFSize )
        // Nao tem linha no Buffer, mas ainda tem partes
        // do arquivo para ler. Lê mais um pedaço

        nRead := fRead(::nHnd , @cTmp, ::nFBuffer)
        if nRead 0
        // Pega o que sobrou apos a ultima quegra e guarda no resto
        ::_Resto := substr(cBuffer , nRPos + len(::cFSep))
        // Isola o resto do buffer atual
        cBuffer := left(cBuffer , nRPos-1 )
        Else
        // Nao tem resto, o buffer será considerado inteiro
        // ( pode ser final de arquivo sem o ultimo separador )
        ::_Resto := ”
        Endif

        // Limpa e Recria o array de cache
        // Por default linhas vazias são ignoradas
        // Reseta posicionamento de buffer para o primeiro elemento
        // E Retorna a primeira linha do buffer
        aSize(::_Buffer,0)
        ::_Buffer := StrTokArr2( cBuffer , ::cFSep )
        ::_PosBuffer := 1
        aArray := StrTokArr2(::_Buffer[::_PosBuffer],cSeparador)
        Return .T.

        Endif

        // Chegou no final do arquivo …
        ::_SetError(-4,”File is in EOF”)
        Return .F.

        Gracias por tu respuesta.

        Saludos.

        Curtido por 1 pessoa

      • Raul,

        Yo que agradezco este nuevo método. Hay algunas técnicas para insertar registros más rápidamente, en algunas bases de datos abrir una transacción y hacer “commit” cada 1000 registros mejora bastante. Otra forma es montar en AdvPL una cadena con hasta 64 KB de inserts, separados por “;” y hacer la inserción en la base de datos usando “TCSqlExec ()”, le recomiendo hacer algunos experimentos con estas ideas!

        Saludos y abrazos

        Curtir

  4. Primeiramente ótimo conteúdo, parabéns!

    Estou fazendo uma importação via TcLink, eu conecto em outro banco, faço a consulta via DbUseArea, depois volta a conexão para o ERP e faço While para a gravação na tabela do ERP.

    Eu consigo otimizar essa importação fazendo algo parecido com o SQL, exemplo:
    INSERT INTO TabelaOracle
    SELECT * FROM TabelaProtheus;

    Desde já, agradeço!

    Curtido por 1 pessoa

    • Olá David, obrigado pela audiência !!

      Então, um SELECT com INSERT entre Bancos até seria possível usando alguma coisa como um “Linked Server”, porém você vai ter que fazer um “de-para” entre os tipos de dados que o DBAccess usa para os campos das tabelas em cada banco, e cada tabela pode ter suas particularidades, como uso de campos CLOB, numeração automática de R_E_C_N_O_, etc. Eu sei que dá pra fazer, mas não pesquisei mais a fundo o uso deste tipo de recurso !

      Abraços e boa sorte !!!

      Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s