Vídeo recomendado
https://youtu.be/diWPPPhW-9E

Tempo de consulta no ACCESS [RESOLVIDO]

Avatar do usuário
guitarhead
Colaborador
Colaborador
Mensagens: 10
Registrado em: Qua Fev 09, 2011 10:09 pm

Tempo de consulta no ACCESS [RESOLVIDO]

Mensagem por guitarhead »

E ai Tomás e camaradas,

Estou com um problema um pouco difícil de resolver e gostaria de saber se alguém tem alguma dica sobre o que pode ser.

Eu possuo uma pasta de trabalho no excel com duas planilhas contendo diversas linhas de matriculas e um banco de dados que guarda o nome, equipe e etc dessas matriculas.

O código tem por finalidade obter o número da matricula, acessar o banco e retornar com o nome da equipe referente aquela matricula.

Porém em uma planilha eu tenho 2000 matriculas em média e a consulta dura um pouco mais de 10 segundos para preencher todas as linhas.

Já na segunda planilha (que é praticamente idêntica a primeira, onde a unica diferença é o nome dela), eu tenho aproximadamente 1200 matriculas (menos do que a primeira), porém a consulta leva em torno de 5 minutos para preencher os dados.

Já fiz diversos testes trocando as matriculas de posição (entre a planilha 1 e 2), já revi o código, e o resultado é sempre o mesmo. A planilha 1 roda a consulta completa em 10 segundos e a planilha 2 demora uns 5 minutos.

Será que alguém sabe o que pode causar essa lentidão na consulta?

Segue o código abaixo:

Código: Selecionar todos

'Esse é o procedimento
Public Sub ConectarBanco(ByVal strPLAN As String, ByVal strCELLMAT As String, strSBEQUIP As String, strEQUIP As String)
Dim lgEndCell As Long, i As Long

Windows(MACRO).Activate
Sheets(strPLAN).Select

'Função que retorna o número de linhas que são preenchidas com dados
lgEndCell = lastValue("*")

    For i = 2 To lgEndCell
        'Range(strCELLMAT & CStr(i) possui o número da matricula
        rst.Open "SELECT ativ, equipe FROM MATRICULAS Where login = '" & Range(strCELLMAT & CStr(i)) & "'", cnn, adOpenStatic

            If rst![ativ] = "" Then
                Range(strSBEQUIP & CStr(i)) = "N/E"
                Range(strEQUIP & CStr(i)) = "N/E"
            Else
                Range(strSBEQUIP & CStr(i)) = rst![ativ]
                Range(strEQUIP & CStr(i)) = rst![equipe]
            End If
 
        rst.Close
    Next

Set rst = Nothing

End Sub

Código: Selecionar todos

'Essas são as chamadas
'Planilha 1
Call ConectarBanco("ENCERRADOS_BaseCompleta", "V", "AZ", "BA")
'Planilha 2
Call ConectarBanco("ENCERRADOS_UltimoAcionamento", 	"V", "AZ", "BA")
Obrigado pela atenção.
Abraços
Editado pela última vez por guitarhead em Ter Mar 29, 2011 9:54 pm, em um total de 1 vez.


Disable adblock

This site is supported by ads and donations.
If you see this text you are blocking our ads.
Please consider a Donation to support the site.


Avatar do usuário
webmaster
Administrador
Mensagens: 3114
Registrado em: Sex Jul 24, 2009 2:44 pm
Contato:

Re: Tempo de consulta no ACCESS

Mensagem por webmaster »

Guitar,

Vi o código e o que dá para concluir é que a lentidão com certeza está vinculada a quantidade de vezes que conecta no banco de dados. A idéia para otimizar isso é, antes de conectar no banco, colete todos os dados de filtro que precisa, monte a query com esses valores e consulte o banco uma só vez.

Consegue visualizar a solução? Provavelmente precisará da cláusula IN no WHERE para facilitar sua vida.

Abraços


Avatar do usuário
guitarhead
Colaborador
Colaborador
Mensagens: 10
Registrado em: Qua Fev 09, 2011 10:09 pm

Re: Tempo de consulta no ACCESS

Mensagem por guitarhead »

webmaster escreveu:Guitar,

Vi o código e o que dá para concluir é que a lentidão com certeza está vinculada a quantidade de vezes que conecta no banco de dados. A idéia para otimizar isso é, antes de conectar no banco, colete todos os dados de filtro que precisa, monte a query com esses valores e consulte o banco uma só vez.

Consegue visualizar a solução? Provavelmente precisará da cláusula IN no WHERE para facilitar sua vida.

Abraços
E ai Tomas,
Valeu pela força novamente.

Consegui visualizar a solução em partes e montei o seguinte código para testar.

Código: Selecionar todos

Public Sub retorno_valor()

'Conecta ao BD
Call OpenConect

rst.Open "SELECT nome FROM MATRICULAS where login IN ('MAT010101','MAT020202')", cnn, adOpenStatic

MsgBox rst![nome]

rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

End Sub
Porém quando peço para exibir o resultado pelo msgbox, somente aparece o primeiro nome.
Sei que precisarei criar uma estrutura de repetição para exibir todas, porém não consigo acessar o segundo nome.

Como seria?

rst(1)![nome] ou rst(2)![nome]?

Tentei mas não funcionou! =P

Se puder iluminar com mais essa informação, agradeço! =S
Abraços


@@@@@@@@ EDITADO @@@@@@@@@

Eu consegui acessar o segundo nome, mas tenho mais problemas em usar essa instrução. =/
Segue abaixo:

Código: Selecionar todos

Public Sub retorno_valor()

Call OpenConect

mat1 = "MAT010101" 'Essa matricula existe
mat2 = "MAT020202" 'Essa matricula existe
mat3 = "MATINVALID" 'Essa matricula NÃO existe
mat4 = "MAT020202" 'Matricula existe

rst.Open "SELECT nome FROM MATRICULAS where login IN ('" & mat1 & "','" & mat2 & "','" & mat3 & "','" & mat4 & "')", cnn, adOpenStatic

While rst![nome] <> ""

    MsgBox rst![nome]
    rst.MoveNext

Wend

rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

End Sub
Com esse código acima, eu consigo acessar os dois nomes das matriculas existentes no banco.
O problema que ele teria que me informar 3 nomes (dois repetidos + um exclusivo)
E quando eu chego na matricula que não existe no banco, o vba acusa erro numero: 3021 "BOF ou EOF são verdadeiros, ou o registro atual foi excluído."

Outra coisa que eu notei é que a ordem das matriculas não é seguida.
Por exemplo:

MAT010101 = "Thiago"
MAT020202 = "Carol"

Ele exibe sempre primeiro 'CAROL' por causa da ordem alfabetica.
Só que em minha planilha, as matriculas estarão em diferentes ordens. =S

Cara, desculpe o incoveniente e se puder ajudar, agradeço.
Abraços


Avatar do usuário
webmaster
Administrador
Mensagens: 3114
Registrado em: Sex Jul 24, 2009 2:44 pm
Contato:

Re: Tempo de consulta no ACCESS

Mensagem por webmaster »

Guitar,

Pelo que entendi, o código está no caminho. Só recomendo realmente usar o While Not rs.EOF. EOF significa End Of File (final de arquivo), ou seja, que você já chegou no final dele, não há mais nada para ler. Se executar um MoveNext neste estado, receberá um erro.

Quanto ao restante, ordenação, critérios, enfim, você vai conseguir resolver tudo isso com o que o SQL oferece. No caso da ordenação, ORDER BY, filtros, WHERE, exclusividade de registros, DISTINCT, agrupamente, GROUP BY, e por aí vai.

Veja se uma destas resolve seu problema. No seu caso, o cuidado com o EOF mais o ORDER BY pelo código devem ser suficientes.

Abraços


Disable adblock

This site is supported by ads and donations.
If you see this text you are blocking our ads.
Please consider a Donation to support the site.


Avatar do usuário
guitarhead
Colaborador
Colaborador
Mensagens: 10
Registrado em: Qua Fev 09, 2011 10:09 pm

Re: Tempo de consulta no ACCESS

Mensagem por guitarhead »

webmaster escreveu:Guitar,

Pelo que entendi, o código está no caminho. Só recomendo realmente usar o While Not rs.EOF. EOF significa End Of File (final de arquivo), ou seja, que você já chegou no final dele, não há mais nada para ler. Se executar um MoveNext neste estado, receberá um erro.

Quanto ao restante, ordenação, critérios, enfim, você vai conseguir resolver tudo isso com o que o SQL oferece. No caso da ordenação, ORDER BY, filtros, WHERE, exclusividade de registros, DISTINCT, agrupamente, GROUP BY, e por aí vai.

Veja se uma destas resolve seu problema. No seu caso, o cuidado com o EOF mais o ORDER BY pelo código devem ser suficientes.

Abraços
E ai Tomás,
Obrigado pelo esclarecimento.

@@@@@@@@@@@@ EDITADO @@@@@@@@@@@@@@@

Tomás,
Eu achei uma solução na qual ainda não sei explicar exatamente como resolver.

Criei uma cópia da planilha 1 e renomiei a copia da Plan1 para Plan2 e tentei rodar novamente.
Como imaginei, rodou rapido igual a Plan1.

Então o tal problema estava relacionado alguma forma a formatação da Plan2. =/
Confuso... =(

Mas obrigado por todos os esclarecimentos e até a forma de realiza a consulta.
Obrigado e abraços!

Consegui implementar o código como havia me informado, porém estranhamente o problema ainda não foi solucionado.

Inicialmente eu achei que era demora na consulta de dados na planilha 2, já que a planilha 1 levava cerca de 10 segundos para realizar a consulta enquanto a plan2 uns 5 minutos.
Agora a Plan1 ainda leva uns 10 segundos e a Plan2 uns 3 minutos.

Sendo a rotina de consulta a mesma para as duas, existe alguma coisa que possa influencia no calculo de uma e de outra?

Vou reavaliar o código para ver se há algum buraco.
Obrigado pelas dúvidas esclarecidas.

Abraços


Disable adblock

This site is supported by ads and donations.
If you see this text you are blocking our ads.
Please consider a Donation to support the site.


Responder