Fondo

Los totales corrientes han sido durante mucho tiempo el núcleo de la mayoría de los sistemas financieros, declaraciones o incluso cálculos de saldo en un momento dado. Ahora no es lo más difícil de hacer en SQL Server, pero definitivamente no es la cosa más rápida en el mundo, ya que cada registro tiene que ser evaluado por separado. Antes de SQL Server 2012, se tenía que definir manualmente la ventana / subconjunto en el que deseaba calcular el total de la ejecución, normalmente definiríamos un número de fila con una ventana en un pedido específico o un cliente, dependiendo de los requisitos actuales.

Discusión

Me encargaron de reescribir un pedazo de código antiguo de C # que tenemos en nuestro sistema en SQL, ya que ya no funcionaba de manera óptima. Los requisitos de datos crecieron demasiado rápido para que el sistema pudiera mantenerse al día. El pedazo de código era responsable de generar declaraciones transaccionales que saldrían a clientes cada noche a petición. Uno de los requisitos, en la declaración, era construir un total de ejecución en la declaración para facilitar a los clientes para conciliar sus transacciones en cuestión en un momento dado. Así que comencé a mirar mis opciones y encontré que hay un par de maneras de crear totales en ejecución en SQL Server, así que empecé a probar todos los métodos que se me ocurrían y buscar soluciones online. Con el rendimiento en mente, traté de asegurarme de tener la solución mejor/más rápida para mi problema. Sé por experiencias anteriores que el uso de funciones de ventanas para ayudar con estos cálculos suele ser el más rápido, pero tenía dos grandes problemas, ya que la primera parte de la declaración se ejecuta en SQL Server 2008 R2 y el segundo objetivo que tenía en mente es ejecutar esto en nuestro APS (puede leer más sobre lo que es un APS y cómo funciona aqui).

Consideraciones

Antes de ejecutar SQL Server 2012, hay que tomar en cuenta que un total no es una cosa fácil de hacer en SQL. No es difícil pero es lento. Siempre pruebe todo en un ambiente de prueba o un ambiente de prueba de aceptación de usuarios si tiene la suerte de tener ambientes de prueba adecuados.

Requisitos previos

1. SQL Server 2008+ o SQL Server 2012+ (si desea utilizar el método windowed)
2. Base de datos de ejemplo de AdventureWorks (si desea seguir los ejemplos del artículo)

Objetivo

Estaremos usando la base de datos de ejemplo de AdventureWorks para utilizar algunos métodos para crear totales en ejecución en SQL Server y al hacer esto, miraremos lo que sería mejor para nuestro entorno.

Solución

Mi primer idea fue crear una subconsulta para crear mi total de ejecución, ya que tendría sentido para calcular el valor del total de ejecución en el tiempo de ejecución.

Ahora, como podemos ver en la captura de pantalla anterior del conjunto de resultados de este trabajo funciona a la perfección, pero no puedo mirar sólo una opción, ya que tengo que asegurarse de que está lo más optimizado posible para garantizar la continuidad del negocio. Entonces, ¿qué pasa si hacemos un self join y unimos la tabla a sí misma con un offset y luego utilizamos esto para conseguir un total de trabajo que sería más rápido ya que puedo controlar la indexación de mejor manera para asegurar el rendimiento siendo mejor que el método de subconsulta.

Así que vamos a ver cómo funciona esto y ver que debería ser más eficaz.

Y lo que usted sabe, funciona y el conjunto de resultados es exactamente el mismo que habríamos esperado. La gran pregunta ahora es, ¿es realmente mejor que el método de subconsulta? Así que vamos a verificar el plan de ejecución y comparar los dos juntos.

Observando sólo los porcentajes relativos a los lotes, podemos ver inmediatamente que el método de subconsulta usó el 54% de los recursos de lote y el self join sólo el 46%. Así que ahora podemos ver si podemos mejorar esto aún más.

Estaba pensando en cómo podría utilizar la naturaleza recursiva de un CTE para crear un total de ejecución. Por lo tanto, el siguiente código utilizará un CTE recursivo para añadir el “LineTotal” a sí mismo todo el tiempo.

Y de nuevo obtuvimos los mismos resultados, pero sólo que esta vez si echamos un vistazo al plan de ejecución de los tres métodos que hemos probado vemos algo muy interesante.

Ahora podemos ver que el CTE recursivo es mucho más rápido que los otros dos métodos tan claramente de tal forma que tenemos un ganador. ¿Realmente lo tenemos? En primer lugar, para permitir que el CTE se utilice de esta manera, tenemos que aumentar la opción MAXRECURSION para esta consulta, y el máximo al que se le permite establecerlo es 32767. En mi caso de uso, nunca me acercaría a este número con ninguna declaración aún si esta tuviera muchos registros, pero si está ejecutando grandes cantidades de datos no podría usted continuar con este método. Y en segundo lugar, cuando lo probé en nuestro APS, me riñe el sistema por siquiera pensar que puedo hacer un CTE recursivo en él. Así que en mi entorno de producción en SQL Server 2008 R2 puedo usar este método, pero en nuestro entorno de almacenamiento de datos, necesito usar uno de los otros dos métodos.

Ahora, si tienes la suerte de ejecutar SQL Server 2012+ (como la mayoría de nosotros debería estar haciendo), Microsoft mejoró en las funciones de Windowed y por lo tanto tenemos un método muy superior que podemos usar. El uso de funciones con ventanas en este modo es más rápido y más fácil de leer cuando tiene que entregar su código a otra persona para que le de soporte o cambie.

Si echamos un vistazo al plan de ejecución ahora, sólo comparando el CTE recursivo con el método de función Windowed, podemos ver claramente que el CTE recursivo no coincide con la función Windowed.

Observando el relativo al lote nuevamente podemos ver que el CTE recursivo ahora usa el 91% comparado con el método de ventana del 9%.

Conclusiones finales

Si conoce bien sus datos y el requisito, utilice el método CTE recursivo en entornos pre-SQL Server 2012 y las funciones Windowed en SQL Server 2012 y posteriores. También creo que un CLR podría ser útil en este escenario, pero mis habilidades en C# estan algo oxidadas en este momento. Tal vez con la ayuda de la comunidad, pueda probar esta idea.

Referencias

• Funciones de ventanas
• CTE
• Self Joins
• APS

• Acerca de
• Últimas entradas

Jean-Pierre Voogt

Jean-Pierre es un desarrollador de SQL y de un equipo de análisis de datos líder de Sudáfrica. Es MCSA (Microsoft Certified Solutions Associate) y propietario de una licenciatura en Ingeniería de Software con gran experiencia en diseño de bases de datos, data warehousing y desarrollo. Él tiene una gran pasión por SQL Servery le gusta resolver problemas de negocios complejos.

Jean-Pierre habla en el Grupo de Usuarios de SQL de Johannesburgo, tratando de brindar ayuda a la comunidad de SQL tanto como sea posible. Le encanta jugar con SQL Server y ver cómo puede abordar un problema con una perspectiva diferente.

Ver todas las entradas de Jean-Pierre Voogt

Últimas entradas de Jean-Pierre Voogt (ver todo)

• Ejecución de totales en ejecución en SQL Server - June 2, 2017