Jump to content
Manuel Gómez

Ralentización durante la actualización de datos desde SQL

Recommended Posts

Buenas.

Tengo una conexión a un SQL que descarga unas 150.000 filas a una tabla de Excel. Al actualizar datos los descarga y coloca en menos de un segundo. 

Después he añadido una segunda hoja en el mismo libro para dar formato y colocar los datos como necesita el departamento. Para hacerlo he utilizado indice-coincidir en cada celda (unas 75.000 celdas).

El problema es que cuando inicio la actualización de datos desde SQL continuamente se estan actualizando todas las casillas indice-coincidir en lugar de esperar a que finalice la descarga de datos.

He probado:

Application.Calculation = False
Application.EnableEvents = False
Application.ScreenUpdating = False
 
Antes de actualizar los datos de la conexión,  pero las fórmulas con indice-coincidir siguen actualizándose durante la descarga, lo que ralentiza enormemente la actualización de datos. 
¿Cómo podría evitar que se actualizaran las celdas indice-coincidir durante la actualización de la conexión?
 
Gracias  y saludos 

Share this post


Link to post
Share on other sites
Guest Cacho R

Hola! Manuel. Sube al Foro una muestra reducida de tu libro. Por ejemplo:

- Una primer hoja con no más de 100 filas de datos (en lugar de las 150 mil). Por cierto: en sí los datos no interesan.
- Y una segunda hoja con no más de 20 filas con fórmulas (en lugar de las 75 mil).

Lo que más interesa es ver TODAS las columnas que empleas, las fórmulas y la disposición de las fórmulas en tu segunda hoja.

Saludos, Cacho R.

Edited by Cacho R

Share this post


Link to post
Share on other sites

Buenos días Cacho R.

Gracias por tu respuesta. Tienes razón, una imagen vale más que mil palabras. Adjunto un excel reducido.

El problema lo tengo al actualizar los datos que importo desde la conexión al SQL ya sea utilizando el menú de Excel como una subrutina de VBA.

Si elimino la hoja "Informe" donde están todas las fórmulas "Indice-Coincidir" la actualización de datos se hace inmediatamente.

 

Datos 2018 v0.9.xlsm

Share this post


Link to post
Share on other sites

Buenos días sailepaty.

Muchas gracias por tu respuesta.

La conexión la tengo definida en el libro de Excel (no la creo por VBA) y tengo desactivadas las opciones:

  • Habilitar actualización en segundo plano.
  • Actualizar cada ..
  • Actualizar al abrir el archivo.

He intentado añadir la opción que me has dicho en la cadena de conexión, pero no me lo guarda.

He puesto un breakpoint antes de realizar la conexión a la base de datos y en las inspecciones he revisado el parámetro

ThisWorkbook.Connections("NombreDeLaConexion").ODBCConnection.BackgroundQuery
Que estaba en False.

Se te ocurre que más podría probar?

Share this post


Link to post
Share on other sites
Guest Cacho R
Hace 1 hora, Manuel Gómez dijo:

Si elimino la hoja "Informe" donde están todas las fórmulas "Indice-Coincidir" la actualización de datos se hace inmediatamente.

¡Ésa es la idea!... O sea:

Reemplaza:

    ActiveWorkbook.Connections("Conexion Infor").Refresh

por:

    With Sheets("Informe")
      .Range(.[a4].End(xlToRight), .[a2].End(xlDown)).ClearContents
      ActiveWorkbook.Connections("Conexion Infor").Refresh
      .Range(.[a3].End(xlToRight), .[a2].End(xlDown)).FillDown
    End With

En resumen: al momento de actualizar la conexión -solamente- dejas las fórmulas de la fila 3. Tras la actualización vuelves a poner las fórmulas tomando a las de la fila 3 como modelo.

Share this post


Link to post
Share on other sites
Hace 18 horas, Cacho R dijo:

¡Ésa es la idea!... O sea:

Reemplaza:

    ActiveWorkbook.Connections("Conexion Infor").Refresh

por:

    With Sheets("Informe")
      .Range(.[a4].End(xlToRight), .[a2].End(xlDown)).ClearContents
      ActiveWorkbook.Connections("Conexion Infor").Refresh
      .Range(.[a3].End(xlToRight), .[a2].End(xlDown)).FillDown
    End With

En resumen: al momento de actualizar la conexión -solamente- dejas las fórmulas de la fila 3. Tras la actualización vuelves a poner las fórmulas tomando a las de la fila 3 como modelo.

Buenas Cacho R

Muchas gracias.

Lo he probado y, aunque funciona bien durante el refresh de la conexión, al hacer el FillDown tarda 5 minutos en actualizarse (además de lanzar un fallo de memoria).

¿Son 75.000 celdas con la fórmula INDICE-COINCIDIR cada una de ellas. Es posible que sean demasiadas celdas?

Muchas gracias por tu tiempo.

Saludos.

Share this post


Link to post
Share on other sites
Hace 14 horas, sailepaty dijo:

Otra opción es Power Query <<revisa el adjunto>>. Podrías incluso conectar tu consulta de SQL directamente a PQ y solo mostrar el informe en tu hoja de Excel.

 

Saludos

 

Datos 2018.xlsm

Buenos días Sailepaty.

Me encanta la solución. No había tenido Office 2016 hasta ahora y ni si quiera me había planteado Power Query.

Viendo su potencial tengo mucho que hacer para revisar trabajos anteriores cuya solución no era eficiente.

¿Hay alguna manera de marcar una respuesta como correcta a parte de los iconos de reacción?

Muchas gracias por tu tiempo.

Saludos.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.



×
×
  • Create New...

Important Information

Privacy Policy


CTA Templates.png