sql server - optimizacion - ¿Por qué aparece una ordenación en mi plan de ejecución?

plan de ejecucion sql server

Question

Tengo la siguiente consulta SQL que se ejecuta muy lentamente. Eché un vistazo al plan de ejecución y afirmé que una ordenación en Files.OrderId es la operación de mayor costo (53%). ¿Por qué sucedería esto si no realizo el pedido por OrderId en ningún lugar? ¿Es mi mejor apuesta crear un índice en File.OrderId?

Plan de ejecución si alguien está interesado.

with custOrders as
(
    SELECT c.firstName + '' '' + c.lastname as Customer, c.PartnerId , c.CustomerId,o.OrderId,o.CreateDate, c.IsPrimary
    FROM Customers c
    LEFT JOIN CustomerRelationships as cr
        ON c.CustomerId = cr.PrimaryCustomerId
    INNER JOIN Orders as o
       ON c.customerid = o.customerid 
           OR (cr.secondarycustomerid IS NOT NULL AND o.customerid = cr.secondarycustomerid)
    where c.createdate >= @FromDate + '' 00:00'' 
       AND c.createdate <= @ToDate + '' 23:59'' 
),
 temp as
(
SELECT Row_number() 
         OVER ( 
           ORDER BY c.createdate DESC)                    AS ''row_number'', 
       c.customerid as customerId, 
       c.partnerid as partnerId, 
       c.Customer, 
       c.orderid as OrderId, 
       c.createdate as CreateDate, 
       Count(f.orderid)                                   AS FileCount, 
       dbo.Getparentcustomerid(c.isprimary, c.customerid) AS ParentCustomerId, 
       au.firstname + '' '' + au.lastname                   AS Admin, 
       '''' as blank, 
       0  as zero
FROM   custOrders c 
       INNER JOIN files f 
               ON c.orderid = f.orderid 
       INNER JOIN admincustomers ac 
               ON c.customerid = ac.customerid 
       INNER JOIN adminusers au 
               ON ac.adminuserid = au.id 
       INNER JOIN filestatuses s 
               ON f.statusid = s.statusid 
WHERE  ac.adminuserid IS NOT NULL 
       AND f.statusid NOT IN ( 5, 6 ) 
GROUP  BY c.customerid, 
          c.partnerid, 
          c.Customer, 
          c.isprimary, 
          c.orderid, 
          c.createdate, 
          au.firstname, 
          au.lastname 
)

Answer 1

Creo que el tipo está ocurriendo para esta unión:

FROM   custOrders c 
       INNER JOIN files f 
               ON c.orderid = f.orderid 

Me gustaría crear un índice en los archivos que incluya las columnas orderid y statusid, ya que la consulta también usa la columna statusid.

Es posible que también desee considerar los siguientes cambios:

1. No necesita "ac.adminuserid NO ES NULO", ya que esto está cubierto por la unión interna entre los usuarios y los administradores.
2. Cambie la prueba "f.statusid NOT IN (5, 6)" a una condición positiva (por ejemplo, In) ya que las condiciones negativas son más caras de procesar.

Answer 2

Sé que esta pregunta es bastante antigua, sin embargo tuve este mismo problema y me di cuenta de que había una razón completamente diferente en la que mis mesas se desaceleraban de repente. Los síntomas eran los mismos, lentos para actualizar vistas que antes eran muy rápidas. "Ordenar" dando un coste del 40%. Esta solución puede ser útil para alguien, y es simple. Al unir tablas, asegúrese de unirse en una base "me gusta para me gusta". Yo estaba uniendo dos mesas en ID. Sin embargo, en una tabla mi ID se estableció como un int y en la otra como nvarchar. Corrigí esto para que ambos estuvieran definidos como el mismo tipo y la vista volvió a la velocidad del rayo.

Esperemos que esto ayude a alguien más a evitar pasar una semana tratando de averiguar qué está mal con SQL, cuando es realmente un momento PEBKAC.

(El problema existe entre el teclado y la silla)

Answer 3

SQL Server está realizando la clasificación para habilitar la combinación de combinación entre el conjunto de datos a la derecha de ese operador de clasificación y los registros en la tabla de Orders . La combinación de combinación en sí misma es una forma muy eficaz de unir todos los registros en un conjunto de datos, pero requiere que cada conjunto de datos que se unirá se ordene según las claves de combinación y en el mismo orden.

Dado que la clave PK_Orders ya está ordenada por OrderID , SQL Server decidió aprovechar eso al ordenar el otro extremo de la unión (el otro material a la derecha del ordenamiento) para que los dos conjuntos de datos se puedan combinar juntos en ese punto en El plan. La alternativa común a la combinación de combinación es una combinación de hash, pero eso no lo ayudaría porque en su lugar tendría un operador de combinación de hash costoso en lugar de la clasificación y combinación. El optimizador de consultas ha determinado que la clasificación y la combinación sean más eficientes en este caso.

La causa raíz del paso costoso en el plan es la necesidad de combinar todos los registros de la tabla de órdenes en el conjunto de datos. ¿Hay alguna manera de limitar los registros provenientes de la tabla de files ? Un índice en files.statusid puede ser útil si los registros que no están en 5,6 son inferiores al 10% del tamaño total de la tabla.

El QO piensa que la mayoría de los registros se filtrarán al final. Intente hacer retroceder tantas de esas condiciones de filtro a las fuentes de registro para que haya que manejar menos registros en medio del plan.

EDITAR: Olvidé mencionar, es muy útil tener un plan de ejecución que podamos ver. ¿Hay alguna manera de obtener un resultado del plan de ejecución real para ver el número real de registros que pasan por esos operadores? A veces, el conteo estimado de registros puede ser un poco apagado.

EDITAR: Profundizando en el campo de predicado del segundo hasta el último filtro, se resume:

c.CustomerId=o.CustomerId
OR o.CustomerId=cr.SecondaryCustomerId AND cr.SecondaryCustomerId IS NOT NULL

Parece que SQL Server está produciendo una unión cruzada entre todos los registros coincidentes posibles entre Orders y Customers hasta este punto en la consulta (el plan a la derecha del segundo al último operador de filtro) y luego observa cada registro con esa condición para ver si de hecho coincide. ¿Observa cómo la línea que entra en el filtro es realmente gruesa y la línea que sale es realmente delgada? Esto se debe a que el número estimado de filas va de 21k a 4 después de ese operador. Olvida lo que dije antes, este es probablemente el principal problema en el plan. Incluso si hay índices en estas columnas, SQL Server no puede usarlos porque la condición de unión es demasiado compleja. Esto hace que el plan combine todos los registros en lugar de buscar solo los que necesita porque no puede usar el predicado de unión completa de inmediato.

Mi primer pensamiento es reformular los custOrders CTE como una unión de dos conjuntos de datos: uno usando CustomerId y otro usando SecondaryCustomerId para unirse. Esto duplicará el trabajo del resto del CTE, pero si permite el uso adecuado de los índices, podría ser una gran ganancia.

Answer 4

SQL Server tiene tres algoritmos para elegir cuando necesita unir dos tablas. La unión de bucles anidados, la combinación de hash y la combinación de combinación de ordenación. A cuál selecciona se basa en estimaciones de costos. En este caso, se calculó que, en base a la información que tenía disponible, unir-Combinar-unirse fue la elección correcta.

En los planes de ejecución de SQL Server, una combinación de clasificación se divide en dos operadores, la clasificación y la combinación de combinación, porque la operación de clasificación podría no ser necesaria, por ejemplo, si los datos ya están ordenados.

Para obtener más información acerca de las combinaciones, consulte mi serie de combinaciones aquí: http://sqlity.net/en/1146/a-join-a-day-introduction/ El artículo sobre Sort-Merg-Join está aquí: http://sqlity.net/en/1480/a-join-a-day-the-sort-merge-join/

Para hacer su consulta más rápida, primero miraría los índices. Tiene un montón de exploraciones de índices agrupados en la consulta. Si puedes reemplazar algunos de ellos con búsquedas, probablemente serás mejor. También verifique si las estimaciones que produce SQL Server coinciden con los conteos de filas reales en un plan de ejecución real. Si están lejos, SQL Server a menudo toma malas decisiones. Por lo tanto, proporcionar mejores estadísticas también puede ayudarlo a consultar el rendimiento.