Access - Rendimiento base datos backend servidor + frontend local

Buenas,

Tengo una base de datos hecha en access mejorada y ampliada durante los último años y dividida en frontend y backend.

Incluye código VBA con consultas SQL, una interfaz bastante trabajada, etc.

Inicialmente estaba el back en un NAS de la red local y el front en uno de los pc de la red local.

La interfaz se movía muy rápido aunque ciertas consultas tardaban unos segundos al tener que mover por la red las tablas.

Para poder trabajar desde cualquier sitio, se ha alojado tanto el front como el back en un servidor VPS con winserver2016 al que se accede por terminal server protegido por una conexión IPsec.

Ahora las consultas se ejecutan instantáneas, al estar datos y programa en el mismo servidor, pero por la propina naturaleza del escritorio remoto y el refresco de pantalla, cuando estás acostumbrado a trabajar muy rápido moviendote por menús, opciones, etc, resulta algo lento.

Mi duda es la siguiente.

Si hago con access la migración del backend a SQLSERVER y le instalo y configuro al servidor virtual el SQLexpress, y vuelvo a pasar el frontend al pc local y vinculo las tablas al sqlserver alojado en el servidor virtual.

¿Se ralentizarán mucho las consultas?
¿Merece la pena?

Gracias!

Hola Pedro, no se muy bien que cantidad de datos hay que pasar por la red, ni que tipo de red teneis...

Si las consultas devuelven millones de registros que hay que pasar por la red, seguramente habria que optimizar esas consultas para que no devuelvan tantos registros.

Si la red es 10/100, es muy recomendable cambiar la red a 1000Mbits... el incremento de velocidad es muy grande.

Nosotros disponemos de servidores que solo son para la base de datos, y no tenemos esa latencia que vosotros encontrais. Si que es cierto, que las bases de datos trabajan sobre Linux, pero no creo que esa sea la diferencia.

Exactamene como comenta info

El rendimiento dependera de la cantidad de informacion a solicitar

Si eres de los que abres el formulario en Acces para mostrar todos los registros antes de filtrar, debes cambiar el concepto de filtrado o rediseñarlo, Access por defecto trae 10.000 porque su cursor es local por lo tanto moveras diez mil registros y digamos que tienes 10 clientes front entonces multiplica eso por 10 y moveras 100.000 registros por la red y si es por ODBC ya te imaginas

Por lo tanto mi recomendacion:

1) Rediseñar los formularios que traen valores antes de solicitarlos
2) No usar tablas vinculadas, reemplaza por ADO y pasa los resultados de los recordsets al formulario o reporte directamente,
3) Manten abierta una unica conexion a la bd, solamente declara una variable global para ello, cierra la conexion cuando salgas de la aplicacion
4) Los recordsets para reportes o consultas debe declararse como de solo lectura, si no vas a modificar con esos datos,
5) Utiliza los prepared statements y pasa parametros a los querys, es mucho mas eficiente
6) Los indices son indispensable deben estar creados en los PK por cada tabla y en las columnas que son FK, estos indices se activan al momento de hacer JOINS
7) Es posible que tengas que rediseñar las vistas de access, si aplicas el punto 2
8) Si usas funciones propias en tus vistas en Access, ejecutaran por tablas vinculadas, en caso contrario tendras que transformar tus funciones a TRANSACT SQL para que sea el servidor quien las ejecute, la ventaja es que ganaras velocidad, la desventaja es que tendras trabajo adicional para reacomodar tus vistas
9) Se comedido con el uso de SELECT * FROM, el asterisco genera mucho trafico en la red, sobre todo cuando hay muchos JOINS de por medio, devuelve solo los campos que necesitas.

Creo que es todo

Saludos

Access no es para tenerlo en un servidor, es una b.d. para usuarios avanzados, no es para desarrollar aplicaciones... luego tienes muchos problemas

Gracias por los comentarios.

Primeramente aclarar que el plantearme la migración es porque trabajar con terminal server se me hace poco ágil, pero de momento funciona perfectamente y no tenemos más problemas. Se trata de "esto va bien, pero podría ir mejor aún?" Por ello estoy valorando si tendré mejor rendimiento haciendo el cambio. Si al final va a ser a peor, seguiría con el frontend y backend en access, todo en el servidor virtual y listo.

Voy a concretaros más.

Para que os hagáis idea, ahora mismo el backend ocupa 3,8MB y la tabla más grande (la de facturación) tiene 6.000 registros. Todo esto con los datos de 6 años. Las características de la empresa hacen que el volumen de facturación anual sea comedido (500-600). Si fuera un supermercado por ejemplo, serían millones de registros al año y complicaría las cosas.

Actualmente dicha base de datos solamente la usa un usuario y en breve un segundo usuario para consultar datos solamente.

El tráfico sería a través de internet, ya que el backend de sql server estaría en el servidor virtual (al cual nos conectamos mediante una VPN para dar cierta seguridad) y el frontend de access estaría en uno o varios pcs (bien de la oficina, o externos, ya que al final se trata de poder trabajar desde cualquier sitio)

Las conexiones usadas en oficina son fibra simétrica 300Mb y fuera depende, puede ser fibra 100Mb o una conexión wimax de 10Mb.

Respecto a lo que dice Pancho. Ya me había hecho idea de que, en caso de hacer el cambio, la filosofía es mandar al servidor sql la consulta a realizar y que él procese todo y solamente devuelva por la red el resultado de las consultas.

Soy muy aficionado a la informática y he sido autodidacta con el access desde el 2010 y la base de datos aún conserva algunos módulos que al verlos hoy me da hasta vergüenza de como están hechos (os podeis imaginar pues todas las cosas ineficientes que haría un novel en access) y poco a poco las voy modificando y dejando formularios "en blanco" que se controlan por código y se obtienen los datos por sentencias SQL en VBA.

Los puntos 5, 6 y 8 son los que no entiendo a que te refieres. Podrías comentarlos un poco más?

En el caso de migrar, también me plantearía el ir poco a poco cambiando el frontend a un programa win hecho en VB.Net (ya me estoy familiarizando con el visual studio como afición) Aunque me da lástima desprenderme del entorno access que tan cómodo es. Merecería la pena?

Gracias una vez más!

Hola

Este es un ejemplo de prepared statement para el punto 5, los comodines (?) son los valores a ser reemplazados, esto tiene sus ventajas,
a) La compilacion de la sentencia se ejecuta una sola vez
b) Se mejora la seguridad (SQL Injection por mencionar una)


El punto 6 se refiere a que las columnas que son claves primarias (PK) deben tener un indice lo mismo se aplica para aquelas columnas que son claves foraneas (FK) en SQL se crean usando la sentencia CREATE INDEX idx_name ON mytable(mycolumn,...), al momento de hace JOINS ( https://www.genbeta.com/desarrollo/explicacion-grafica-de-los-join-en-sql-y-sus-resultados ) el servidor activa los indices si estan dsiponibles para acelerar los resultados

El punto 8 se refiere a los UDF (funciones definidas por usuarios) si has usado VBA sabes que puedes desarrollar tus propias UDF y usarlas dentro de tus sentencias SQL asi -> SELECT col1, col2, mi_funcion(col3) FROM mi_tabla al momento de ejecutarse mi funcion debe estar declarado dentro de tu codigo VBA, pero si usas esa misma sentencia desde un SQL Server por ejemplo, te devolvera error por que el servidor no puede encontrar mi_funcion(), esto se debe a que el servidor entiende o esperaba codigo TRANSACT SQL no VBA,

TRANSACT SQL es es lenguaje de programacion para SQL Server, asi como Postgres tiene su PGPL y ORACLE su PLSQL, por lo tanto todas aquellas funciones que usas en tus sentencias con codigo VBA tienes que reescribirla al lenguaje nativo del servidor de base de datos.

Como comentario final, hasta la version 2010 de Access, se podia integrar perfectamente un aplicacion Access con SQL Server lo mejor de los dos mundos), la extension de los archivos es ADP (Access Data Project), tenias toda la funcionalidad de Access (formularios, reportes, vistas) integrada con la potencia de un Servidor SQL Server, por desgracia eliminaron esa integracion ( como siempre sin preguntar ) en versiones posteriores y era una lastima porque funcionaba de maravilla.

Yo en lo particular todavia lo uso, por su increible facilidad para desarrollar cualquier tipo de aplicacion y me ha funcionado hasta con servidores SQL Server 2014. Echale un vistazo que te puede interesar!

Esper haber aclardo tus dudas

Saludos

Puntos 6 y 8 aclarados.

Usamos O365proplus por lo que el access es el más reciente y hemos perdido esa integración que dices :(

Lo que me mosquea también es que hablando con los informáticos que nos mantienen el VPS y con otro externo, coinciden en que, si bien con escritorio remoto hay un lag por el envío del propio escritorio por internet, va a ser mejor solución que montar el sql y adaptar el access para tan solo dos usuarios y con una base de datos de poco peso. Además coinciden en que las consultas realizadas a través de la VPN al servidor van a dar unos segundos de retraso. Con todo esto me dicen que si fuera para ellos, no migrarían. En todo caso dotarían al VPS de un núcleo más de procesador para que fuera siempre sobrado con dos usuarios a la vez y mejorar/optimizar la actual base datos access.

Entiendo entonces que cuando el sql va fluido es cuando servidor y clientes están en la misma red local.

Que opináis?

Y es cierto lo que te recomiendan, en realidad por tan pocos registros no vale la pena el esfuerzo, el lag que comentan se debe al tiempo de respuesta que tarda en enviarse un paquete por la red desde origen a destino y viceversa.

Valores por encima de un segundo se considera lento, pero hay otros factores como calidad tu infraestructura de red ( me refiero a si respeta las normas ), la banda ancha que tengas y el consumo o servicios que estas usando por ejemplo camaras de seguridad LAN/WAN, VoIP, Audio, etc. tambien influye a que distancia esta tu servidor.

Cuando se desarrolla una app cliente/servidor ya estas entrando en otro terreno, desarrollo por capas, pool de conexiones, etc. Ya estariamos hablando de desarrollo con lenguajes como ASP, PHP, JSP, JSF, usando frameworks como ASP.Net, CodeIgniter, Primefaces, Django(Python), Rails(Ruby) y asi sucesivamente.

Espero que te hayas formado una idea general con el nuevo conocimiento y puedas tomar la decision mas adecuada a tu caso

Saludos

La verdad es que sí, ya tengo una idea bastante clara del camino a seguir.

De momento será optimizar lo que ya hay, dotar quizás al servidor de un núcleo más de procesador para que vaya lo más fluido posible (winserver a veces se come muchos recursos), reescribir algunos de los módulos de access existentes para hacerlos óptimos y como "afición" iré aprendiendo VB.net y jugueteando con conexiones a la base de datos para en un futuro quizás, tener todo el frontend en VB.

Empezaré a seguir este foro para intentar aportar también mis experiencias.

Gracias por todo!