Imprimir página | Cerrar ventana

actualizar sql lento

Impreso de: Foro de Access y VBA
Categoría: Access y VBA
Nombre del foro: Access y Otros sistemas
Descripción del foro: Proyectos ADP & Sql Server, Mysql& Access, MSDE
URL: http://www.mvp-access.com/foro/forum_posts.asp?TID=84828
Fecha de impresión: 19/Abril/2024 a las 20:23


Tema: actualizar sql lento
Publicado por: bugy
Asunto: actualizar sql lento
Fecha de publicación: 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


Respuestas:
Publicado por: raipon
Fecha de publicación: 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.






-------------
Ramon desde Terrassa.

http://www.sqlraipon.blogspot.com/" rel="nofollow - Mi blog

Publicado por: bugy
Fecha de publicación: 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

Publicado por: raipon
Fecha de publicación: 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.

http://www.sqlraipon.blogspot.com/" rel="nofollow - Mi blog

Publicado por: bugy
Fecha de publicación: 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

Publicado por: lbauluz
Fecha de publicación: 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

Publicado por: bugy
Fecha de publicación: 25/Noviembre/2019 a las 16:42
Gracias

Publicado por: bugy
Fecha de publicación: 21/Diciembre/2019 a las 11:10
Buenas
Se puede cerrar el tema
Gracias


Imprimir página | Cerrar ventana