none
Escaso rendimiento en consulta desde Microsoft Access RRS feed

  • Pregunta

  • Hola, 

    Tengo una pequeña aplicación de control horario hecha en MSAccess que trata los datos guardados en una BBDD hospedada en un servidor SQL Server 2008.

    El problema que comento, es que una consulta relativamente simple, definida en la aplicación de Access (un INSERT INTO con unas operaciones con fechas hechas con funciones en VBA llamadas desde la propia consulta de Access) me parece que tiene un rendimiento muy pobre (puede tardar como 15-20 minutos en añadir 500 reguistros a la tabla principal hospedada en el SQL Server. 

    Lo que me despista mas, es que ninguno de los elementos que intervienen en la operación, está saturado ni de lejos. Es decir. El ordenador cliente que ejecuta la consulta en MsAccess, ostenta un uso de CPU inferior al 20% durante la ejecución de la consulta. Al mismo tiempo, el uso de la red, es tambien inferior al 10% tanto en el cliente como en el servidor (red gigabit en ambos). Y el servidor que hospeda el SQL server, tambien muestra unos datos de uso de CPU realmente bajos (por debajo del 5%) y muy pocos cores en uso. 

    A si que... no tengo claro donde está el cuello de botella para que la consulta se realize tan lentamente si como digo, todos los elementos están lejos de su limite (al menos de lo que yo puedo visualizar)

    martes, 10 de diciembre de 2019 21:48

Todas las respuestas

  • Hola nachoBcn:

    500 registros en Sql Server, la insercción debería de durar menos de 1 segundo.

    Aunque tuvieras que hacer 500 conexiones y realizar las mismas 500 veces, debería ser un tiempo de muy pocos segundos (digo muy pocos, por no decirte que contados con los dedos de una mano).

    Yo probaría a asteriscar las conexiones a la base de datos sql y ejecutaría el código de acces y las funciones vba. Si el resultado es bueno, y el problema no esta en esa capa cliente, luego haría lo mismo, con las conexiones al sql server, pero sin hacer las insercciones.

    Luego haría las 500 insercciones, pero solo en el sql.

    Intentando realizar cada cosa, por separado para poder saber quien es el que demora.

    martes, 10 de diciembre de 2019 23:36
  • Gracias por la respuesta. 

    Lo de 'asteriscar' las conexiones... que quieres decir? Tengo conocimientos de administración de SQL, pero no soy un experto.

    miércoles, 11 de diciembre de 2019 6:09
  • Hola nachoBcn:

    En tú código, que llamas al Sql, comentar las conexiones, para que no se establezcan.

    Es posible, que el cuello, esté en la gestión de las conexiones, pero eso solo lo sabes, si lo pruebas.

    Comentarios

    https://riptutorial.com/es/vba/example/6732/apostrofe-comentarios

    miércoles, 11 de diciembre de 2019 6:26
  • Hola nachoBcn:

    En tú código, que llamas al Sql, comentar las conexiones, para que no se establezcan.

    Es posible, que el cuello, esté en la gestión de las conexiones, pero eso solo lo sabes, si lo pruebas.

    Comentarios

    https://riptutorial.com/es/vba/example/6732/apostrofe-comentarios

    Gracias por la respuesta.

    Efectivamente la consulta INSERT INTO lleva unos 10 campos calculados que llaman cada uno de ellos a funciones VBA en las cuales se hace una SELECT a la misma BBDD SQL SERVER. 

    Estoy convencido que si, para probar, comento la consulta SQL de cada una de las funciones, y devuelvo un valor constante... la velocidad subirá exponencialmente. Pero lo que me resulta incompresnsible es que las consultas Select que se realizan en cada funcion, son selects muy sencillas y que deberían ser rapidisimas de ejecutar. 

    Por otro lado, tambien he mirado de solventarlo con SUB SELECTS. Que sería la manera correcta de hacerlo. Pero claro, es bastane mas facil (al menos para mi) hacerlo con funciones y abriendo un recordset en VBA y usando las expresiones regulares del Acces que no hacerlo en T-SQL puro, que estoy muy pez.

    Para que te hagas una idea, te pongo el codigo de una de las funciones VBA que devuelve el valor para uno de los campos del INSERT INTO:

    Function HoraEntradaCalc(Dia As String, Usuario As String) As Date

    Dim rst As New Recordset
    On Error Resume Next

        rst.Open "SELECT Min(Format(Hour([TransactionTime]) & ':' & Minute([TransactionTime]),'hh:mm')) AS hora " & _
                "FROM dbo_cMarcajes " & _
                "GROUP BY dbo_cMarcajes.UserID, Format(Day([TransactionTime]) & '/' & Month([TransactionTime]) & '/' & Year([TransactionTime]),'dd/mm/yyyy') " & _
                "HAVING (((dbo_cMarcajes.UserID)='" & Usuario & "') AND ((Format(Day([TransactionTime]) & '/' & Month([TransactionTime]) & '/' & Year([TransactionTime]),'dd/mm/yyyy'))='" & Dia & "'))", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
                
        

        If Not rst.EOF Then
            HoraEntradaCalc = rst("hora")
        End If
        
        rst.Close
        Set rst = Nothing
        
    End Function

    domingo, 15 de diciembre de 2019 18:22
  • Hola nachoBcn:

    Bueno como ya tenemos claro, que el diálogo de ese modo, para Sql y Access no es muy funcional, y para confirmarlo, puedes hacer una sola insercción de una fila, sin cambiar tú código y examinar el numero de consultas que se producen en el Sql server con el profiler.

    Sql server simple

    https://sqaletec.wordpress.com/2016/11/29/sql-server-como-usar-el-profiler-para-monitorear-sps-o-transacciones/

    Yo me supongo que realizará muchas conexiones, para una sola fila. Al menos tantas como funciones tenga que llamar para resolver la fila.

    Y si es correcto, solo tienes dos opciones. O consigues todos los valores de la fila en Access y le insertas con los valores, ya "masticados", o lo haces en Sql server.

    domingo, 15 de diciembre de 2019 18:49
  • Gracias por la respuesta.

    Lo miro lo antes posible. Pero cada vez tengo mas claro que lo que me toca es 'traducir'.

    :-)

    domingo, 15 de diciembre de 2019 18:53