Lentitud en consulta Access vba (ADO)
He creado una consulta para realizar la formación de trabajadores y que me facilita cuando realizaron la última formación. Hasta aquí todo correcto pero he querido ir más allá y he incorporado un campo para consultar, en base a una fecha prevista de formación y que me muestre el turno del trabajador. El sistema funciona, lento, pero funciona y me lo muestra el turno de ese día pero cada vez que bajo el scroll parece cómo sí me volviese a realizar la búsqueda. He intentado cambiar el tipo de recorset, etc pero no veo donde puede fallar.
En la consulta de agrupación le he añadido el campo siguiente, con total tipo Expresión y que llama a la función Turno_por_fecha pasándole el dni del trabajador (la fecha de formació la coge del formulario):
Turno: Turno_por_fecha([Plantilla].[dni])
La función de consulta a la otra BD llamada Turno_por_fecha es la siguiente y es lo que se ejecuta por cada registro para recibir el turno:
Public Function Turno_por_fecha(dni As Variant) As Variant
Dim dnix As Variant
Dim fecha As Date
Dim fechax As Variant
Dim rs2 As dao.Recordset
Dim strBDRemota As String
Dim dbx As dao.Database
Dim stAppName As String
fechax = Forms!Formacion.[fecha_por_turnos]
If (IsNull(fechax)) Then
Turno_por_fecha= "Sense dades"
Else
fecha = Format(fechax, "mm/dd/yyyy")
stAppName = Dir("U:\Cuadrantes.accdb")
Set dbx = DBEngine.OpenDatabase(stAppName)
dnix = dni
strSQL = "SELECT Turno FROM Turnos " & _
" WHERE dni='" & dnix & "' and Fecha=#" & fecha & "#"
Set rs2 = dbx.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
If rs2.RecordCount <> 0 Then
Turno_por_fecha= rs2!Turno
Else
Turno_por_fecha= "Sense dades"
End If
rs2.Close
Set rs2 = Nothing
dbx.Close
End If
End Function
No se si hay manera de optimizar el proceso. ¿Cómo lo véis?
2 Respuestas
Siga estas recomendaciones de Microsoft Cómo optimizar Microsoft Access cuando se utilizan orígenes de datos ODBC - Soporte técnico de Microsoft
En su caso particular ni utilizando ADO va a ganar velocidad, menos con la solución que le dan (DAO), trate de vincular la tabla Turno de la base de datos cuadrantes. Accdb, esto es más rápido que abrir un recordset, porque según su consulta por cada registro se llamará la función. Si es lenta a nivel local, qué tal si fuese para consultar otra base de datos de un servidor, un desastre.
Personalmente utilizo recordset desconectados cuando consulto al servidor.
Para agilizar la consulta debe vincular la tabla. Se require ADO si la tabla esta en un servidor, no obstante influye el hardware de su pc. Si tarda 7 u 8 segundos es mucho, que tal con 500 mil registros o más. Sin conocer como está diseñado el subformulario no puedo dar más detalles. Si quiere envíeme las bases de datos a [email protected] y trato de colaborarle.
Solo veo DAO (no ADO) y si se utiliza esa función de forma repetitiva, sera bastante lenta.
Yo declararía el recordset al principio del modulo y resumiría la función un poco, algo se ganara si no se están declarando y borrando variables, bases de datos y recordset de forma reiterada
Option Compare Database Option Explicit Private rs2 As DAO.Recordset
La funcion modificada
Public Function Turno_por_fecha(dnix) As Variant
Turno_por_fecha = "Sense dades"
If Dir("U:\Cuadrantes.accdb") = "" Then Exit Function
If Not IsDate(Forms.Formacion.fecha_por_turnos) Then Exit Function
Set rs2 = CurrentDb.OpenRecordset("SELECT Turno FROM Turnos In 'U:\Cuadrantes.accdb' WHERE dni='" & dnix & "' And Fecha= " & CDbl(CDate(Forms.Formacion.fecha_por_turnos)), , dbReadOnly)
If rs2.RecordCount <> 0 Then Turno_por_fecha = rs2!Turno
rs2.Close
End Function.- Le damos una respuesta por defecto en caso de error
.- Si no existe la base de datos externa, abortamos
.- Si no hay un valor de fecha, abortamos
.- Asignamos la SQL al recordset (que esta declarado en la cabecera del modulo)
.- Si hay por lo menos un dato lo asignamos a la funcion como valor de retorno
,-Cerramos el recordset, no es indispensable (era 'solo lectura', un poco de jabón no hace daño)
Buenas tardes, he mejorado el código según tus premisas y continúa funcionando igual. Lo que no acabo de entender es que lo carga en aproximadamente 7 u 8 segundos en el subformulario, lo cual es aceptable dado el número de registros pero cuando bajo la barra desplegable es cuando se queda pensando otros 7 u 8 segundos para cargarlo. Porqué? Si la consulta ya está hecha y cargada?¿?¿?!!! Mi no entender...
Con la metodología que aplicas (y que he respetado) no se cargan los datos como un conjunto, se cargan de uno en uno y (antes) cada vez tenía que crear el entorno, se supone que si se eliminan acciones se aceleran los procesos.
¿En qué medida se aceleran? ... Depende del entorno (maquina, red ... etc.) y solo se puede verificar el aumento de velocidad si (utilizando el reloj en tiempo real) se toma el tiempo al inicio y al final, básicamente lo que se ahorra es el tiempo de limpiar el entorno (crear variables, recordset y conexiones a datos externos).
Los segundos que tarde en cargar es el tiempo en el que localiza los datos a mostrar, una vez que se modifica algo, para refrescar la presentación (tengamos en cuenta que trabaja en tiempo real) se repite el proceso y una vez más y volverá a ser necesario el mismo tiempo para cargar el mismo número de datos.
Si se cambia la metodología de acceso (abrir un canal de comunicación al conjunto de datos externo y mantenerlo abierto mientras ese formulario esta activo) se ganaría algo más de velocidad (casi similar a tener la tabla vinculada).
De hecho, lo que se está emulando con el código original (o se intenta emular) es la función DLookup que cuando es repetitiva comienza a mostrar su debilidad.
¿Cómo se obtendría el mejor rendimiento?, pues con la tabla en modo local (se puede cargar al inicio, cuando se abre el formulario) y utilizar la potencia bruta de DAO, (datos indexados y su metodología SEEK) contra la que no hay aplicación externa que la supere (es el ADN de Access, basado en el corazón del sistema operativo NT y su gestión de los directorios de los HD y asimilados).
Disculpa y, en lugar de cargar los datos de uno en uno, como se haría para cargarlos "en conjunto"?
De igual forma que se hace con la definición de objetos: optimizando su uso.
Se definen las variables indispensables a nivel local, en la apertura del formulario se asigna el origen de datos del Recordset (sea la tabla o una consulta sobre ella)
En el cambio de registro se utiliza FindFirst para localizar el dato, si se desea mayor velocidad, se crea en la tabla un índice y para localizar el elemento del conjunto se utiliza SEEK .
El Recordset permanecerá abierto durante todo el tiempo que este abierto el formulario y se cerrara al cerrarlo.
Si con este método no se logra eficiencia, me plantearía una profunda revisión del esquema base del diseño actual de las tablas y sus relaciones.
Es un clásico que el diseño original no se contemplasen posibilidades que al implementarlas con posterioridad no permitan lograr la eficiencia indispensable (por eso existen las versiones 2.0)