Rendimiento de Access contra backend Access en servidor de archivos remoto. Cuarta parte.

En este artículo finalizamos la parte de consultas de selección de nuestra serie de artículos sobre Rendimiento de Access como Backend de servidor que están incluidos en la serie de artículos sobre Rendimiento de Access contra bases de datos de servidor.

Analizaremos consultas que carguen lo máximo posible el SGBD del servidor (Access en este caso), intentaremos mejorarlas y sacaremos conclusiones importantes a mi juicio.

Las pruebas se realizarán de una forma similar a los artículos anteriores, esta vez solo con índices (ya hemos demostrado que funciona mucho mejor para criterios de filtrado), contra base de datos con usuarios conectados, contra la base de datos simulada con tablas de 67.000, 59.000, 3.200 y 9.000 y contra la base de datos simulada con tablas de 500.000 registros. Recomiendo la lectura de los anteriores artículos de la serie, pero para los que no queráis, vuelvo a describir el escenario de las pruebas:

• BackEnd: Base de datos Access alojada en servidor de archivos. 20 tablas correctamente relaciones y normalizadas siguiendo el modelo relacional.
• FrontEnd: Aplicación Access y VBA.
• Red: Servidor de archivos en otra provincia, red corporativa dedicada.
• Tablas de prueba: Se harán pruebas con tablas con índices en los campos a utilizar como criterio. Se utilizarán 4 tablas con 67.000, 59.000, 3.200 y 9.000 registros respectivamente. Edito: Segunda prueba. Tablas de 500.000 registros
• Consultas: Varios tipos. Devuelven valores entre 41 registros y 372.492 registros (si, habéis leído bien). Edito: Segunda prueba. Consultas con 2.000.000 de registros
• Pruebas: Se realizarán las pruebas en tandas de 100, utilizando bucles para ello. Para que los resultados sean más fiables, se realizará cada tipo de prueba seguido del siguiente, evitando así que influya el estado de la red
• Concurrencia: Como he comentado, las pruebas principales se realizaron de noche, por lo que no hay usuarios conectados. Gracias a José Antonio Pérez me di cuenta que puede ser importante realizar las mismas pruebas con los usuarios conectados a la base de datos, así que las he realizado desde el equipo de un compañero.

Para los que estéis interesados, os paso los enlaces a los anteriores artículos de la serie, que os harán entender mejor este artículo. En ellos explico los tipos de consultas, a qué llamo emular Pass Through y las conclusiones sacadas en consultas de selección más sencillas:

• Rendimiento de Access contra backend Access en servidor de archivos remoto. Primera parte.
• Rendimiento de Access contra backend Access en servidor de archivos remoto. Segunda parte.
• Rendimiento de Access contra backend Access en servidor de archivos remoto. Tercera parte.
• Rendimiento de Access contra backend Access en servidor de archivos remoto. Aclaración.

En este artículo añadimos las consultas que he visto que más le cuesta procesar a Access, consultas que muestran valores únicos, es decir, consultas con DISTINCT. Como vimos en el artículo anterior, las consultas con criterios utilizando comodines (like *criterio) también provocan tiempos de espera largos, pero nada comparable a lo que me he encontrado con este tipo de selecciones.

Vamos a empezar a mostrar los resultados de las pruebas. He separado en 3 los tipos de pruebas:

Prueba 1: 4 tablas con 67.000, 59.000, 3.200 y 9.000. Devuelven valores entre 41 registros y 372.492 registros. Sin concurrencia.

Prueba 2: 4 tablas con 500.000, 300.000, 3.200 y 9.000. Devuelven valores entre 41 registros y 2.584.580 registros. Sin concurrencia.

Prueba 3: 4 tablas con 11.000, 5.000, 3.200 y 9.000. Devuelven valores entre 41 registros y 17.000 registros (lo siento, no he podido realizarlas con más registros, es «fuego real»…). 30 usuarios conectados.

Prueba 1

Antes de empezar con la prueba, voy a postear un recordatorio de resultados de las pruebas anteriores «optimizadas». Es decir, voy a postear las consultas que mejores resultados dieron para los valores deseados.

Si habéis leído los artículos, recordaréis 2 tipos de consultas, una contra tabla única («tabla») y otra contra varias tablas relacionadas con LEFT JOIN («larga») que hacía a Access sufrir bastante.

Los mejores resultados se obtenían abriendo la base de datos directamente desde VBA con ADO y con DAO (algo mejores con DAO):

Prueba con DAO

Mejores resultados DAO.

Prueba con ADO

Mejores resultados con ADO.

Y ahora vamos con las pruebas más interesantes que he realizado desde que empezamos a medir el rendimiento de Access, las pruebas con consultas de selección con valores únicos, es decir, con DISTINCT:

Pruebas con el generador de consultas de Access

Generador de consultas Access valores únicos.

Como podéis ver, los resultados son interesantísimos… Aquí es cuando de verdad vemos lo importante que es optimizar las consultas. Podemos sacar varias conclusiones:

• Veremos lo que sucede con SGBD de servidor, pero aquí vemos que para consultas de selección con DISTINCT, los tiempos se disparan cuando hay mucha diferencia entre la consulta inicial y la de valores únicos. No creo que dependa de los datos enviados por la red ya que hemos visto que enviando muchos más datos, los tiempos son mucho menores. Es un tema de carga de proceso del motor de base de datos.
• La misma consulta filtrando los resultados a mostrar, divide los resultados por 40. Esto es porque primero filtra y después evalúa el DISTINCT.

Pruebas con DAO

Pruebas con DAO valores únicos.

Pruebas con ADO

Aunque ponga DAO, realmente son pruebas con ADO.

Pruebas con ADO valores únicos.

Como podéis ver, los tiempos cuando descartamos el generador de consultas de Access se dividen por 4. Siguen siendo bastante grandes pero hasta no compararlos con el resto de SGBD no sabremos si es por carencias de Access o simplemente porque son tiempos razonables.

Prueba 2

Igual que en la primera prueba, empiezo con los mejores resultados obtenidos en las pruebas con consultas más simples:

Prueba con DAO

Mejores resultados tablas de 500.000 registros DAO.

Prueba con ADO

Mejores resultados tablas de 500.000 registros ADO.

Como veis, los tiempos son muy buenos con ambos métodos (mirad los registros devueltos por la segunda consulta…). Siempre algo mejores con DAO.

Vamos ahora con las pruebas con DISTINCT, aquí viene la verdadera carga:

Pruebas con el generador de consultas de Access

Generador de consultas Access valores únicos 500.000 registros.

Ahí si que «duele» de verdad… Más de un minuto ejecutando la consulta. Podríamos sacar varias conclusiones de estos resultados (hay que suponer, no podemos asegurar sin una herramienta tipo Profiler). Lo que seguimos viendo, es que filtrando primero, divide el tiempo casi por 60.

Podríamos pensar que primero se envían los datos y que luego se ejecuta la consulta en local. Por ello, al enviar más de 2.000.000 de registros por la red, tarda tanto. Pero como hemos comentado antes, esto no tiene sentido viendo que en las pruebas tarda menos de 3 segundos en mostrar la consulta entera.

Entonces ¿dónde está el problema? O a lo mejor no hay tal problema y si hiciéramos la misma consulta contra Oracle obtendríamos resultados similares… Vamos a probar con DAO y con ADO a ver si aclaramos algo.

Prueba con DAO

Pruebas con DAO valores únicos 500.000 registros.

Prueba con ADO

Pruebas con ADO valores únicos 500.000 registros.

Aquí nos sucede algo parecido que lo que sucedía en la prueba anterior, los tiempos se dividen entre 2. No tengo ni idea de si estos tiempos son buenos o malos, pero estamos evaluando una consulta de más de 2 millones de registros y convirtiéndola en 11.643 únicos. Ya veremos en otros SGBD…

Prueba 3

Poco que comentar en esta prueba, como comentaba en el artículo anterior, es «fuego real» así que no tengo tablas con tantos registros. Los tiempos son muy buenos incluso para consultas con mucha carga como las que venimos viendo. Posteo los resultados simplemente como anécdota:

Pruebas con el generador de consultas de Access

Generador de consultas Access concurrencia.

Pruebas con DAO

Pruebas con DAO valores únicos concurrencia.

Ya veis que los resultados son muy buenos incluso para este tipo de consultas. Fuego real en una base de datos mediana y resultados más que aceptables.

Conclusiones generales para consultas de selección

Para terminar con la serie de artículos sobre rendimiento de Access como servidor en consultas de selección, vamos a resumir las conclusiones:

Para bases de datos de hasta 500.000 registros por tabla, el comportamiento de Access para consultas de selección es muy bueno. Hemos realizado pruebas contra tablas únicas, varias tablas relacionadas, tablas con LEFT JOIN y los tiempos son siempre inferiores al segundo exceptuando la consulta con LEFT JOIN contra las tablas de 500.000 registros que ronda los 3 segundos.

Para consultas con parámetros, los resultados contra tablas bien indizadas son muy superiores.

Los resultados son mucho mejores utilizando tanto DAO como ADO en vez del generador de consultas de Access, aunque este aguanta bastante bien si no hay tablas muy grandes o mucha carga de proceso.

Cuando necesitamos mucha carga de proceso (en nuestro caso en las consultas de valores únicos), el rendimiento de Access baja bastante. Es necesario pedir solo lo que necesitemos, filtrando bien las consultas. Hay que optimizar.

En general, los resultados han sido bastante mejores de lo que me esperaba. Veremos lo que ocurre en los siguientes artículos con consultas de agregado (GROUP BY) y más adelante con consultas de acción (UPDATE, INSERT y DELETE).

The following two tabs change content below.

• Bio
• Latest Posts

Arkaitz Arteaga

Administrador at Programador de Palo

Llevo más de 10 años programando, sobre todo en Visual Basic y con bases de datos Access. Para mí, VBA y Access siguen siendo herramientas muy potentes. He desarrollado varios proyectos con PHP y MySql. Si sumo las webs que he tenido, probablemente pasaría de 100. Ahora prefiero dedicar todo mi esfuerzo a este blog (aunque sigo manteniendo unas cuantas...). Trabajo en la administración pública (si, soy funcionario), pero he trabajado en pequeñas empresas e incluso en una "grande" de las telecomunicaciones. Ultimamente estoy bastante metido en abrirme nuevos horizontes con C# y .NET. Renovarse o morir!

Latest posts by Arkaitz Arteaga (see all)

• Access: Encriptar contraseñas con SHA-256 utilizando biblioteca de clases .NET con C# - 4 mayo, 2014
• Rendimiento de Access contra backend Access en servidor de archivos remoto. Cuarta parte. - 27 abril, 2014
• Rendimiento de Access contra backend Access en servidor de archivos remoto. Aclaración. - 21 abril, 2014
• Utilizar biblioteca de clases .NET en Access. Tercera aproximación a la Interoperabilidad COM - 14 abril, 2014
• Vincular tablas en Access con Visual Basic - 11 abril, 2014