Conexão com MySQL - Lentidão

Bom dia!

Estou com um BD, 10 usuários, vinculado ao MYSQL. Está apresentando muita lentidão e travando com a mensagem "Não Respondendo".

Já verifiquei o servidor do MySQL e está ok, processdo , espaço.

Alguém tem alguma dica de como posso resolver isso?


Abs

Janette

Você está a usar consultas internas ás tabelas vinculadas do MySQL ?

Ou está a usar via VBA, por exemplo:

Código:

Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("tabela", DB_OPEN_TABLE)
rst.Index = "primarykey"

E quantos registros têm as tabelas do MySQL, 2 ou 2 milhões ???

Na verdade eu tenho os dois casos, tem bastante consultas internas (criar tabela, adiciona) e tem VBA também. A maior tabela tem 100 mil registros.


Exemplo:


Dim dbp As Database
Dim i As Integer
Dim sql_del As String
Dim sql_drop As String
Set dbp = DBEngine.Workspaces(0).Databases(0)
Dim nTable As String
sql_del = "DELETE * FROM "
sql_drop = "DROP TABLE "
nTable = "tab_temppend"
dbp.TableDefs.Refresh
       
For i = 0 To dbp.TableDefs.Count - 1
   If nTable = dbp.TableDefs(i).name Then
       DoCmd.SetWarnings False
       DoCmd.RunSQL (sql_drop & "[" & nTable & "]")
       DoCmd.SetWarnings True
       'MsgBox ("Apagada a tabela = " & nTable)
       Exit For
   End If
Next i
dbp.Close
Set dbp = Nothing

No Ms Access, trabalhar em cima do MySQL, nada é mais rápido que o SQL Pass-Through.

Por curiosidade, existe algum motivo pelo qual você não está abrindo a conexão no modo SQL Pass-Through?
Passando o SQL diretamente e recebendo os resultados de volta, ou atualizando ?

Eu vejo que você não está a usar consultas Pass-Through e em vez disso, o Access pendura a pensar sobre a consulta e depois passa para a caixa SQL, que por sua vez ela vai pensar sobre a consulta.
Assim tem duas ou mais interpretações da consulta e os resultados são obviamente muito lentos.

Também acredito que agora e como já tem tudo montado, vai ser mais dificil mudar.

Fica aqui a explicação da Microsoft, que por acaso o ano passado eu tiver de recorrer.

Cole no seu browser:
support.microsoft.com/pt-pt/help/303968/how-to-create-an-sql-pass-through-query-in-access

Então JPaulo, não fui eu quem desenvolveu o BD,  estão me solicitando uma ajuda pq o sistema é muito utilizado e está travando demais e lento.

Vou anexar o BD aqui se vc puder dar uma olhada eu agradeço.

Mesmo que a mudança tenha que ser grande eu vou ter que fazer.


Atenciosamente


Janette

Ola;

Não consigo testar, porque essas ligações são exclusivas de vocês, no entanto localmente vejo que varios trechos de código podem melhorar a performance;

Espero que não seja maçador o que vou escrever abiaxo, mas se quer melhorar a performance desse banco, vai ter de arregaçar as mangas.
É um pouco do que aprendi no pequeno curso da Microsoft.

Exemplo:

Em toda a codificação vejo o uso do DBEngine.Workspaces(0).Databases(0), quando estão a trabalhar no banco atual, ok sem problema, mas.

Se usar CurrentDB() que é uma função do Access e é processada pela camada UI (user interface), retorna sempre uma nova referência ao banco de dados atual.
Como resultado, está sempre atualizado com informações atuais, logo mais seguro e eficaz.

Se usar DBEngine.Workspaces(0).Databases(0), ou abreviado dbEngine(0)(0), está a obter um ponteiro para a referência DAO (Data Access Object) mantida pelo JET.
Ao usar essa referência, você está ignorando a camada de UI, o que significa que você não saberá se algo foi feito, a menos que você faça atualização da coleção.
Essa atualização pode ser bastante má em termos de desempenho.

Por outro lado é a contradição, porque o dbEngine(0)(0) é 5 mil vezes mais rápido.

No entanto o dbEngine(0)(0) tem outro problema que você precisa saber.
Em determinadas situações pode não apontar para o mesmo banco de dados, que o CurrentDB() faz.
Tenha em consideração que um deles é o banco de dados atual com o qual a UI está a trabalhar, enquanto o outro é o banco de dados atual com o qual o JET está a trabalhar.

Mas podemos dar a volta á questão e fazer com que o CurrentDB() seja igualmente 5 mil vezes mais rápido, tendo a garantia de que está sempre a trabalhar no banco atual.

Cole num modulo:

Código:


Private xDB As DAO.Database

Public Function xBancoAtual(Optional xRef As Boolean = False) As DAO.Database
 If xDB Is Nothing Or xRef = True Then
    Set xDB = CurrentDb()
 End If
 
 Set xBancoAtual = xDB
End Function


Agora pode usar sem problemas o substituto do CurrentDB() em seus códigos;

Código:


'Dim dbp As Database
Dim i As Integer
Dim sql_del As String
Dim sql_drop As String
'Set dbp = DBEngine.Workspaces(0).Databases(0)
Dim nTable As String
sql_del = "DELETE * FROM "
sql_drop = "DROP TABLE "
nTable = "tab_temppend"

xBancoAtual.TableDefs.Refresh
        
For i = 0 To xBancoAtual.TableDefs.Count - 1
    If nTable = xBancoAtual.TableDefs(i).name Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL (sql_drop & "[" & nTable & "]")
        DoCmd.SetWarnings True
        'MsgBox ("Apagada a tabela = " & nTable)
        Exit For
    End If
Next i
xBancoAtual.Close
Set xBancoAtual = Nothing



Substitua tudo isto;

Código:

Set rs_incons = CurrentDb.OpenRecordset("tab_inconsist")
Set rs_pend = CurrentDb.OpenRecordset("tab_temppend")
Set rs_hotel = CurrentDb.OpenRecordset("tab_hotel")
Set rs_empr = CurrentDb.OpenRecordset("tab_empr")
Set rs_vinc = CurrentDb.OpenRecordset("tab_vinc_func_hotel")
Set rs_acomp = CurrentDb.OpenRecordset("tab_acomp_movto")
Set rs_movto = CurrentDb.OpenRecordset("tab_movto")
Set rs_func = CurrentDb.OpenRecordset("tab_func")
Set rs_corte = CurrentDb.OpenRecordset("tab_dt_vcto_corte_cart")
Set rs_plan = CurrentDb.OpenRecordset("tab_plan_movto")
Set rs_baixados_algar = CurrentDb.OpenRecordset("tab_baixados_algar")
Set rs_alerta = CurrentDb.OpenRecordset("tab_alerta")


Por isto;

Código:


Set rs_incons = xBancoAtual.OpenRecordset("tab_inconsist")
Set rs_pend = xBancoAtual.OpenRecordset("tab_temppend")
Set rs_hotel = xBancoAtual.OpenRecordset("tab_hotel")
Set rs_empr = xBancoAtual.OpenRecordset("tab_empr")
Set rs_vinc = xBancoAtual.OpenRecordset("tab_vinc_func_hotel")
Set rs_acomp = xBancoAtual.OpenRecordset("tab_acomp_movto")
Set rs_movto = xBancoAtual.OpenRecordset("tab_movto")
Set rs_func = xBancoAtual.OpenRecordset("tab_func")
Set rs_corte = xBancoAtual.OpenRecordset("tab_dt_vcto_corte_cart")
Set rs_plan = xBancoAtual.OpenRecordset("tab_plan_movto")
Set rs_baixados_algar = xBancoAtual.OpenRecordset("tab_baixados_algar")
Set rs_alerta = xBancoAtual.OpenRecordset("tab_alerta")


Outra questão, é a abertura de Recordsets sem informar a constante para identificar o tipo de Recodset criado;
DbOpenTable
DbOpenDynaset
DbOpenSnapshot

Você tem CurrentDb.OpenRecordset("tab_temphotel")
Poderia ter CurrentDb.OpenRecordset("tab_temphotel",DbOpenTable) ou DbOpenDynaset dependendo da situação.

E neste caso;
xBancoAtual.OpenRecordset("tab_temphotel",DbOpenTable)

Vejo que usam dentro do VBA do proprio formulario o Forms!Principal!Campo, em vez do Me.Campo ou Me!Campo.
É realmente a mesma coisa, mas é mais lento, porque o Access vai ler primeiro o nome da Coleção (formulario) e depois o nome do Objecto da Coleção (Campo),
quando o Me!Campo é direto ao campo ativo do form em questão.

Pode ainda usar "Analise de Performance", em "Database Tools" no proprio banco para ver o que diz e sugere.

JPaulo,


Nem sei como te agradecer!

O sistema está em outro local, vou hoje à noite na empresa fazer todo o levantamento dessa "lentidão".

Vou sugerir ao cliente suas correções/sugestões e te posiciono.


Mais uma vez! Muito Obrigada!

Oi Janette,

Verifiquei sua aplicação e seguem algumas sugestões:

- Tabelas vinculadas com base de dados MySQL não são seguras, devido à perda do vinculo.

- Se possível, criar uma sub de conexão ADO com o MySQL (e para isto é necessário o drive do MySQL instalado), onde as transações de banco de dados são realizadas através desta conexão.

- As rotinas de select, insert , delete e update, que são executadas via VBA, devem ser implementadas como procedures no MySQL, onde o processamento dessas rotinas é realizado pelo servidor.

Existem mais coisas para melhorar o desempenho.

Espero ter ajudado.

Abs

Oi Alexandre,

Agradeço pelas dicas.

Eu realmente nunca trabalhei com MySql meus bancos sempre foram em SQL.

E esse sistema também não fui eu quem desenvolvi, sou seja, estou meio vendida...rs


Abs

Olá a todos, intressante este tópico.

Janette, como ficou a situação, o retorno é importante, o fórum agradece.

Alexandre Fim, tenho andado a explorar (e estudar) interação do MS Access com BackEnd em MySql (Online).
Na sua mensagem diz "... devem ser implementadas como procedures no MySQL...", concordo.
a) Tem algum exemplo simples que possa partilhar com as operacões CRUD, chamando as "procedures no MySQL"
b) Através do MS Access podemos criar as tabelas via ADO, pergunto essas "procedures no MySQL" podem ser criadas via VBA ou tem que ser criadas .

Abraço a todos