Como lograr realizar un top de manera eficiente en una consulta SQL Server: Optimizacion de consultas top n en grupos de filas

Optimizacion de consultas top n

En Optimizacion de consultas top n.una problema puede tener varias soluciones, en una consulta ocurre lo mismo, sin embargo no todas las soluciones son las mas optimas, cuando se trata de datos siempre debemos buscar el mejor enfoque para tener el mejor rendimiento, una solución no proporcionará el mejor rendimiento en todos los casos. Varios factores pueden influir en qué solución funciona mejor, incluida la densidad de datos, la disponibilidad de índices, etc. Para decidir sobre una solución, debe conocer sus datos, comprender cómo funciona el optimizador de SQL Server y determinar si vale la pena agregar índices importantes en términos de impacto general en el sistema.

 

En este artículo presentaremos un problema,posteriormente mostraremos tres enfoques para resolver el ejercio planteado y analizaremos cual solución funcionaría mejor.

Problema a resolver

El ejercicio se conoce comúnmente como Top N Per Group. La idea es devolver un número superior de filas para cada grupo de filas. Supongamos que tienen una tabla llamada Orders con atributos llamados orderid, custid, empid, shipperid, orderdate, shipdate y filller (que representan otros atributos). También tiene tablas relacionadas llamadas Customers, Employees, and Shippers. Debe escribir una consulta que devuelva las N órdenes más recientes por cliente (o empleado o remitente). “Más reciente” está determinada por la fecha de orden descendente, Escribir una solución que funcione no es difícil; el desafío es determinar la solución óptima según los factores variables y lo que es específico para su caso en su sistema. como dice el dicho, todos los caminos conducen a roma, el como se llege, y sobre todo el tiempo en que se llegue, determinara lo eficiente que podemos ser, cuando hablamos de eficienca es donde elegir el camino correcto se complica 

Además, suponiendo que puede determinar la estrategia de índice óptima para respaldar su solución, debe considerar si está autorizado y si puede permitirse crear dicho índice. A veces, una solución funcionaría mejor si el índice óptimo para soportarla estuviera disponible, pero otra solución funcionaría mejor sin dicho índice. En nuestro caso, la densidad de la columna de partición (por ejemplo, custid, si necesita devolver las filas superiores por cliente) también juega un papel importante para determinar qué solución es óptima.

Optimizacion de consultas top n

Codigo 1:Datos de muestra

Ejecute el siguiente código  para crear una base de datos de muestra llamada TestTOPNSQListo y dentro de ella una función auxiliar llamada GetNums. La función auxiliar acepta un número como entrada y devuelve una secuencia de enteros en el rango 1 a través del valor de entrada.

Esta función es utilizada por el código es  para rellenar las diferentes tablas con un número solicitado de filas.

Codigo 2:Generar Datos

El código  rellenará

  1. Clientes (Customers) con 50,000 registros
  2. Empleados(Employees) con 400 registros
  3. Remitentes (Shippers)con 10 registros
  4. Pedidos (Orders)1,000,000 registros

Esto generará aproximadamente 240 bytes de fila, por un total de más de 200MB . Para probar sus soluciones con otra distribución de datos, cambie los valores de las variables. Con los números existentes, el atributo custid representa una columna de particiones con baja densidad (gran cantidad de valores custid distintos, cada uno aparece un pequeño número de veces en la tabla Pedidos), mientras que el atributo shipperid representa una columna de particiones con alta densidad (pequeño número de distintos valores shipperid, cada uno de los cuales aparece una gran cantidad de veces).



Independientemente de la solución que utilice, si puede permitirse crear una indices de manera óptima, el mejor enfoque es crear un índice en la columna de partición , e incluir en la definición del índice todos los demás atributos que necesita devolver de la consulta (por ejemplo, relleno).

 

Solución ROW_NUMBER

Esta solución basada en la función ROW_NUMBER,supone que la solicitud fue para el pedido más reciente para cada cliente. El concepto es directo: asigne números de fila particionados por custid, ordenados por orden DESC, orderid DESC, y filtre solo las filas en las que el número de fila es igual a 1

Tenga en cuenta que cuando el particionamiento está involucrado en el cálculo de clasificación, la dirección de las columnas de índice debe coincidir con la dirección de las columnas en la lista ORDER BY del cálculo para que el optimizador considere confiar en el ordenamiento de índices. Por lo tanto, si crea el índice con la lista de claves (custid, orderdate, orderid) con orderdate y orderid ascendente, terminará con una clasificación costosa en el plan. Aparentemente, el optimizador no estaba codificado para darse cuenta de que un escaneo hacia atrás del índice podría usarse en tal caso.




Solución CROSS APPLY

La solución con el operador APPLY.  muestra un ejemplo para esta solución, con custid como la columna de partición y 1 como el número de órdenes más recientes para devolver por partición. Esta solución consulta la tabla que representa la entidad de particionamiento (custid) y utiliza el operador APPLY para aplicar una consulta que devuelve las órdenes más recientes (N) para cada fila externa. Esta solución es sencilla y se puede adaptar fácilmente para admitir múltiples filas por partición (simplemente ajuste la entrada a la opción TOP).

Puede ver  que cuando un índice está disponible, el plan escanea la tabla que representa la entidad de partición, luego para cada fila realiza una búsqueda y escaneo parcial en el índice de la tabla Pedidos. Este plan es altamente eficiente en casos de alta densidad porque el número de operaciones de búsqueda es igual al número de particiones; con un número pequeño de particiones, hay un pequeño número de operaciones de búsqueda. Cuando ejecuté esta solución para el caso de alta densidad (con shipperid en lugar de custid), cuando el índice estaba disponible, obtuve solo 39 lecturas lógicas, en comparación con las 28,000 lecturas de la solución anterior, basadas en ROW_NUMBER. En el caso de baja densidad, habrá muchas operaciones de búsqueda, y el costo de E / S será consecuentemente alto, hasta un punto donde la solución ROW_NUMBER será más económica. Como ejemplo, cuando se usa custid como la columna de partición

 

Solución con concatenación

Algunas soluciones funcionan bien solo cuando los índices correctos están disponibles, pero sin esos índices las soluciones funcionan mal. Tal es el caso con ambas soluciones que presenté. Crear nuevos índices no siempre es una opción: quizás su organización tenga políticas contra hacerlo; o tal vez el rendimiento de escritura es primordial en sus sistemas, y agregar índices agrega sobrecarga a las escrituras. Si la creación de un índice óptimo para admitir las soluciones ROW_NUMBER o APPLY no es una opción, puede usar una solución basada en la agrupación y la agregación de elementos concatenados(Como dicen si no puedes con el enemigo unete a el)

 

 




El principal inconveniente de esta solución es que es compleja y poco intuitiva, a diferencia de las demás. La consulta de expresión de tabla común (CTE) agrupa los datos por la columna de partición. La consulta aplica el agregado MAX a la partición concatenada + orden + elementos cubiertos después de convertirlos a una forma común que conserva el orden y el comportamiento de comparación de los elementos de particionamiento + orden (cadenas de caracteres de tamaño fijo con intercalación binaria con COLLATE Latin1_General_BIN2). Por supuesto, debe asegurarse de que se conserve el comportamiento de ordenamiento y comparación, de ahí el uso del estilo 112 (AAAAMMDD) para las fechas de pedido y la adición de ceros a las ID de orden

 

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

WP to LinkedIn Auto Publish Powered By : XYZScripts.com