Hola hola hola mis queridos programadores. Hoy abordaremos la segunda parte del test ¿son eficientes los ORM? Si en la primer publicación comprobamos el rendimiento de una inserción masiva de registros, mediante nuestro ejemplo de parte de horas anual, en esta ocasión usaremos esos miles de registros generados para los partes del año 2020, generaremos unos miles de registros más para otros años, y con ellos diseñaremos un informe de absentismo, contraponiendo la eficiencia de las consultas generadas en un procedimiento almacenado, con las consultas generadas con Entity Framework.
A continuación simularemos a varios usuarios realizando peticiones del informe, con cada una de las técnicas, y valoraremos los resultados. Les recomiendo encarecidamente revisar el test 3 para recordar los términos del ejemplo Parte Horas.
Como siempre les recuerdo que disponen del código fuente íntegro de la plataforma de test en GitHub.
En este artículo además aprenderemos:
Son 100 trabajadores identificados con un GUID, cada uno de los cuales tiene un registro por cada día laborable (de lunes a viernes, sin fiestas ni vacaciones), en el que se indica las horas que trabajó.
Consideramos una jornada normal de trabajo la que dura ocho horas, y absentismo todo lo que este por debajo de una jornada de 8 horas, pero debemos tomar en cuenta que nuestros trabajadores imaginarios tienen la oportunidad de recuperar horas perdidas, fíjense por ejemplo en nuestro amigo 2D4F479E-D17B-4273-A965-4F52ACAA70D5:
Lo primero que vamos a hacer es diseñar un informe básico de absentismo, completamente desarrollado con un procedimiento almacenado de SQL SERVER. Lo usaremos como base para después implementar variaciones técnicas, con idéntica funcionalidad.
¡Bien! La ejecución de este procedimiento para el año 2020 devuelve 100 registros (uno por cada trabajador) con toda la información que necesitamos para nuestro informe:
Nuestro objetivo ahora será el de desarrollar una consulta idéntica a ésta, pero con notación LINQ, dentro de un repositorio de Entity Framework . Como contenedor de datos vamos a crear una clase DTO, que será el resultado tanto de nuestro ejemplo con ADO.NET como con EF.
Veamos como quedaría nuestra SELECT con LINQ:
Comprobemos que los resultados de las consultas SQL y LINQ son los mismos:
¡Perfecto!
Con esta primera versión de nuestro informe de absentismo, señores, estamos en disposición de montar el¡¡ Test 4 !! Ya conocen Uds. sobradamente el mecanismo para montar un nuevo test en la plataforma, por lo que no voy a repetirlo en esta ocasión. Si tienen alguna duda al respecto no duden en contactar conmigo a través de los comentarios y haré lo posible e imposible por ayudarles.
Vean como queda el código de nuestros dos nuevos testcases en la clase test4_sql_querying:
Y veamos también el resultado de un par de ejecuciones:
Demasiado fácil. Unos 200 milisegundos para EF y unos 10 para ADO. Poca diferencia. Estos tiempos lo que nos dicen es que la máquina resuelve esta consulta sin problema aparente. Pero no nos vamos a quedar aquí amigos.
Podríamos decir que casi todas las consultas de un sistema de software en producción, ejecutadas de forma aislada, en un entorno sin estrés, nos devolverán resultados aceptables. La cosa se complica cuando son muchos usuarios concurrentes solicitando informaciones. Bien, pues trataremos de simular ese estrés de modo que en lugar de ser un único usuario el que consulta el informe de absentismo, sea todo un departamento de recursos humanos.
En primer término adaptaremos nuestra capa de negocio para poder hacer una solicitud de varios años del informe:
Para simular peticiones más o menos simultáneas de varios usuarios haremos lo siguiente: en los métodos InformeAbsentismo_EF e InformeAbsentismo_ADO crearemos tres oleadas de peticiones, la primera realizará 5 peticiones del año 2020, la segunda 5 peticiones de los años 2019 y 2020 y la tercera 5 peticiones de los años 2018, 2019 y 2020. Como punto de partida crea que son cifras aceptables. Así queda el método InformeAbsentismo_EF:
Cada uno de los hilos llaman a la función privada Hilo_Peticiones_EF que les muestro a continuación:
Bien, así en total disponemos de (5*3)+(5*2)+5 = 30 peticiones para ADO y 30 para EF. ¿Qué más? Bueno, antes de poder probar estas nuevas complejidades, hemos de tener en cuenta que actualmente en nuestra base de datos solo disponemos de información para el año 2020 (registros que se quedan grabados tras la ejecución del TEST3). Debemos generar registros para el año 2018 y 2019, para lo cual debemos modificar ligeramente el código del Test3 en su método Start(), ya que el año 2020 se encuentra hardcodeado.
Fácil. Ahora disponemos de 78.400 registros en la tabla test.ParteHoras. Implementamos las mismas oleadas de peticiones para la parte ADO.NET y lanzamos de nuevo un par de ejecuciones de los test:
Curioso ¿verdad? Parece que los tiempos se van igualando. Más o menos nos está resolviendo las peticiones a una por segundo. No está mal, pero tampoco está bien. Tenemos que ver si pueden mejorarse estos tiempos. Pero antes una cuestión: ¿cómo sabemos que el test está haciendo lo que realmente creemos que debería estar haciendo? ¿Sabemos si realmente está devolviendo la info. a cada una de nuestras peticiones?
Una manera interesante es usar la utilidad SQL Server Profiler, que nos devuelve una traza de todos los eventos que están ocurriendo en nuestra base de datos. vean:
La interpretación es un poco engorrosa, pero la información que devuelve es de gran utilidad. La línea que está marcada en la captura es el INSERT de la ejecución en base de datos. Por lo tanto nos marca el final del testcase 1. Nos interesan las columnas con el número de lecturas y escrituras, y la duración del proceso. Además en la columna TextData nos dice qué instrucción se está ejecutando. Para el caso de Entity Framework esto es bastante interesante, ya que podemos ver cómo nuestra instrucción LINQ se transforma en una SQL, no siempre todo lo bonita que nos gustaría:
Y cuando finalmente voy a buscar las ejecuciones del procedimiento almacenado, me encuentro que todas las ejecuciones del testcase 2 son idénticas a las del testcase 1. ¿Cómo es esto posible? Pues se lo voy a decir: error humano. Tomar la precaución de usar esta herramienta me ha servido para darme cuenta de que en los dos testcases estaban ejecutando el mismo código de datos, el correspondiente a Entity Framework. ¡Por eso los tiempos se igualaban! Sepan que el error ha sido completamente fortuito, nada intencionado. Sobre la marcha nos encontramos con dificultades y juntos las vamos resolviendo. Localizo el error, lo corrijo y vuelvo a ejecutar:
El error está aquí, en la inyección del repositorio. La línea corregida en el método Hilo_Peticiones_ADO quedaría así:
Las dos nuevas ejecuciones nos devuelven resultados bien distintos:
Vamos de nuevo a ejecutar una traza con SQL Server Profiler para comprobar que esta vez sí salió todo como esperábamos:
Esto es otra cosa. Resaltado en amarillo está el final del testcase1 y comienzo del testcase2. Y aquí ya encontramos las llamadas a nuestro procedimiento GetEstadisticasAbsentismo. Además podemos apreciar que los tiempos que nos arroja nuestra herramienta de test son coherentes con los tiempos de ejecución que nos da Profiler.
Ya nos hemos asegurado de que efectivamente se realizan las peticiones que hemos programado. Y dada la gran diferencia de tiempos, ¿podemos ir extrayendo ciertas conclusiones? No, aun no.
Aunque ADO está resolviendo sus 30 peticiones en poco más de un segundo, me pregunto si en lugar de 30 peticiones fueran 300, ¿se vería nuestro sistema de parte de horas muy resentido?
Bueno, desde el principio del post tengo en la cabeza algunas mejoras que deberíamos aplicar a nuestras consultas. Las mismas deberían afectar positivamente tanto a las consultas con EF como a las consultas con ADO. Comprobémoslo.
Bastante más elegante ¿no creen? Y el broche de oro de la elegancia lo ponemos en el DTO de esta manera:
Noten que añadimos aquí las propiedades conteo_registros y suma_horas para poder recuperar estas informaciones de la base de datos, y calculamos el resto de propiedades en base a estos dos valores. Las propiedades del informe quedan en modo solo lectura y además, no se ejecutará el cálculo hasta el momento en que se requiera. Y no ganamos solo en performance, sino también en legibilidad.
Como resultado de estas modificaciones obtenemos con EF unos tiempos de en torno a 25 segundos, una mejora bastante significativa. Los tiempos con ADO permanecen parecidos.
Pero no nos conformamos.
Habrán observado que para obtener el informe del año adecuado usamos YEAR(Fecha) = @anho en el procedimiento almacenado, y .Where(an => an.Fecha.Year == anho) en LINQ. ¿Y si pudiéramos ahorrarnos ejecutar una función sobre una fecha para cada uno de los registros? Sería tan sencillo como añadir un campo entero en la tabla test.ParteHoras, y dejar el año del registro pre-grabado. Vamos a ello.
Añadir el campo es BB.DD es tan simple como tocar el modelo de E.F. y volver a ejecutar la aplicación. Al momento se crea nuestro nuevo campo en BBDD:
Ahora para alimentar este campo en la clase de dominio añadimos igualmente el campo:
Con Entity Framework el nuevo campo queda automáticamente alimentado. En cambio con ADO tenemos que añadir manualmente el campo, en la capa de datos, y en el procedimiento almacenado. Como ven cada mecanismo tiene sus ventajas y sus inconvenientes. Para más INRI como este es un INSERT masivo, debería modificar el tipo definido por el usuario de tipo tabla. Por suerte, como es un campo calculado, en este caso me puedo evitar este trastorno simplemente tocando el procedimiento almacenado de la siguiente forma:
Toca comprobar que la carga de datos con el nuevo campo funciona correctamente con ambos mecanismos, y que el nuevo campo queda informado
Ya casi estamos terminando. Volvemos a tener nuestros 78.400 registros en la tabla, esta vez con el campo Anho informado, así que me dispongo a cambiar las condiciones de las consultas.
Todo listo para volver a ejecutar el TEST4. ¿Creen que un cambio tan minúsculo podrá tener alguna repercusión? Comprobemos:
Pues otros pocos segundos le hemos podido arañar a las consultas con Entity Framework, mientras que los resultados consultando con ADO.NET se mantienen estables, lo cual no deja de ser buena señal. Noten que, en el caso de EF, hemos pasado de 40 segundos en nuestras primeras tentativas, a poco más de 20 segundos en esta última ejecución. Ahora extrapolen esto a un sistema en producción con cientos, o miles de usuarios. Sí amigos, el demonio está en los detalles.
Llegados a este punto uno podría pensar que hemos alcanzado la perfección, que no puede mejorarse más, ¿cierto? ¡Nunca! Siempre puede mejorarse un poco más.
Puesto que esta publicación ha quedado ya bien nutrida, voy a ir rápido, pero sepan que el impacto de un índice adecuado en el lugar adecuado es CLAVE.
Está claro que una consulta de estas características se vería beneficiada por un índice en la tabla ParteHoras. Por regla general hay que hacer índices sobre los criterios de búsqueda (WHERE) e incluir (con mucho cuidado) los campos resultantes (SELECT) en la sección INCLUDE del índice. Pero hemos de ir con cuidado ya que los índices (i) tienen repercusión negativa en la performance de INSERTS y UPDATES, y (ii) ocupan espacio.
Para ir sobre seguro vamos a revisar el plan de ejecución de nuestro procedimiento almacenado, una práctica totalmente aconsejable. El mismo motor SQL se dará cuenta de que esta consulta funcionaría mejor con un índice, y nos lo sugerirá:
El índice que nos propone SQL es totalmente coherente de modo que le doy un nombre (IX_Absentismo) y me dispongo a crearlo ejecutando el script. A continuación evaluaremos su impacto volviendo a ejecutar, ya sí por última vez, el TEST4:
Vean que he dejado lo mejor para el final. Aunque para dar con el índice correcto hemos usado el plan de ejecución del procedimiento almacenado, el mismo afecta tanto al procedimiento, como a las consultas con EF. El resultado es impactante: hemos reducido el tiempo de consulta con EF de unos 20 segundos a 4-5 segundos, y el de ADO lo hemos dejado en menos de 1 segundo para el total de ejecuciones.
¿Mi conclusión personal? I LOVE THIS GAME.
Espero tener ocasión en próximas publicaciones, de profundizar en el tema de las trazas (profiler), los índices y los planes de ejecución, ya que son herramientas de enorme potencia.
Les anuncio que estoy trabajando en algo muy, muy interesante que pronto estaré en disposición de compartir con Uds. Aquí les dejo una intrigante pista. Amigos, les deseo que todo compile bien en sus vidas: salud, y prosperidad.
A continuación simularemos a varios usuarios realizando peticiones del informe, con cada una de las técnicas, y valoraremos los resultados. Les recomiendo encarecidamente revisar el test 3 para recordar los términos del ejemplo Parte Horas.
Como siempre les recuerdo que disponen del código fuente íntegro de la plataforma de test en GitHub.
En este artículo además aprenderemos:
- Uso de SQL Server Profiler para analizar la actividad de la base de datos
- Analizar planes de ejecución para detectar nuevos índices
- Buenas prácticas en el desarrollo de consultas
Son 100 trabajadores identificados con un GUID, cada uno de los cuales tiene un registro por cada día laborable (de lunes a viernes, sin fiestas ni vacaciones), en el que se indica las horas que trabajó.
Consideramos una jornada normal de trabajo la que dura ocho horas, y absentismo todo lo que este por debajo de una jornada de 8 horas, pero debemos tomar en cuenta que nuestros trabajadores imaginarios tienen la oportunidad de recuperar horas perdidas, fíjense por ejemplo en nuestro amigo 2D4F479E-D17B-4273-A965-4F52ACAA70D5:
Lo primero que vamos a hacer es diseñar un informe básico de absentismo, completamente desarrollado con un procedimiento almacenado de SQL SERVER. Lo usaremos como base para después implementar variaciones técnicas, con idéntica funcionalidad.
/* exec test.GetEstadisticasAbsentismo 2020 exec test.GetEstadisticasAbsentismo 2019 exec test.GetEstadisticasAbsentismo 2018 */ ALTER PROCEDURE [test].[GetEstadisticasAbsentismo] @anho int as begin select Trabajador, COUNT(1) * 8 as total_horas_laborales, COUNT(1) as total_dias_registrados, SUM(Horas) as horas_trabajadas, COUNT(1)*8 - SUM(Horas) as horas_no_trabajadas, (COUNT(1)*8 - SUM(Horas))/8 as dias_no_trabajados, (COUNT(1)*8 - SUM(Horas))*100 / (COUNT(1) * 8) as pct_absentismo from [test].[ParteHoras] where YEAR(Fecha) = @anho group by Trabajador order by horas_no_trabajadas end
¡Bien! La ejecución de este procedimiento para el año 2020 devuelve 100 registros (uno por cada trabajador) con toda la información que necesitamos para nuestro informe:
Nuestro objetivo ahora será el de desarrollar una consulta idéntica a ésta, pero con notación LINQ, dentro de un repositorio de Entity Framework . Como contenedor de datos vamos a crear una clase DTO, que será el resultado tanto de nuestro ejemplo con ADO.NET como con EF.
namespace ZmLabsObjects.DTO { public class InformeAbsentismoDTO { public Guid Trabajador { get; set; } public int TotalLaborales { get; set; } public int DiasRegistrados { get; set; } public int HorasTrabajadas { get; set; } public int HorasNoTrabajadas { get; set; } public int DiasNoTrabajados { get; set; } public int PctAbsentismo { get; set; } } }
Veamos como quedaría nuestra SELECT con LINQ:
public List<InformeAbsentismoDTO> GetInformeAbsentismoAnual (int anho) { List<InformeAbsentismoDTO> res = new List<InformeAbsentismoDTO>(); using (var db = new context.LabsContext(_str_cnx)) { res = db.ParteHoras .Where(an => an.Fecha.Year == anho) .GroupBy(ph => ph.Trabajador) .Select(inf => new InformeAbsentismoDTO { Trabajador = inf.FirstOrDefault().Trabajador, TotalLaborales = inf.Count()*8, DiasRegistrados = inf.Count(), HorasTrabajadas = inf.Sum(h => h.Horas), HorasNoTrabajadas =(inf.Count()*8) - inf.Sum(h => h.Horas), DiasNoTrabajados = ((inf.Count() * 8) - inf.Sum(h => h.Horas)) / 8, PctAbsentismo = (((inf.Count() * 8) - inf.Sum(h => h.Horas)) * 100) / (inf.Count() * 8), }).ToList(); } return res; }
Comprobemos que los resultados de las consultas SQL y LINQ son los mismos:
¡Perfecto!
Con esta primera versión de nuestro informe de absentismo, señores, estamos en disposición de montar el¡¡ Test 4 !! Ya conocen Uds. sobradamente el mecanismo para montar un nuevo test en la plataforma, por lo que no voy a repetirlo en esta ocasión. Si tienen alguna duda al respecto no duden en contactar conmigo a través de los comentarios y haré lo posible e imposible por ayudarles.
Vean como queda el código de nuestros dos nuevos testcases en la clase test4_sql_querying:
private void InformeAbsentismo_EF(ref TestCasesDomain _testcase) { try { _testcase.TestCaseExecution.idTestCase = _testcase.id; //registra inicio _testcase.TestCaseExecution.dtBegin = DateTime.Now; InitTestCase(_testcase.Function, _testcase.TestCaseExecution.dtBegin); //ejecuta testcase var res = _phfunctions.GetInformeAbsentismoEF(2020, new ZMLabsData.repos.sqltest_repos_partehoras(DataFunctions.GetLabsCnx())); //registra fin _testcase.TestCaseExecution.dtEnd = DateTime.Now; EndTestCase(_testcase.Function, _testcase.TestCaseExecution); } catch (Exception ex) { SetMsg("Error ejecutando InformeAbsentismo_EF"); _logger.Error(ex, "Error ejecutando InformeAbsentismo_EF"); } } private void InformeAbsentismo_ADO(ref TestCasesDomain _testcase) { _testcase.TestCaseExecution.idTestCase = _testcase.id; //registra inicio _testcase.TestCaseExecution.dtBegin = DateTime.Now; InitTestCase(_testcase.Function, _testcase.TestCaseExecution.dtBegin); //ejecuta testcase var res = _phfunctions.GetInformeAbsentismoADO(2020, new ZMLabsData.ADO.data_test_partehoras(DataFunctions.GetLabsCnx())); //registra fin _testcase.TestCaseExecution.dtEnd = DateTime.Now; EndTestCase(_testcase.Function, _testcase.TestCaseExecution); }
Y veamos también el resultado de un par de ejecuciones:
Demasiado fácil. Unos 200 milisegundos para EF y unos 10 para ADO. Poca diferencia. Estos tiempos lo que nos dicen es que la máquina resuelve esta consulta sin problema aparente. Pero no nos vamos a quedar aquí amigos.
Podríamos decir que casi todas las consultas de un sistema de software en producción, ejecutadas de forma aislada, en un entorno sin estrés, nos devolverán resultados aceptables. La cosa se complica cuando son muchos usuarios concurrentes solicitando informaciones. Bien, pues trataremos de simular ese estrés de modo que en lugar de ser un único usuario el que consulta el informe de absentismo, sea todo un departamento de recursos humanos.
En primer término adaptaremos nuestra capa de negocio para poder hacer una solicitud de varios años del informe:
//Informe de absentismo ADO y EF public List<InformeAbsentismoDTO> GetInformeAbsentismoADO(List<int> anhos, IParteHorasRepository Repository) { List<InformeAbsentismoDTO> res = new List<InformeAbsentismoDTO>(); List<InformeAbsentismoDTO> parcial; foreach (int _anho in anhos) { parcial = Repository.GetInformeAbsentismoAnual(_anho); res.AddRange(parcial); } return res; } public List<InformeAbsentismoDTO> GetInformeAbsentismoEF(List<int> anhos, IParteHorasRepository Repository) { List<InformeAbsentismoDTO> res = new List<InformeAbsentismoDTO>(); List<InformeAbsentismoDTO> parcial; foreach (int _anho in anhos) { parcial = Repository.GetInformeAbsentismoAnual(_anho); res.AddRange(parcial); } return res; }
Para simular peticiones más o menos simultáneas de varios usuarios haremos lo siguiente: en los métodos InformeAbsentismo_EF e InformeAbsentismo_ADO crearemos tres oleadas de peticiones, la primera realizará 5 peticiones del año 2020, la segunda 5 peticiones de los años 2019 y 2020 y la tercera 5 peticiones de los años 2018, 2019 y 2020. Como punto de partida crea que son cifras aceptables. Así queda el método InformeAbsentismo_EF:
private void InformeAbsentismo_EF(ref TestCasesDomain _testcase) { try { _testcase.TestCaseExecution.idTestCase = _testcase.id; //registra inicio _testcase.TestCaseExecution.dtBegin = DateTime.Now; InitTestCase(_testcase.Function, _testcase.TestCaseExecution.dtBegin); //EJECUTA TESTCASE //----------------------------------------------------------------------------------------- //Primera oleada de peticiones (5 peticiones - 1 año) for (int cont = 0; cont < 5; cont++) { List<int> anhossolicitados = new List<int>() { 2020 }; Thread thPeticiones = new Thread(() => Hilo_Peticiones_EF(cont, anhossolicitados)); _lst_process_control.Add(new objects.process_control() { Estado = objects.process_control.enumEstadoProceso.Ejecutando, Hilo = thPeticiones }); Thread.Sleep(22); cont++; } //Segunda oleada de peticiones (5 peticiones - 2 años) for (int cont = 0; cont < 5; cont++) { List<int> anhossolicitados = new List<int>() { 2019, 2020 }; Thread thPeticiones = new Thread(() => Hilo_Peticiones_EF(cont+5, anhossolicitados)); _lst_process_control.Add(new objects.process_control() { Estado = objects.process_control.enumEstadoProceso.Ejecutando, Hilo = thPeticiones }); Thread.Sleep(22); cont++; } //Tercera oleada de peticiones (5 peticiones - 3 años) for (int cont = 0; cont < 5; cont++) { List<int> anhossolicitados = new List<int>() { 2018, 2019, 2020 }; Thread thPeticiones = new Thread(() => Hilo_Peticiones_EF(cont + 10, anhossolicitados)); _lst_process_control.Add(new objects.process_control() { Estado = objects.process_control.enumEstadoProceso.Ejecutando, Hilo = thPeticiones }); Thread.Sleep(22); cont++; } //Inicia los hilos foreach (var _listapetis in _lst_process_control) { _listapetis.Hilo.Start(); } //Controla el final de los hilos while (_lst_process_control.Exists(pc => pc.Estado == objects.process_control.enumEstadoProceso.Ejecutando)) { Thread.Sleep(55); } //registra fin _testcase.TestCaseExecution.dtEnd = DateTime.Now; EndTestCase(_testcase.Function, _testcase.TestCaseExecution); } catch (Exception ex) { SetMsg("Error ejecutando InformeAbsentismo_EF"); _logger.Error(ex, "Error ejecutando InformeAbsentismo_EF"); } }
Cada uno de los hilos llaman a la función privada Hilo_Peticiones_EF que les muestro a continuación:
private void Hilo_Peticiones_EF(int index, List<int> anhos) { try { for (int cont = 0; cont < 5; cont++) { var res = _phfunctions.GetInformeAbsentismoEF(anhos, new ZMLabsData.repos.sqltest_repos_partehoras(DataFunctions.GetLabsCnx())); Thread.Sleep(200); } _lst_process_control[index].Estado = objects.process_control.enumEstadoProceso.Finalizado; } catch (Exception ex) { _testexec.SetMsg("Error INESPECÍFICO en Hilo_Peticiones_EF - Hilo nº " + index.ToString()); _logger.Error(ex, "Hilo_Peticiones_EF - Index " + index.ToString()); _lst_process_control[index].Estado = objects.process_control.enumEstadoProceso.Erroneo; } }
Bien, así en total disponemos de (5*3)+(5*2)+5 = 30 peticiones para ADO y 30 para EF. ¿Qué más? Bueno, antes de poder probar estas nuevas complejidades, hemos de tener en cuenta que actualmente en nuestra base de datos solo disponemos de información para el año 2020 (registros que se quedan grabados tras la ejecución del TEST3). Debemos generar registros para el año 2018 y 2019, para lo cual debemos modificar ligeramente el código del Test3 en su método Start(), ya que el año 2020 se encuentra hardcodeado.
List<ParteHorasDomain> parteTotal = new List<ParteHorasDomain>(); parteTotal.AddRange(_phfunctions.Generate(100, 2020)); _phfunctions.Clear(); parteTotal.AddRange(_phfunctions.Generate(100, 2019)); _phfunctions.Clear(); parteTotal.AddRange(_phfunctions.Generate(100, 2018));
Fácil. Ahora disponemos de 78.400 registros en la tabla test.ParteHoras. Implementamos las mismas oleadas de peticiones para la parte ADO.NET y lanzamos de nuevo un par de ejecuciones de los test:
Una manera interesante es usar la utilidad SQL Server Profiler, que nos devuelve una traza de todos los eventos que están ocurriendo en nuestra base de datos. vean:
La interpretación es un poco engorrosa, pero la información que devuelve es de gran utilidad. La línea que está marcada en la captura es el INSERT de la ejecución en base de datos. Por lo tanto nos marca el final del testcase 1. Nos interesan las columnas con el número de lecturas y escrituras, y la duración del proceso. Además en la columna TextData nos dice qué instrucción se está ejecutando. Para el caso de Entity Framework esto es bastante interesante, ya que podemos ver cómo nuestra instrucción LINQ se transforma en una SQL, no siempre todo lo bonita que nos gustaría:
Y cuando finalmente voy a buscar las ejecuciones del procedimiento almacenado, me encuentro que todas las ejecuciones del testcase 2 son idénticas a las del testcase 1. ¿Cómo es esto posible? Pues se lo voy a decir: error humano. Tomar la precaución de usar esta herramienta me ha servido para darme cuenta de que en los dos testcases estaban ejecutando el mismo código de datos, el correspondiente a Entity Framework. ¡Por eso los tiempos se igualaban! Sepan que el error ha sido completamente fortuito, nada intencionado. Sobre la marcha nos encontramos con dificultades y juntos las vamos resolviendo. Localizo el error, lo corrijo y vuelvo a ejecutar:
El error está aquí, en la inyección del repositorio. La línea corregida en el método Hilo_Peticiones_ADO quedaría así:
var res = _phfunctions.GetInformeAbsentismoADO(anhos, new ZMLabsData.ADO.data_test_partehoras(DataFunctions.GetLabsCnx()));
Las dos nuevas ejecuciones nos devuelven resultados bien distintos:
Vamos de nuevo a ejecutar una traza con SQL Server Profiler para comprobar que esta vez sí salió todo como esperábamos:
Esto es otra cosa. Resaltado en amarillo está el final del testcase1 y comienzo del testcase2. Y aquí ya encontramos las llamadas a nuestro procedimiento GetEstadisticasAbsentismo. Además podemos apreciar que los tiempos que nos arroja nuestra herramienta de test son coherentes con los tiempos de ejecución que nos da Profiler.
Ya nos hemos asegurado de que efectivamente se realizan las peticiones que hemos programado. Y dada la gran diferencia de tiempos, ¿podemos ir extrayendo ciertas conclusiones? No, aun no.
Aunque ADO está resolviendo sus 30 peticiones en poco más de un segundo, me pregunto si en lugar de 30 peticiones fueran 300, ¿se vería nuestro sistema de parte de horas muy resentido?
Bueno, desde el principio del post tengo en la cabeza algunas mejoras que deberíamos aplicar a nuestras consultas. Las mismas deberían afectar positivamente tanto a las consultas con EF como a las consultas con ADO. Comprobémoslo.
Mejora 1: Campos calculados
Si se fijan bien en el resultado de nuestro informe, todos los campos se calculan en base a dos valores: la suma de las horas, y el conteo de los registros afectados. Entonces, ¿porqué pedirle a la base de datos que calcule todos los campos? Lo correcto sería que la base de datos (tanto en EF como en ADO) devuelva los dos valores necesarios, y que el resto de cálculos se hagan en nuestra capa de negocio, que para eso está. Aplicaremos cambios en el procedimiento almacenado (por consiguiente también en la capa de datos ADO), en la consulta LINQ y finalmente, en el DTO, que será donde introduciremos los cálculos tal como les voy a mostrar:/* exec test.GetEstadisticasAbsentismo 2020 exec test.GetEstadisticasAbsentismo 2019 exec test.GetEstadisticasAbsentismo 2018 */ ALTER PROCEDURE [test].[GetEstadisticasAbsentismo] @anho int as begin select Trabajador, COUNT(1) as conteo_registros, SUM(Horas) as suma_horas from [test].[ParteHoras] where YEAR(Fecha) = @anho group by Trabajador end
public List<InformeAbsentismoDTO> GetInformeAbsentismoAnual(int anho) { List<InformeAbsentismoDTO> res = new List<InformeAbsentismoDTO>(); SqlConnection cnx = new SqlConnection(str_cnx); SqlCommand cmd = new SqlCommand(); cmd.Connection = cnx; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "test.GetEstadisticasAbsentismo"; cmd.Parameters.AddWithValue("@anho", anho); cnx.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { InformeAbsentismoDTO _infline = new InformeAbsentismoDTO() { Trabajador = Guid.Parse(reader["Trabajador"].ToString()), conteo_registros = int.Parse(reader["conteo_registros"].ToString()), suma_horas = int.Parse(reader["suma_horas"].ToString()) //TotalLaborales = int.Parse(reader["total_horas_laborales"].ToString()), //DiasRegistrados = int.Parse(reader["total_dias_registrados"].ToString()), //HorasTrabajadas = int.Parse(reader["horas_trabajadas"].ToString()), //HorasNoTrabajadas = int.Parse(reader["horas_no_trabajadas"].ToString()), //DiasNoTrabajados = int.Parse(reader["dias_no_trabajados"].ToString()), //PctAbsentismo = int.Parse(reader["pct_absentismo"].ToString()) }; res.Add(_infline); } cnx.Close(); return res; }
public List<InformeAbsentismoDTO> GetInformeAbsentismoAnual (int anho) { List<InformeAbsentismoDTO> res = new List<InformeAbsentismoDTO>(); using (var db = new context.LabsContext(_str_cnx)) { res = db.ParteHoras .Where(an => an.Fecha.Year == anho) .GroupBy(ph => ph.Trabajador) .Select(inf => new InformeAbsentismoDTO { Trabajador = inf.FirstOrDefault().Trabajador, conteo_registros = inf.Count(), suma_horas = inf.Sum(h => h.Horas) }).ToList(); //res = db.ParteHoras // .Where(an => an.Fecha.Year == anho) // .GroupBy(ph => ph.Trabajador) // .Select(inf => new InformeAbsentismoDTO // { // Trabajador = inf.FirstOrDefault().Trabajador, // TotalLaborales = inf.Count()*8, // DiasRegistrados = inf.Count(), // HorasTrabajadas = inf.Sum(h => h.Horas), // HorasNoTrabajadas =(inf.Count()*8) - inf.Sum(h => h.Horas), // DiasNoTrabajados = ((inf.Count() * 8) - inf.Sum(h => h.Horas)) / 8, // PctAbsentismo = (((inf.Count() * 8) - inf.Sum(h => h.Horas)) * 100) // / (inf.Count() * 8), // }).ToList(); } return res; }
Bastante más elegante ¿no creen? Y el broche de oro de la elegancia lo ponemos en el DTO de esta manera:
public class InformeAbsentismoDTO { public Guid Trabajador { get; set; } public int TotalLaborales { get => conteo_registros * 8; } public int DiasRegistrados { get => conteo_registros; } public int HorasTrabajadas { get => suma_horas; } public int HorasNoTrabajadas { get => (conteo_registros * 8) - suma_horas; } public int DiasNoTrabajados { get => HorasNoTrabajadas / 8; } public int PctAbsentismo { get => ((TotalLaborales - HorasTrabajadas) * 100) / TotalLaborales; } //resultados parciales obtenidos de BBDD public int conteo_registros { get; set; } public int suma_horas { get; set; } }
Noten que añadimos aquí las propiedades conteo_registros y suma_horas para poder recuperar estas informaciones de la base de datos, y calculamos el resto de propiedades en base a estos dos valores. Las propiedades del informe quedan en modo solo lectura y además, no se ejecutará el cálculo hasta el momento en que se requiera. Y no ganamos solo en performance, sino también en legibilidad.
Como resultado de estas modificaciones obtenemos con EF unos tiempos de en torno a 25 segundos, una mejora bastante significativa. Los tiempos con ADO permanecen parecidos.
Pero no nos conformamos.
Mejora 2: Diseño
Una muy apreciada jefa mía solía decir: "el demonio está en los detalles". Y así es. De modo que hoy nos proponemos hilar fino. Muy fino.Habrán observado que para obtener el informe del año adecuado usamos YEAR(Fecha) = @anho en el procedimiento almacenado, y .Where(an => an.Fecha.Year == anho) en LINQ. ¿Y si pudiéramos ahorrarnos ejecutar una función sobre una fecha para cada uno de los registros? Sería tan sencillo como añadir un campo entero en la tabla test.ParteHoras, y dejar el año del registro pre-grabado. Vamos a ello.
Añadir el campo es BB.DD es tan simple como tocar el modelo de E.F. y volver a ejecutar la aplicación. Al momento se crea nuestro nuevo campo en BBDD:
[Table("ParteHoras", Schema = "test")] public class ParteHorasModel { public Int64 id { get; set; } [Required] public Guid Trabajador { get; set; } [Required] public DateTime Fecha { get; set; } [Required] public Int16 Horas { get; set; } [Required] public int Anho { get; set; } }
Ahora para alimentar este campo en la clase de dominio añadimos igualmente el campo:
public class ParteHorasDomain { public enum enumTipoJornada { Normal, Baja, Incidencia }; public Guid Trabajador { get; set; } public DateTime Fecha { get; set; } public Int16 _horas; public Int16 Horas { get => _horas; set { _horas = value; if (Horas == 0) { this.TipoJornada = enumTipoJornada.Baja; } else if (Horas == 8) { this.TipoJornada = enumTipoJornada.Normal; } else { this.TipoJornada = enumTipoJornada.Incidencia; } } } public int Anho { get => Fecha.Year; } public enumTipoJornada TipoJornada { get; set; } }
Con Entity Framework el nuevo campo queda automáticamente alimentado. En cambio con ADO tenemos que añadir manualmente el campo, en la capa de datos, y en el procedimiento almacenado. Como ven cada mecanismo tiene sus ventajas y sus inconvenientes. Para más INRI como este es un INSERT masivo, debería modificar el tipo definido por el usuario de tipo tabla. Por suerte, como es un campo calculado, en este caso me puedo evitar este trastorno simplemente tocando el procedimiento almacenado de la siguiente forma:
create or ALTER procedure [test].[insertParteHoras] @ParteHoras test.tblParteHoras readonly as begin insert into [test].[ParteHoras] ([Trabajador], [Fecha], [Horas], Anho) select [Trabajador], [Fecha], [Horas], YEAR([Fecha]) from @ParteHoras end
Toca comprobar que la carga de datos con el nuevo campo funciona correctamente con ambos mecanismos, y que el nuevo campo queda informado
Ya casi estamos terminando. Volvemos a tener nuestros 78.400 registros en la tabla, esta vez con el campo Anho informado, así que me dispongo a cambiar las condiciones de las consultas.
CREATE or ALTER PROCEDURE [test].[GetEstadisticasAbsentismo] @anho int as begin select Trabajador, COUNT(1) as conteo_registros, SUM(Horas) as suma_horas from [test].[ParteHoras] where Anho = @anho group by Trabajador end
res = db.ParteHoras .Where(an => an.Anho == anho) .GroupBy(ph => ph.Trabajador) .Select(inf => new InformeAbsentismoDTO { Trabajador = inf.FirstOrDefault().Trabajador, conteo_registros = inf.Count(), suma_horas = inf.Sum(h => h.Horas) }).ToList();
Todo listo para volver a ejecutar el TEST4. ¿Creen que un cambio tan minúsculo podrá tener alguna repercusión? Comprobemos:
Pues otros pocos segundos le hemos podido arañar a las consultas con Entity Framework, mientras que los resultados consultando con ADO.NET se mantienen estables, lo cual no deja de ser buena señal. Noten que, en el caso de EF, hemos pasado de 40 segundos en nuestras primeras tentativas, a poco más de 20 segundos en esta última ejecución. Ahora extrapolen esto a un sistema en producción con cientos, o miles de usuarios. Sí amigos, el demonio está en los detalles.
Llegados a este punto uno podría pensar que hemos alcanzado la perfección, que no puede mejorarse más, ¿cierto? ¡Nunca! Siempre puede mejorarse un poco más.
Mejora 3: Revisando los planes de ejecución
Vamos a aplicar una tercera y última mejora. Uds. habrán oído sobre los índices de base de datos. Una herramienta fundamental y determinante para mejorar sus consultas.Puesto que esta publicación ha quedado ya bien nutrida, voy a ir rápido, pero sepan que el impacto de un índice adecuado en el lugar adecuado es CLAVE.
Está claro que una consulta de estas características se vería beneficiada por un índice en la tabla ParteHoras. Por regla general hay que hacer índices sobre los criterios de búsqueda (WHERE) e incluir (con mucho cuidado) los campos resultantes (SELECT) en la sección INCLUDE del índice. Pero hemos de ir con cuidado ya que los índices (i) tienen repercusión negativa en la performance de INSERTS y UPDATES, y (ii) ocupan espacio.
Para ir sobre seguro vamos a revisar el plan de ejecución de nuestro procedimiento almacenado, una práctica totalmente aconsejable. El mismo motor SQL se dará cuenta de que esta consulta funcionaría mejor con un índice, y nos lo sugerirá:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [test].[ParteHoras] ([Anho]) INCLUDE ([Trabajador],[Horas])
El índice que nos propone SQL es totalmente coherente de modo que le doy un nombre (IX_Absentismo) y me dispongo a crearlo ejecutando el script. A continuación evaluaremos su impacto volviendo a ejecutar, ya sí por última vez, el TEST4:
¿Mi conclusión personal? I LOVE THIS GAME.
Espero tener ocasión en próximas publicaciones, de profundizar en el tema de las trazas (profiler), los índices y los planes de ejecución, ya que son herramientas de enorme potencia.
Les anuncio que estoy trabajando en algo muy, muy interesante que pronto estaré en disposición de compartir con Uds. Aquí les dejo una intrigante pista. Amigos, les deseo que todo compile bien en sus vidas: salud, y prosperidad.
Comentarios
Publicar un comentario