Excel para Banco de dados está muito lento

Question

Ola amigos

Tenho a seguinte situação:

De hora em hora recebo vários arquivos contendo a atualização do status de um determinado serviço que presto.

Mas é muito demorado. Para fazer 20 planilhas demora aproximadamente 1 hora. Ou seja, quando estamos terminando um lote, já chega outro.

Excel (Recebido a cada 1 Hora com o status alterado)				MySql (Banco de Dados Oficial)
TrackingNumber	Nome	Endereço	Status				TrackingNumber	Nome	Endereço	Status
BR000001	FULANO01	ENDEREDOFULANO01	DELIVERING				BR000001	FULANO01	ENDEREDOFULANO01	DELIVERING
BR000002	FULANO02	ENDEREDOFULANO02	DELIVERING				BR000002	FULANO02	ENDEREDOFULANO02	DELIVERING
BR000003	FULANO03	ENDEREDOFULANO03	DELIVERED				BR000003	FULANO03	ENDEREDOFULANO03	DELIVERING
BR000004	FULANO04	ENDEREDOFULANO04	DELIVERED				BR000004	FULANO04	ENDEREDOFULANO04	DELIVERING
BR000005	FULANO05	ENDEREDOFULANO05	ONHOLD				BR000005	FULANO05	ENDEREDOFULANO05	DELIVERING
BR000006	FULANO06	ENDEREDOFULANO06	DELIVERED				BR000006	FULANO06	ENDEREDOFULANO06	DELIVERING
BR000007	FULANO07	ENDEREDOFULANO07	DELIVERED				BR000007	FULANO07	ENDEREDOFULANO07	DELIVERING
BR000008	FULANO08	ENDEREDOFULANO08	DELIVERED				BR000008	FULANO08	ENDEREDOFULANO08	DELIVERING
BR000009	FULANO09	ENDEREDOFULANO09	DELIVERED				BR000009	FULANO09	ENDEREDOFULANO09	DELIVERING
BR000010	FULANO10	ENDEREDOFULANO10	DELIVERING				BR000010	FULANO10	ENDEREDOFULANO10	DELIVERING
BR000011	FULANO11	ENDEREDOFULANO11	DELIVERING				BR000011	FULANO11	ENDEREDOFULANO11	DELIVERING
BR000012	FULANO12	ENDEREDOFULANO12	ONHOLD				BR000012	FULANO12	ENDEREDOFULANO12	DELIVERING
BR000013	FULANO13	ENDEREDOFULANO13	ONHOLD				BR000013	FULANO13	ENDEREDOFULANO13	DELIVERING
BR000014	FULANO14	ENDEREDOFULANO14	ONHOLD				BR000014	FULANO14	ENDEREDOFULANO14	DELIVERING
BR000015	FULANO15	ENDEREDOFULANO15	DELIVERING				BR000015	FULANO15	ENDEREDOFULANO15	DELIVERING

Essa script (abaixo) funciona perfeitamente, porém é muito demorada pelo fato dele fazer a atualização linha a linha.

Do While ln <= ultcel.Row

status_PedidoEx = UCase(xls.Cells(ln, col + 4))

filtra_PedidoEx = UCase(xls.Cells(ln, col + 1))

mysql3 = "UPDATE tbl_monitoramento_LM SET status_pedido =" & "'" & status_PedidoEx & "'" & " WHERE pedido_tr = " & "'" & filtra_PedidoEx & "'" & ";"

rs.Open mysql3, conn

ln = ln + 1

Loop

É possível fazer essa atualização de uma unica vez, sem ter que ser linha a linha?

Como por exemplo, quando fazemos uma atualização no proprio banco de dados MySql com 400 mil linhas, demora aproximadamente 5 segundos para atualizar.

Isso porque ela faz tudo de uma unica vez e não linha a linha.

Answer 1

Se preferir integrar o código SQL que atualiza várias linhas de uma tabela ao mesmo tempo, você pode substituir o loop While existente pelo seguinte código:

Dim pedido_tr As StringDim status_pedido As StringDim filtra_PedidoEx As StringDim status_PedidoEx As StringDim updateQuery As StringFor ln = 2 To ultcel.Row pedido_tr = UCase(xls.Cells(ln, col + 1)) status_pedido = UCase(xls.Cells(ln, col + 4)) filtra_PedidoEx = filtra_PedidoEx & "'" & pedido_tr & "', " status_PedidoEx = status_PedidoEx & "'" & status_pedido & "', "Next ln' Remover a vírgula extra no final das strings de filtro e de statusfiltra_PedidoEx = Left(filtra_PedidoEx, Len(filtra_PedidoEx) - 2)status_PedidoEx = Left(status_PedidoEx, Len(status_PedidoEx) - 2)' Construir a consulta de atualizaçãoupdateQuery = "UPDATE tbl_monitoramento_LM SET status_pedido = CASE pedido_tr "updateQuery = updateQuery & "WHEN " & filtra_PedidoEx & " THEN " & status_PedidoEx & " END"' Executar a consultars.Open updateQuery, conn

Neste exemplo, a instrução SQL de atualização em massa é construída dinamicamente dentro do loop For que percorre as linhas da planilha do Excel. Em vez de executar uma consulta de atualização para cada linha, a instrução SQL é construída para atualizar todas as linhas em uma única consulta.

A cláusula CASE é usada para atualizar cada linha com o status correto com base no valor da coluna "pedido_tr". A consulta de atualização em massa é executada apenas uma vez após o loop For.

Answer 2

Olá

Bem vindo à Comunidade Microsoft 365 e Office

Tudo bem, Paulo? Aqui está um exemplo de como atualizar várias linhas de uma tabela ao mesmo tempo usando a instrução SQL UPDATE com uma cláusula WHERE:

mysql3 = "UPDATE tbl_monitoramento_LM SET status_pedido = '" & status_PedidoEx & "' WHERE pedido_tr IN (" & filtra_PedidoEx & ");"rs.Open mysql3, conn

Neste exemplo, a cláusula WHERE é usada para especificar quais linhas da tabela devem ser atualizadas com base nos valores da coluna "pedido_tr" que correspondem aos valores da variável "filtra_PedidoEx". A cláusula "IN" permite que você especifique uma lista de valores separados por vírgulas, o que significa que você pode atualizar várias linhas de uma só vez em vez de uma de cada vez.

Lembre-se de testar a instrução SQL antes de executá-la em produção, para garantir que ela esteja funcionando corretamente e atualizando as linhas corretas na tabela.