[Resolvido] Lentidão em consulta acrescentar com Left Join

Tenho uma consulta acrescentar que possue em média 68 mil linhas ((tbl_dynamics_temp), estou fazendo um left join com uma tabela que possue mais de 1 milhão de linhas (tbl_base_dynamics) , o problema é que esta demorando uns 30 minutos para acrescentar as linhas, como posso melhorar o desempenho dessa consulta ?

Abaixo o Código que insere as informações

Obs. O meu where é apenas para que insira informações que não existem na tabela base.

Código:



INSERT INTO tbl_base_dynamics ( [Documento (Cliente)], [Status da Oportunidade], [Canal de Origem], Fase, Status, [Campanha de Origem], [Criada por], [Especialista Fase 6], [Modificado por], [Número da Apólice], [Número da Cotação], [Número da Proposta], [Razão do Status], [Razão do Status da Fase], [Razão Status Apoio Histórico], Seguradora, Sistema, [Nome Completo (Cliente)], [Agência de Origem], [Data da Criação do Agendamento], [Data de Criação], [Data de Melhor Contato], [Data para trabalho], [Data Vencimento do Seguro], [Data Vencimento do Seguro Atualizada], [Data Fase 6], [Data de Modificação], [Funcionário (Especialista Fase 6)], [Agência do Cliente], [Conta Corrente], [Ano Fabricação], [Ano Modelo], [Funcional do Responsável do Agendamento], [Marca do Veículo], [Modelo do Veículo], [Segmento de Origem], [Agência (Agência do Cliente)], [Segmento do Cliente], [Agência (Agência de Origem)], [Categoria (Razão do Status da Fase)], [Gestor 1 Fase 2], [Gestor 2 Fase 2], Proprietário, Mes, Cod_Mes, Ano )

SELECT tbl_dynamics_temp.[Documento (Cliente)], tbl_dynamics_temp.[Status da Oportunidade], tbl_dynamics_temp.[Canal de Origem], tbl_dynamics_temp.Fase, tbl_dynamics_temp.Status, tbl_dynamics_temp.[Campanha de Origem], tbl_dynamics_temp.[Criada por], tbl_dynamics_temp.[Especialista Fase 6], tbl_dynamics_temp.[Modificado por], tbl_dynamics_temp.[Número da Apólice], tbl_dynamics_temp.[Número da Cotação], tbl_dynamics_temp.[Número da Proposta], tbl_dynamics_temp.[Razão do Status], tbl_dynamics_temp.[Razão do Status da Fase], tbl_dynamics_temp.[Razão Status Apoio Histórico], tbl_dynamics_temp.Seguradora, tbl_dynamics_temp.Sistema, tbl_dynamics_temp.[Nome Completo (Cliente)], tbl_dynamics_temp.[Agência de Origem], tbl_dynamics_temp.[Data da Criação do Agendamento], tbl_dynamics_temp.[Data de Criação], tbl_dynamics_temp.[Data de Melhor Contato], tbl_dynamics_temp.[Data para trabalho], tbl_dynamics_temp.[Data Vencimento do Seguro], tbl_dynamics_temp.[Data Vencimento do Seguro Atualizada], tbl_dynamics_temp.[Data Fase 6], tbl_dynamics_temp.[Data de Modificação], tbl_dynamics_temp.[Funcionário (Especialista Fase 6)], tbl_dynamics_temp.[Agência do Cliente], tbl_dynamics_temp.[Conta Corrente], tbl_dynamics_temp.[Ano Fabricação], tbl_dynamics_temp.[Ano Modelo], tbl_dynamics_temp.[Funcional do Responsável do Agendamento], tbl_dynamics_temp.[Marca do Veículo], tbl_dynamics_temp.[Modelo do Veículo], tbl_dynamics_temp.[Segmento de Origem], tbl_dynamics_temp.[Agência (Agência do Cliente)], tbl_dynamics_temp.[Segmento do Cliente], tbl_dynamics_temp.[Agência (Agência de Origem)], tbl_dynamics_temp.[Categoria (Razão do Status da Fase)], tbl_dynamics_temp.[Gestor 1 Fase 2], tbl_dynamics_temp.[Gestor 2 Fase 2], tbl_dynamics_temp.Proprietário, tbl_dynamics_temp.Mes, tbl_dynamics_temp.Cod_Mes, tbl_dynamics_temp.Ano

FROM tbl_dynamics_temp LEFT JOIN tbl_base_dynamics ON (tbl_dynamics_temp.Cod_Mes = tbl_base_dynamics.Cod_Mes) AND (tbl_dynamics_temp.Ano = tbl_base_dynamics.Ano)

WHERE tbl_base_dynamics.Cod_Mes Is Null AND tbl_base_dynamics.Ano) Is Null




Nos meus estudos e antes de perder vários arquivos, lembro que um deles tratava do que era mais rápido: adicionar, editar e excluir registros em massa com instruções em SQL ou através de recordsets.

Resultado:
Adicionar: Operações com recordsets eram mais rápidas
Editar: Operações com recordsets eram mais rápidas
Excluir: Operações com instruções em SQL eram mais rápidas

Então minha dica para sua situação de adição em uma tabela é: faça operações com recorsets.

Abra um recordset somente leitura para encontrar os registros que serão adicionados. ex: currentdb.openrecordset("select...", 8)
Abra um recordset somente adição para a tabela que receberá os dados. ex: currentdb.openrecordset("select...", , 8)

e faça

Código:

while not rsLeitura.eof
    rsAdicao.AddNew
        rsAdicao!Campo1 = rsLeitura!Campo1
        ...
        rsAdicao!CampoN = rsLeitura!CampoN
    rsAdicao.Update
    rsLeitura.movenext
wend

IvanJr.

Fiz por recordset apenas com 3 campos para teste, porém continua lento, pode avaliar o código abaixo se fiz algo errado?

Código:



Dim DB As DAO.Database, rsLeitura As DAO.Recordset, rsAdicao As DAO.Recordset
Dim SQL As String

    Set DB = CurrentDb()
   
   
      SQL = "Select [Documento (Cliente)], StrConv(Format([Data de Criação],""mmm/"") & Year([Data de Criação]),3) AS Mes, Month([Data de Criação])
      AS Cod_Mes, Year([Data de Criação]) AS Ano " _
      & "From tbl_base_dynamics_temp " _
      & "where StrConv(Format([Data de Criação],""mmm/"") & Year([Data de Criação]),3) not in (Select Mes from tbl_base_dynamics)"


  Set rsLeitura = DB.OpenRecordset(SQL, 8)
   
   
  Set rsAdicao = DB.OpenRecordset("tbl_base_dynamics", , 8)
   
    While Not rsLeitura.EOF
        rsAdicao.AddNew
            rsAdicao![Documento (Cliente)] = rsLeitura![Documento (Cliente)]
            rsAdicao!Mes = rsLeitura!Mes
            rsAdicao!Cod_Mes = rsLeitura!Cod_Mes
            rsAdicao!Ano = rsLeitura!Ano
       
       
        rsAdicao.Update
        rsLeitura.MoveNext
Wend




Obrigado.

Alexandre Neves

Obrigado pela dica, tentei porém continua lento ;/

O banco está na rede ou está na sua máquina?

Demorou mais ou demorou menos do que os 30 minutos que levava antes? Simulei o cenário ontem gerando registros aleatórios (conforme a mensagem nº4 e não levou mais que alguns minutos.

Nem deixei ele terminar, esperei por 11 minutos ai desisti

eu percebi que ele da uma travada depois do rsLeitura.MoveNext

eu fiz um teste aqui da seguinte forma

Limprei a tabela que irá receber as informações no caso a (rsAdicao) que possuia mais de 1 milhão de linhas, e executei o código para inserir linhas de uma tabela que contem 68 mil linhas, e está a 5 min rodando aqui.

Outro teste que eu fiz foi limpar ambas as tabelas e deixar com poucos registros (40) registros para cada, rodou super rapido, então estou vendo que a lentidão esta ocorrendo pelo fato de ter que inserir os 68 mil registros... ;/

vou ver se consigo anexar o banco pra vc dar uma olhada pode ser?

Ahh e faltou uma parte no código que é o CPF para concatenar também se não ele irá inserir apenas 1 registro se eu olhar só pela coluna Mes e Ano

então o código correto é esse

Código:



Dim DB As DAO.Database, rsLeitura As DAO.Recordset, rsAdicao As DAO.Recordset
Dim SQL As String

    Set DB = CurrentDb()
    
    
      SQL = "Select [Documento (Cliente)], StrConv(Format([Data de Criação],""mmm/"") & Year([Data de Criação]),3) AS Mes, Month([Data de Criação]) AS Cod_Mes, Year([Data de Criação]) AS Ano " _
      & "From [Cópia de tbl_base_dynamics_temp] " _
      & "where StrConv(Format([Data de Criação],""mmm/"") & Year([Data de Criação]),3)& [Documento (Cliente)] not in (Select Mes&[Documento (Cliente)] from tbl_base_dynamics_Teste)"


   Set rsLeitura = DB.OpenRecordset(SQL,
    
    
   Set rsAdicao = DB.OpenRecordset("tbl_base_dynamics_Teste", ,
    
    While Not rsLeitura.EOF
        rsAdicao.AddNew
            rsAdicao![Documento (Cliente)] = rsLeitura![Documento (Cliente)]
            rsAdicao!Mes = rsLeitura!Mes
            rsAdicao!Cod_Mes = rsLeitura!Cod_Mes
            rsAdicao!Ano = rsLeitura!Ano
        
        
        rsAdicao.Update
        rsLeitura.MoveNext
Wend




Pode ser sim... com o banco dá pra averiguar melhor. No Aguardo.

Bom dia
Experimente em 2 etapas
1 - adicione todos os registos
2 - elimine os duplicados

Olá!

Atente para a dica número 1 deste meu artigo (copie e cole o endereço abaixo no seu navegador):

usandoaccess.com.br/dicas/gerar-milhoes-registros-teste-access.asp

Sucesso!

Paulo, novidades?

IvanJr.

Tentei postar o Banco, porém a politica de segurança da empresa barra a subida do arquivo

vou fazer um teste aqui com os comentários acima, já respondo se deu ou não.

Obrigado pela atenção

Alexandre Neves

Eu nunca consegui fazer uma consulta para Eliminar Duplicidade do Access

Pode me dizer como fazer isso? essa seria uma ótima solução

Obs. Eliminar duplicidade não é pra excluir todas as linhas, mas sim aquelas que passarem de uma.

Ex: Paulo
Paulo

Deve ficar 1 Paulo.

Isso é possível ?

IvanJr.

Fiz um teste aqui diminuindo a quantidade de linhas para serem inclusas (7 mil linhas para serem inclusas na tabela)

Usando o Comando RecordSet o tempo para inclusão foi de 17~20 segundos
Usando uma consulta Acrescentar com Left Join, o tempo de resposta foi de 1 segundo

Estou entendendo que por RecordSet leva-se muito mais tempo do que por consulta.

Chegando em casa hoje vou criar uma tabela com 1 milhão de linhas na base e 60 mil para incluir na tabela, posto aqui e você tenta ver se consegue executar rápido com recordset ou com algum outro comando, vai ser a única forma de resolver isso.

Bom dia
Sobre consultas de duplicados pode fazer através do assistente

Paulo,

Existem algumas considerações que se fazem necessárias para otimizar o desempenho do seu bd. Seguem elas:

- Verificar tamanho de colunas de suas tabelas: geralmente, quando se cria uma tabela, alguns usuarios costumam deixar o tamanho 255 para campos que armazenam 10, 20, ou 30 caracteres.

- Sempre que utilizar INNER JOIN, LEFT JOIN, RIGHT JOIN, faça com campos indexados.

- Funções de domínio agregado como DSum, DLookup, DCount também geram lentidão, pois quando são executadas, elas percorrem toda a tabela. Se possível, evite-as.

- Use regularmente o "Compactar/Reparar"

- Use o analisador de desempenho para otimizar sua base de dados

Espero ter ajudado.

[]'s

Pessoal, consegui resolver!!

A a minha tabela possue varias colunas como vocês viram no código, porém no left join eu estava cruzando apenas os campos Cod_Mes e Ano (pois preciso incluir apenas informações de meses que não foram inclusas) ai resolvi testar colocando mais um campo pra cruzar sendo ele o [Documento do (Cliente)], feito isso o tempo para incluir as linhas foi de 30 minutos para 30 segundos.

Pergunta: Porque isso? Quanto mais campo para cruzar melhor? Ou é porque eu estava cruzando com Cod_Mês e Ano e como ele se repete por diversas vezes é preciso varrer mais o meu banco ?

Obs. Depois fiz o teste colocando apenas um Left join pelo campo [Documento do (Cliente)] e também foi rápido, sendo assim estou entendendo que o que estava ferrando minha consulta era o Left Join pelo Cod_Mês e Ano

De Qualquer forma muitíssimo obrigado a todos

Bom dia
Ainda bem que resolveu e mostrou a solução para ajudar outros. É este o espírito do fórum