Cómo medir cuánto tarda una consulta en actualizarse en Power Query con las Opciones de Dignóstico.
En esta píldora de Power Query veremos cómo medir el rendimiento de una consulta cuando se actualiza, es decir, conocer el tiempo que tarda esta acción, y así saber si son: 3 segundos, 9 segundos, 5 minutos, etc.
Antes de entrar en materia te quiero poner en contexto de lo que ha sucedido, resulta que tenemos un vídeo el cual lleva por nombre: La FORMA IDEAL de Remover Columnas Vacías en POWER QUERY, que tiene la particularidad de que necesita transponer la tabla como paso intermedio para poder ejecutar la eliminación de columnas vacías dinámicamente (los métodos clásico de remover o elegir directamente son estáticos y no recomendable si sabemos que las columnas vacías pueden cambiar de posición en futuras actualizaciones de los datos).
Resulta que en ese vídeo Juan Carlos Guevara comenta que no está de acuerdo con el método, veamos:
De hecho, estoy de acuerdo con Juan Carlos, porque en ese vídeo en la parte final precisamente se comento que para volúmenes de datos considerables el método no es factible.
Aprovechando estas anotaciones vamos aprender a medir cuánto tiempo tarda una consulta en ejecutarse para actualizar, así procedemos a crear varios métodos para remover columnas vacías dinámicamente y seleccionar la que mejor se ajuste según el escenario particular.
A continuación, puedes leer el paso a paso con ilustraciones o ver el vídeo aquí, según tu formato para consumir el contenido preferido.
Opciones de Diagnóstico de la Consulta
Dado que la función se apoya de las opciones de Diagnóstico, debemos corroborar que si tenemos la funcionalidad habilitada, para ello, hacemos lo siguiente: Pestaña: File, Sección: Opciones y Configuración, Comando: Opciones, en la ventana que aparece ubicamos en la parte derecha la Sección: Diagnóstico, para finalmente en la parte central de la ventana cerciorarnos que en la parte: Diagnóstico de la consulta, la segunda opción: Habilitar en el editor de consultas (no requiere que se ejecute como administrador) sea la opción seleccionada:
Por otro lado, si lo único que vamos es calcular el tiempo que tarda la consulta es buena idea en Nivel de Diagnóstico dejar habilitado únicamente Agregado. OJO: Las demás opciones son muy potentes y nos permite indagar mucho más sobre el proceso interno, pero es buena práctica activar y/o desactivar las demás de acuerdo al análisis que se vaya a ejecutar.
Cómo Obtener Datos de Diagnóstico de una Consulta
Para determinar cuánto tiempo tarda una consulta, por ejemplo, una denominada: Datos, primero vamos a la pestaña Herramientas y pulsamos clic en el comando Iniciar diagnóstico.
Luego, seleccionamos en el Panel de Consultas la consulta que se quiere evaluar (para el caso Datos), después vamos a la pestaña Inicio y pulsamos clic en el comando Actualizar Vista Previa, una vez detectamos que ha finalizado la actualización volvemos a la pestaña Herramientas para pulsar clic en el comando Detener diagnósticos.
Con ello se genera una nueva carpeta con nombre Diagnóstico, allí dependiendo del nivel de diagnóstico configurado, podemos observar una o más consultas, no obstante, debemos buscar la que lleva por nombre: Diagnostics_Aggregated …. o la palabra Aggegated en alguna parte con el nombre de la consulta y la hora
No obstante, el resultado del diagnóstico tiene muchos detalles que, por supuesto se puede exprimir para conocer detalles de la ejecución y otros aspectos, es decir, el tiempo que tardo la consulta en ejecutarse no viene compactado y listo para ser visualizado.
Cómo Obtener el Tiempo Que Tardo la Consulta.
Aunque es bastante sencillo obtener el tiempo total de la consulta, dado que es tomar la columna Exclusive Duration en la tabla de agregado del diagnóstico y determinar el tiempo, no obstante, si lo vamos a hacer constantemente es mejor tener una función personalizada (y mejor aún integrada en la librería estándar) que nos permita obtener ese resultado bastante rápido y eliminar retrabajos.
La buena noticia es que tenemos la función Record.QueryExecutionTimeFromDiagnostic en Power Query Zone lista para ser importada y utilizarla, puedes obtenerla aquí.
Sino no sabes como llevar una función personalizada de Power Query Zone a tu proyectos puedes ver este vídeo e incluso Cómo Convertir Tu Función a Nativa en Power Query y Lenguaje M en este vídeo.
Con la función Record.QueryExecutionTimeFromDiagnostic integrada, en ella vamos a la lista desplegable de su parámetro y seleccionamos la tabla agregada:
Pulsamos clic en el botón Invocar y observaremos el resultado desglosado en Días, Horas, Minutos y segundos en un registro (record):
Como podemos notar para el escenario la consulta tardo 0,1387 segundo, en otras palabras, 370 ms.
Corroborando la Veracidad de la Función (Ejemplo 2)
Para corrobora la precisión de la función personalizada Record.QueryExecutionTimeFromDiagnostic creemos una consulta en blanco con el siguiente código del lenguaje M:
El script #1 utiliza la función Function.InvokeAfter, la cual invoca o ejecuta un valor de función proporcionado en en su primer argumento después de un tiempo de espera establecido como valor de duración en el segundo argumento.
Para el ejemplo se utiliza una función bien sencilla: ()=> «Hola Mundo», que la ser invocada arroja el texto «Hola Mundo», mientras que el tiempo de espera para ejecutarse ses de: 3 segundos que se señalan con el apoyo de la función intrínseca: #duration así # duration ( 0, 0, 0, 1).
Repitiendo el procedimiento con el diagnóstico de la consulta y luego calculando el tiempo de ejecución con la función personalizada Record.QueryExecutionTimeFromDiagnostic, tenemos:
Que confirma la precisión y veracidad de la función, los 0,013 segundos excedentes son por otros temas internos del mashup engine, sin embargo, es algo mínimo en general.
Si quieres otra prueba puedes variar en la función #duration el tiempo, por ejemplo 1 minuto, sería:
Cuyo resultado es:
No es la única manera que tenemos para medir el rendimiento de una consulta, Chris Webb a expuesto como hacerlo modificando el código M aquí, también como hacerlo con process monitor aquí e incluso con SQL profiler aquí.
Por favor, comenta y regálame tus puntos de vista y feedback y correcciones. Estaré leyendo y respondiendo todos los comentarios activamente
Cordialmente
— Miguel Caballero