Rendimiento de Access contra SGBD de servidor. Primera parte.
Hace unos días, mi amigo Ángel de AccessyExcel.com publicó un artículo sobre Conectar Access con base de datos Azure que nos hizo reflexionar sobre el rendimiento de Access como Front-End utilizando un SGBD de servidor como Back-End.
Os recomiendo que leáis su artículo ya que hace una muy buena presentación de las posibles decisiones de diseño que tendremos que tomar si queremos migrar una base de datos Access a cualquier SGBD de servidor.
El equilibrio entre mejorar el rendimiento, y la necesidad de realizar modificaciones del Front-End ya implantado, con todo lo que esto conlleva (Molestias al cliente, trabajo extra, posibles incompatibilidades, etc.), puede ser clave. Si utilizamos tablas vinculadas, las modificaciones del Front-End y por tanto las molestias a los clientes serán casi nulas. En cambio el rendimiento puede verse afectado, o eso es de lo que iremos investigando en este artículo.
Todo lo anterior se centra en que partimos de un escenario de una aplicación ya creada con Access y con Back-End También Access. Lo ideal sería desarrollar desde cero las aplicaciones para utilizar con bases de datos de servidor, creando desde un inicio la base de datos en el SGBD de servidor (el enfoque para crear bases de datos en SGBD de servidor cambia bastante en algunos aspectos y además nos ahorramos el trago de migrar los datos). De todo esto va a tratar este artículo, o mejor dicho, esta serie de artículos.
Alguno os preguntaréis, si creamos desde cero ¿por qué no utilizar otra herramienta más potente que Access? La respuesta a esta pregunta puede ser distinta en cada uno de vosotros, yo voy a exponer varios motivos que podrían ser suficientes, pero no dudo que tengáis razones de peso para utilizar otro tipo de herramienta.
No voy a basar mi defensa de Access como Front-End de bases de datos de servidor en las típicas cuestiones de sencillez de desarrollo, licencias, conocimiento de la herramienta por parte de los usuarios, escalabilidad, etc. Voy a hablar directamente de potencia de la herramienta.
Todos sabemos que queda mucho mejor decir que estás diseñando un aplicación con Java contra Oracle o con Visual Studio y SQL-Server que decir que utilizas Access. Incluso el usuario medio puede soltar perlas del estilo:
Eso lo manejo yo desde hace mucho, soy experto. Para eso ya creo yo las tablas y las consultas. Pensaba que eras programador, esto es una herramienta ofimática
Pues bien, con esta supuesta herramienta ofimática se pueden desarrollar aplicaciones cliente/servidor tanto o más potentes (depende del desarrollador) que con los lenguajes «de moda» de cada época (si, cada época tiene su lenguaje de moda). A ver, yo también he desarrollado en Java y estoy de acuerdo en que te deja una libertad de movimientos difícilmente igualable y su potencia de base casi no tiene límites. Lo que no tengo tan claro es que el desarrollador medio le saque partido a todo esto. He visto desarrollos en Java, C# o incluso en PHP terriblemente malos y desarrollos en Access impresionantemente buenos.
Si, tenéis razón, en el párrafo anterior no estoy hablando de la herramienta, estoy hablando del desarrollador. Siguiendo esa lógica, un desarrollador bueno creará mejores herramientas en Java que en Access. Es posible, pero a veces nos olvidamos de algo que tiene Access y no tienen las demás herramientas, algo que normalmente se suele poner en el lado negativo, el motor de base de datos local.
Pues si, aunque vaya a contracorriente, voy a romper una lanza a favor del motor de base de datos de Access. Estamos de acuerdo en que los motores de base de datos de SGBD de servidor típicos como SQL-Server, Oracle, SQL Azure, MySql, son mucho más potentes, seguros, aceptan procedimientos, manejan mejor la concurrencia y ya no hablemos de backups, disponibilidad y recuperación ante desastres.
¿Y por qué no aprovechamos esto y le sumamos un motor de base de datos local?. Me explico, podemos encontrar el equilibrio entre utilizar la potencia de este tipo de SGBD descargando cuando nos interese la carga del servidor realizando cálculos, consultas u otras operaciones en local.
Ahora la pelota está del lado del desarrollador, quitamos importancia a la herramienta y se la ponemos a la optimización del desarrollo. Tendremos que encontrar para cada problema una solución óptima utilizando las herramientas de las que disponemos. Y aquí es donde empieza la conversación con mi amigo Ángel y donde empezamos a realizarnos preguntas que intentaré contestar, aunque al final creo que la mejor opción es probar distintas soluciones para cada caso.
La conversación empieza hablando de las tablas vinculadas ya que lo más cómodo sería vincular las tablas del servidor con Access y mantener el resto. ¿Viajan las tablas enteras por la red o sólo los datos que pedimos?.
Después de buscar bastante por la red y obtener todo tipo de respuestas contradictorias encontré este post de UtterAccess en el que el autor directamente prueba con SQL-Profiler la manera de actuar de una tabla vinculada a SQL-Server mediante ODBC. En esta prueba se puede ver que no siempre viajan todos los datos por la red y se ven ejemplos de como el motor de base de datos de Access pide un solo registro a la base de datos. Es más, el ejemplo utiliza el motor que utilizaban las versiones antiguas de Access, el motor de base de datos JET. Con el motor actual (ACE) debería de funcionar todavía mejor sobre todo en Sharepoint y servicios web.
Y ahora la pregunta del millón, ¿cuándo se da el caso de que se le piden todos los registros de una tabla al servidor trabajando con tablas vinculadas?
Pues aquí también he realizado algo de labor de investigación y parece que queda claro que hay varias situaciones en las que las tablas vinculadas mediante ODBC pueden ser una opción poco aconsejable (más adelante introduciré varias posible soluciones). Access crea un plan de ejecución de consultas en forma de árbol en el que intenta recuperar solo los datos necesarios del servidor. Copio literal de la web de Microsoft:
Office Access crea un plan de ejecución de las consultas que está en forma de árbol, con las tablas de origen como los nodos de la hoja y el conjunto de resultados como la raíz. Cuando se trabaja con tablas vinculadas de SQL Server , Office Access intenta crear una única sentencia SQL que pueda presentar a través de ODBC para recuperar el conjunto de resultados de la raíz. Sin embargo , a veces es incapaz de hacerlo. Puede ser capaz de combinar sólo un subconjunto de todas las combinaciones y criterios en este tipo de instrucción SQL. En estos casos , Office Access presenta dos o más consultas de SQL Server y combina los resultados a nivel local para obtener los resultados finales.
Después pasa a enumerar los casos:
- Consultas anidadas que utilizan una consulta externa para procesar los resultados de una o varias consultas internas.
- Combinaciones complejas de inner y outer joins
- Las consultas que hacen referencia a los datos de múltiples fuentes de datos, incluso varias bases de datos de SQL Server
- Las consultas que pasan los valores de fila a funciones de VBA
- Las consultas que utilizan funciones o expresiones incorporadas que no se traducen con éxito
- Las consultas que utilizan la sintaxis SQL Office Access específico , como PIVOT… TRANSFORM
Después Microsoft nos aconseja ir mirando consulta por consulta (en este caso y como utiliza SQL-Server, mediante SQL Profiler) la manera de actuar del motor de base de datos Access. Si vemos que intenta obtener del servidor más datos de los que realmente necesitamos, trataremos primero de optimizar la consulta añadiendo criterios que se enviarán al servidor. Si a pesar de todos nuestros esfuerzos de optimización de consultas, Access recupera algunas tablas enteras para luego procesarlas en local, la mejor solución puede pasar por crear vistas en el servidor de base de datos y vincularlas o en crear consultas pass-through.
Cuando se habla de consultas pass-through, nos estamos refiriendo a consultas que se ejecutan directamente en el servidor. Hay varias maneras de crearlas como veremos en un artículo posterior. Muy relacionado con esto están los procedimientos de SQL creados en el servidor de bases de datos, que también pueden ser llamados desde Access. Al final son distintos métodos para mover la carga de procesamiento al servidor, así que tendremos que encontrar cuando es mejor utilizar cada uno.
Otro de los escenarios en los que Microsoft desaconseja la utilización de tablas vinculadas es en las operaciones de actualización de grandes cantidades de datos. El decidir cuándo una operación de actualización de datos es lo suficientemente grande queda de nuestro lado.
He de decir que viendo la manera de actuar de Access para actualizaciones, prefiero pasarme de precavido y realizar todo en el servidor. Parece ser que Access aunque selecciona correctamente los datos a actualizar (en bloque), luego los va actualizando registro por registro desaprovechando la mayor virtud de SQL, el manejo de bloques de registros. En el siguiente artículo discutiremos la mejor manera de ejecutar en servidor este tipo de instrucciones (ADO, DAO, procedimientos, etc), pero queda claro que para actualización de datos (cuando hablo de actualización me refiero a añadir, actualizar y borrar datos) es mejor dejar al SGBD del lado de servidor que demuestre toda su potencia.
Nos quedan muchas cosas en el tintero para uno o varios artículos posteriores, utilizar vistas, procedimientos, incompatibilidades, DAO o ADO, formas de ejecutar en servidor, cuándo es preferible realizar cálculos en local, etc.
Además, hay un concepto muy interesante que quiero introducir, los Front-End desconectados (que por cierto es la manera en la que yo suelo desarrollar, sobre todo utilizando Back-End Access) y los tipos de bloqueo de registro relacionados a este concepto.
Arkaitz Arteaga
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
- AccesyExcel | ADO | Aplicaciones | Azure | BackEnd | Bases de datos | DAO | MySql | Oracle | Sql-Server | tabledef