** NORMAS DEL FORO **
Inicio del foro Inicio del foro > Access y VBA > Access y Otros sistemas
  Mensajes nuevos Mensajes nuevos RSS - actualizar sql lento
  Preguntas frecuentes Preguntas frecuentes  Buscar en el foro   Eventos   Registro Registro  Iniciar sesion Iniciar sesion

Tema cerradoactualizar sql lento

 Responder Responder
Autor
Mensaje
bugy Ver desplegable
Habitual
Habitual


Unido: 19/Febrero/2013
Localización: España
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Tema: actualizar sql lento
    Enviado: 15/Noviembre/2019 a las 11:16
Buenas
Tengo una hoja de excel (vinculada al access), que tiene que actualizar una tabla del sql server (que tambien tengo vinculada al access)
Tanto el excel como la tabla tienen unos 10.000 registros.
La tabla tiene un campo de clave principal que es el NIF (sin duplicados)
Lo que quiero hacer es actualizar todos los campos de la tabla, a partir del excel , sólo cuando el NIF sea nuevo.
Ahora lo tengo hecho así, pero me resulta muy lento

Public Sub copiarexcel()
    Dim SQL_Text As String
    SQL_Text = "INSERT INTO dbo_tablasql(NIF, Nombre, Asunto, Fecha)" & _
    "SELECT NIF, Nombre, Asunto, Fecha FROM listadoexcel WHERE NIF NOT IN (SELECT NIF FROM dbo_tablasql);"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (SQL_Text)
End Sub

Alguna idea de como mejorarlo?
Gracias

Arriba
raipon Ver desplegable
Moderador
Moderador


Unido: 10/Diciembre/2004
Localización: Desconocida
Estado: Sin conexión
Puntos: 4044
Enlace directo a este mensaje Enviado: 23/Noviembre/2019 a las 00:01
Hola, utilizar una subconsulta en la clausula Where siempre será lento. Emplea el asistente de no coincidentes para entender como relacionar ambas tablas en la clausula From y obtener los registros que te faltan en la que está alojada en sql server.

Con lo anterior ya notarás una gran mejora de rendimiento, pero si quieres optimizar mas, piensa que ahora tienes alojadas las tablas en dos sitios, pero que utlizas un tercero (Access) para procesar la consulta.
Si el archivo Excel y el servidor sql comparten una misma red, te recomendaria crear en Sql Server un servidor vinculado al libro de Excel y desarrollar un store procedure en el primero para actualizar la tabla.

A modo de ejemplo este seria el script para crear el servidor vinculado (Versión Excel 2003, nombre del servidor vinculado: EXCEL_CUSTOMERS, la hoja con datos se llama Customers):

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL_CUSTOMERS', @srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'C:\ruta del archivo\Customers.xls', @provstr=N'Excel 8.0'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCEL_CUSTOMERS',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

-------------------------------------------------------------------------------------------------------------------

Después de ejecutar el script anterior, dispondrás de una conexión estable al libro de Excel (el servidor vinculado EXCEL_CUSTOMERS) que podrás usar en una consulta como si se tratara de una tabla o una vista.
El código sql para actualizar la tabla (Customers) con los registros nuevos del Excel:

Insert Into [dbo].[Customers]
Select T.* 
From [EXCEL_CUSTOMERS]...Customers$ T
         Left Join
 [dbo].[Customers] On [dbo].[Customers].[CustomerID]=T.[CustomerID]
Where [dbo].[Customers].[CustomerID] Is Null

Los tres puntos entre el servidor vinculado y el nombre la hoja, no son un capricho, son necesarios, así como el simbolo $ para cerrar el nombre de la hoja.

Saludos.






Editado por raipon - 23/Noviembre/2019 a las 00:09
Ramon desde Terrassa.

Mi blog
Arriba
bugy Ver desplegable
Habitual
Habitual


Unido: 19/Febrero/2013
Localización: España
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Enviado: 23/Noviembre/2019 a las 20:18
Buenas Ramon
La idea me parece muy buena. 
Que pasa si no tengo el servidor sql server y las hojas de excel no estan en el mismo servidor?
Gracias
Jordi
Arriba
raipon Ver desplegable
Moderador
Moderador


Unido: 10/Diciembre/2004
Localización: Desconocida
Estado: Sin conexión
Puntos: 4044
Enlace directo a este mensaje Enviado: 24/Noviembre/2019 a las 20:18
Hola, no pasa nada. Solo tienen que estar en la misma red, que entiendo que es el caso.

De todas formas he montado un escenario parecido al descrito: 
Una tabla en sql server con +- 65.000 registros y una hoja con un número parecido de datos (la hoja es visible en Sql Server a través de un servidor vinculado).
El proceso lo ejecuta un procedimiento almacenado (con la cosulta de inserción que aparece en este mismo hilo) y la verdad es que el rendimiento es igual de bueno que si el proceso lo ejecuta Access: dos tabla vinculadas: una al Excel y la otra a Sql Server y un insert con el mismo esquema que el anterior.

En definitiva, que la lentitud que sufres es debido a como está montada la consulta original. Como regla general para este tipo de situaciones ten en cuenta esta comparativa:

Rendimiento pésimo:
Select ...
From TablaOrigen
Where MiCampo Not In (Select MiCampo ... From TablaDestino ...)

Mejora la velocidad:
Select ...
From TablaOrigen
Where Not Exists (Select MiCampo From TablaDestino As T Where T.Id = TablaOrigen.Id)

Óptimo:
Select ...
From TablaOrigen
Left Join
TablaDestino On TablaOrigen.Id = TablaDestino.Id
Where TablaDestino.Id Is Null

Saludos.
Ramon desde Terrassa.

Mi blog
Arriba
bugy Ver desplegable
Habitual
Habitual


Unido: 19/Febrero/2013
Localización: España
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Enviado: 24/Noviembre/2019 a las 22:04
Buenas Ramon
Antes que nada agradecerte las respuestas.
He intentado el código óptimo de la siguiente manera.
Public Sub copiarexcel()
    Dim SQL_Text As String
    SQL_Text = "INSERT INTO dbo_tablasql(NIF, Nombre, Asunto, Fecha)" & _
    "SELECT NIF, Nombre, Asunto, Fecha FROM listadoexcel LEFT JOIN " & _
     "dbo_tablasql ON listadoexcel.NIF = dbo_tablasql.NIF" & _
      "WHERE dbo_tablasql.NIF IS NULL;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (SQL_Text)
End Sub

Sabes donde puede estar el error?
Muchas gracias
Jordi
Arriba
lbauluz Ver desplegable
Administrador
Administrador
Avatar

Unido: 29/Marzo/2005
Localización: La Gloria
Estado: Sin conexión
Puntos: 3849
Enlace directo a este mensaje Enviado: 25/Noviembre/2019 a las 00:51
?es esa la query tal cual?

Te faltan espacios, 
Fecha)" & _ por Fecha) " & _
dbo_tablasql.NIF" por dbo_tablasql.NIF "

Luis
El Búho es un pajarraco
Arriba
bugy Ver desplegable
Habitual
Habitual


Unido: 19/Febrero/2013
Localización: España
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Enviado: 25/Noviembre/2019 a las 16:42
Gracias
Arriba
bugy Ver desplegable
Habitual
Habitual


Unido: 19/Febrero/2013
Localización: España
Estado: Sin conexión
Puntos: 192
Enlace directo a este mensaje Enviado: 21/Diciembre/2019 a las 11:10
Buenas
Se puede cerrar el tema
Gracias
Arriba
 Responder Responder
  Compartir tema   

Ir al foro Permisos de foro Ver desplegable