De Rendimiento de SQL: SELECT DISTINCT frente a GRUPO POR

He estado tratando de mejorar los tiempos de consulta de base de datos Oracle, aplicación basada en que ha estado funcionando un poco lento. La aplicación se ejecuta varias consultas grandes, tales como el de abajo, que puede tomar más de una hora para que se ejecute. Sustitución de la DISTINCT con un GROUP BY cláusula en la consulta siguiente se redujo el tiempo de ejecución de 100 minutos y 10 segundos. A mi entender fue que SELECT DISTINCT y GROUP BY operado en prácticamente la misma manera. ¿Por qué una enorme disparidad entre los tiempos de ejecución? ¿Cuál es la diferencia en la forma en que se ejecuta la consulta en el back-end? Es allí alguna vez una situación en la que SELECT DISTINCT se ejecuta más rápido?

Nota: En la siguiente consulta, WHERE TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A' representa sólo uno de un número de maneras en que los resultados pueden ser filtrados. En este ejemplo se proporciona para mostrar el razonamiento para unir todas las tablas que no tienen columnas incluidas en el SELECT y se traduciría en cerca de un décimo de todos los datos disponibles

SQL utilizando DISTINCT:

SELECT DISTINCT 
    ITEMS.ITEM_ID,
    ITEMS.ITEM_CODE,
    ITEMS.ITEMTYPE,
    ITEM_TRANSACTIONS.STATUS,
    (SELECT COUNT(PKID) 
        FROM ITEM_PARENTS 
        WHERE PARENT_ITEM_ID = ITEMS.ITEM_ID
        ) AS CHILD_COUNT
FROM
    ITEMS
    INNER JOIN ITEM_TRANSACTIONS 
        ON ITEMS.ITEM_ID = ITEM_TRANSACTIONS.ITEM_ID 
        AND ITEM_TRANSACTIONS.FLAG = 1
    LEFT OUTER JOIN ITEM_METADATA 
        ON ITEMS.ITEM_ID = ITEM_METADATA.ITEM_ID
    LEFT OUTER JOIN JOB_INVENTORY 
        ON ITEMS.ITEM_ID = JOB_INVENTORY.ITEM_ID     
    LEFT OUTER JOIN JOB_TASK_INVENTORY 
        ON JOB_INVENTORY.JOB_ITEM_ID = JOB_TASK_INVENTORY.JOB_ITEM_ID
    LEFT OUTER JOIN JOB_TASKS 
        ON JOB_TASK_INVENTORY.TASKID = JOB_TASKS.TASKID                              
    LEFT OUTER JOIN JOBS 
        ON JOB_TASKS.JOB_ID = JOBS.JOB_ID
    LEFT OUTER JOIN TASK_INVENTORY_STEP 
        ON JOB_INVENTORY.JOB_ITEM_ID = TASK_INVENTORY_STEP.JOB_ITEM_ID 
    LEFT OUTER JOIN TASK_STEP_INFORMATION 
        ON TASK_INVENTORY_STEP.JOB_ITEM_ID = TASK_STEP_INFORMATION.JOB_ITEM_ID
WHERE 
    TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A'
ORDER BY 
    ITEMS.ITEM_CODE

SQL utilizando GROUP BY:

SELECT
    ITEMS.ITEM_ID,
    ITEMS.ITEM_CODE,
    ITEMS.ITEMTYPE,
    ITEM_TRANSACTIONS.STATUS,
    (SELECT COUNT(PKID) 
        FROM ITEM_PARENTS 
        WHERE PARENT_ITEM_ID = ITEMS.ITEM_ID
        ) AS CHILD_COUNT
FROM
    ITEMS
    INNER JOIN ITEM_TRANSACTIONS 
        ON ITEMS.ITEM_ID = ITEM_TRANSACTIONS.ITEM_ID 
        AND ITEM_TRANSACTIONS.FLAG = 1
    LEFT OUTER JOIN ITEM_METADATA 
        ON ITEMS.ITEM_ID = ITEM_METADATA.ITEM_ID
    LEFT OUTER JOIN JOB_INVENTORY 
        ON ITEMS.ITEM_ID = JOB_INVENTORY.ITEM_ID     
    LEFT OUTER JOIN JOB_TASK_INVENTORY 
        ON JOB_INVENTORY.JOB_ITEM_ID = JOB_TASK_INVENTORY.JOB_ITEM_ID
    LEFT OUTER JOIN JOB_TASKS 
        ON JOB_TASK_INVENTORY.TASKID = JOB_TASKS.TASKID                              
    LEFT OUTER JOIN JOBS 
        ON JOB_TASKS.JOB_ID = JOBS.JOB_ID
    LEFT OUTER JOIN TASK_INVENTORY_STEP 
        ON JOB_INVENTORY.JOB_ITEM_ID = TASK_INVENTORY_STEP.JOB_ITEM_ID 
    LEFT OUTER JOIN TASK_STEP_INFORMATION 
        ON TASK_INVENTORY_STEP.JOB_ITEM_ID = TASK_STEP_INFORMATION.JOB_ITEM_ID
WHERE 
    TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A'
GROUP BY
    ITEMS.ITEM_ID,
    ITEMS.ITEM_CODE,
    ITEMS.ITEMTYPE,
    ITEM_TRANSACTIONS.STATUS
ORDER BY 
    ITEMS.ITEM_CODE

Aquí es el de Oracle plan de consulta para la consulta mediante DISTINCT:

De Rendimiento de SQL: SELECT DISTINCT frente a GRUPO POR

Aquí es el de Oracle plan de consulta para la consulta mediante GROUP BY:

De Rendimiento de SQL: SELECT DISTINCT frente a GRUPO POR

  • Mostrar la consulta con group by.
  • No tengo la respuesta a tu pregunta, pero espero que eso de ver TANTO las consultas, explicar sus planes y el número de lógica se Presenta podría ayudar en la comprensión (por lo que vale, yo habría esperado DISTINTO a tener una ventaja de rendimiento, en todo caso).
  • En SQL Server, puede obtener Planes de Ejecución de Consultas.. puede conseguir algo similar en Oracle? Que iba a decirle, en donde la diferencia fue.
  • Por CIERTO: ¿por qué la gran cadena larga de la IZQUIERDA se une, cuando sólo se desea que los registros con un ‘TIPO a’ en el final?
  • Dos cosas; 1) Pon tu GRUPO de consulta de su pregunta y 2) Ejecutar un explain PLAN en cada consulta, y también añadir el resultado a la pregunta.
  • ITEM_PARENTS tiene a los niños ?? que interesante.
  • Gracias por los comentarios, he añadido la misma consulta con el GRUPO, y aclaró que el razonamiento de la estructura de consulta un poco. Voy a generar planes de consulta y agregar poco.
  • Gracias @Dan-o, he añadido el plan de consulta.
  • Gracias, @HamletHakobyan, he añadido al grupo.
  • Este es sólo un ejemplo de cómo los resultados son filtrados para esta consulta. Las columnas de la mayoría de estas tablas puede ser que se hace referencia en la cláusula where.
  • Con qué frecuencia son estos conjuntos de datos se actualiza? Este parece un buen candidato para las vistas materializadas
  • Normalmente, varios cientos de registros se agregó un día. Los registros que se muestran en la salida tiene que ser actualizada, por lo que la vista materializada tendría que ser actualizan bastante a menudo.
  • hmm, definir con bastante frecuencia. Cuando escucho una de varios cientos de día, que implica algún tipo de la hora programada o basado en insertar.
  • Lo siento, debería haber sido más claro. Los registros se generan cuando los usuarios realizar ciertas tareas en el sistema, así que no hay horario. Un usuario podría generar un único registro en un día o a cientos de personas por hora. Las cosas importantes es que cada vez que un usuario ejecuta una búsqueda actualizada de los registros deben ser devueltos, lo que me hace dudoso que una vista materializada que trabajo aquí, especialmente si la consulta rellenando tomaría tiempo para funcionar.
  • He actualizado mi post acerca de cómo se podrían utilizar una vista materializada. Por favor, hágamelo saber si no es claro. El punto clave es que se puede insertar datos en la vista materializada.
  • stackoverflow.com/questions/7943957/…

InformationsquelleAutor woemler | 2012-12-19

4 Kommentare

  1. 18

    La diferencia de rendimiento es probablemente debido a la ejecución de la subconsulta en la SELECT cláusula. Supongo que es volver a ejecutar esta consulta para cada fila antes de las distintas. Para el group by, se ejecutaría una vez después de el grupo.

    Tratar de sustituirlo con una combinación, en lugar de:

    select . . .,
           parentcnt
    from . . . left outer join
          (SELECT PARENT_ITEM_ID, COUNT(PKID) as parentcnt
           FROM ITEM_PARENTS 
          ) p
          on items.item_id = p.parent_item_id
    • +1 – Esto es exactamente lo que yo estaba pensando demasiado (incluyendo el potencial de la solución), pero no sé lo suficiente acerca de Oracle para estar seguro.
    • Este parece ser el cuello de botella. He intentado quitar la subconsulta y de la consulta ejecutada tan pronto como el GRUPO de versión (100 min vs 20 seg.). Gracias!
  2. 16

    Estoy bastante seguro de que GROUP BY y DISTINCT tienen aproximadamente el mismo plan de ejecución.

    La diferencia aquí, ya que tenemos que adivinar (ya que no tenemos el explicar los planes) es la OMI que la línea de la subconsulta se ejecuta DESPUÉS de la GROUP BY pero ANTES de la DISTINCT.

    Así que si la consulta devuelve 1 millón de filas y obtiene agregada a 1k filas:

    • La GROUP BY consulta tendría ejecutar la subconsulta 1000 veces,
    • Mientras que el DISTINCT consulta tendría ejecutar la subconsulta 1000000 de veces.

    La tkprof explicar el plan ayudaría a demostrar esta hipótesis.


    Mientras estamos discutiendo esto, creo que es importante tener en cuenta que la forma en que la consulta se escribe es engañosa, tanto para el lector y para el optimizador: que, obviamente, desea buscar todas las filas de elemento/item_transactions que tienen un TASK_INVENTORY_STEP.STEP_TYPE con un valor de «TIPO a».

    OMI su consulta tendría un plan mejor y sería más fácil de leer si escribe así:

    SELECT ITEMS.ITEM_ID,
           ITEMS.ITEM_CODE,
           ITEMS.ITEMTYPE,
           ITEM_TRANSACTIONS.STATUS,
           (SELECT COUNT(PKID) 
              FROM ITEM_PARENTS 
             WHERE PARENT_ITEM_ID = ITEMS.ITEM_ID) AS CHILD_COUNT
      FROM ITEMS
      JOIN ITEM_TRANSACTIONS 
        ON ITEMS.ITEM_ID = ITEM_TRANSACTIONS.ITEM_ID 
       AND ITEM_TRANSACTIONS.FLAG = 1
     WHERE EXISTS (SELECT NULL
                     FROM JOB_INVENTORY   
                     JOIN TASK_INVENTORY_STEP 
                       ON JOB_INVENTORY.JOB_ITEM_ID=TASK_INVENTORY_STEP.JOB_ITEM_ID
                    WHERE TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A'
                      AND ITEMS.ITEM_ID = JOB_INVENTORY.ITEM_ID)

    En muchos casos, una clara puede ser un signo de que la consulta no está escrito correctamente (debido a que una buena consulta no debería devolver los duplicados).

    Nota también de que 4 de las tablas no se usan en su original, seleccione.

    • Gracias por la respuesta. La consulta dada es sólo un ejemplo que muestra una de las lejanamente se unió a tablas que se utilizan para filtrar los resultados. Las columnas de casi todas las mesas se unieron en esta consulta podría ser utilizado en la cláusula where.
    • Todavía debe utilizar SEMI-ÚNETE a (o EN) cuando corresponda, en lugar de DISTINTO, es más claro para ambos futuro lector y tal vez más importante para el optimizador.
  3. 8

    Lo primero que debe señalarse es el uso de Distinct indica un código de olor, también conocido como anti-patrón. Esto generalmente significa que hay una falta de unirse adicional o una combinación que es la generación de datos duplicados. Mirando a su consulta, supongo que la razón por la que group by es más rápido (sin ver la consulta), es que la ubicación de la group by reduce el número de registros que terminan siendo devueltos. Mientras que distinct se expulse fuera el resultado de establecer y hacer fila por fila comparaciones.

    Actualización enfoque

    Lo siento, debería haber sido más claro. Los registros se generan cuando
    los usuarios realizar ciertas tareas en el sistema, así que no hay horario. Un
    el usuario puede generar un único registro en un día o a cientos de personas por hora. El
    las cosas importantes es que cada vez que un usuario ejecuta una búsqueda, arriba-a-fecha de
    los registros deben ser devueltos, lo que me hace dudoso que un materializado
    ver que trabajo aquí, especialmente si la consulta rellenando tomaría
    tiempo en ejecutarse.

    Yo creo que este es el motivo exacto por el uso de una vista materializada. Por lo que el proceso de trabajo de esta manera. Usted toma el tiempo la ejecución de la consulta como de la pieza que se construye fuera de su vista materializada, ya que sabemos que el usuario solo se preocupa de la «nueva» datos después de realizar algunas arbitraria tarea en el sistema. Así que lo que quieres hacer es una consulta contra la base de la vista materializada, lo que puede actualizar constantemente en el back-end, la persistencia de la estrategia no debe ahogar la vista materializada (la persistencia de un par de cientos de registros en un tiempo de no aplastar cualquier cosa). Lo que esto va a permitir que se de Oracle para agarrar un bloqueo de lectura (nota: no nos importa cómo muchas fuentes de leer nuestros datos, nosotros sólo nos preocupamos de los escritores). En el peor de los casos el usuario tendrá «obsoletos» los datos de microsegundos, por lo menos este es un sistema de negociación financiera en Wall Street o en un sistema de un reactor nuclear, estos «picos» debe pasar desapercibido, incluso por la mayoría de los ojos de águila de los usuarios.

    Código de ejemplo de cómo hacer esto:

    create materialized view dept_mv FOR UPDATE as select * from dept; 

    Ahora la clave para esto es el tiempo que usted don’ t invocar actualizar usted no perderá ninguno de los datos almacenados. Será hasta usted para determinar cuando se quiere «línea de base» de su vista materializada de nuevo (de la medianoche tal vez?)

    • +1 para el código de olor. Consultas de unirse a las tablas a través de PK no debería devolver duplicados ; si lo hacen tal vez algo está mal 🙂
    • Usted está definitivamente a la derecha en este punto. El esquema es bastante mal diseñado, con una gran cantidad de redundancia, de muchos años de tener módulos con nuevas tablas fijadas sin un esquema de revisión. Por desgracia, tengo que vivir con lo que tengo.
  4. -3

    Usted debe utilizar el GRUPO para aplicar los operadores de agregado para cada grupo y DISTINTA si sólo necesita para eliminar los duplicados.

    Creo que el rendimiento es el mismo.

    En tu caso creo que deberías usar GROUP BY.

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea