Hay una diferencia de rendimiento entre el CTE , la Sub-Consulta, Tabla Temporal o Variable de Tabla?

En este excelente ASÍ, pregunta, las diferencias entre CTE y sub-queries fueron discutidos.

Me gustaría preguntar específicamente:

¿En qué circunstancia es cada uno de los siguientes más eficiente y más rápido?

  • CTE
  • Sub-Consulta
  • Tabla Temporal
  • Variable De Tabla

Tradicionalmente, la he usado un montón de temp tables en el desarrollo de stored procedures – como parece más fácil de leer que un montón de entrelazados sub-consultas.

Non-recursive CTEs encapsular los conjuntos de datos muy bien, y son muy legible, pero hay determinadas circunstancias en las que uno puede decir que siempre va a funcionar mejor? o es un caso de tener siempre el violín alrededor con las diferentes opciones para encontrar la solución más eficiente?


EDITAR

Recientemente he sido informado de que, en términos de eficiencia, las tablas temporales son una buena primera opción, ya que tienen asociado un histograma es decir, las estadísticas.

  • Respuesta General: depende. Y depende de muchos factores, cualquier declaración general es probablemente falsa – en algunas situaciones. Básicamente: que usted necesita para probar y medir – ver cual funciona mejor para usted!
  • ok, tal vez esta pregunta debe ser cerrado por ser subjetiva? La mente un montón de SQL preguntas sobre LO que podría ser juzgado como subjetiva.
  • Se podría llegar cerrado por ser demasiado amplia y estoy de acuerdo con usted – un montón de cosas y temas en SQL realmente va a obtener una respuesta de depende. A veces uno puede hacer una lista de dos o tres criterios para tomar una decisión, pero con su pregunta aquí, es simplemente imposible dar un buen consejo – depende mucho – su tabla de estructuras de datos en las tablas, las consultas que usted está utilizando, su estrategia de indización y mucho mucho más….
  • sería bueno probar y mantener cualquier consejo sobre las posibles modificaciones a la operación para tratar de hacerlo más concreto y estrecho ?
  • Por favor nota: esta pregunta es específica de SQL Server. Para otros DBs como postgres, una CTE es a menudo mucho más lento que el equivalente en subconsultas (ver http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/)
  • Sin embargo, por alguna razón, en la práctica, la gran mayoría de consultas complejas que encuentro en real del negocio de software se ejecutan mucho más rápido escrita como Cte en Postgres que subconsultas. Esto todavía deja perplejos a mi, pero de una feliz coincidencia, ya que esas son generalmente mucho más legible meses más tarde (o por nuevos ojos). «… cualquier declaración general es probablemente falsa» de hecho!
  • ese enlace es de la vieja (2014). Todos los demás lectura de Jay comentario, tomamos nota.
  • Este podría ser de interés y que, a la hora de escribir (Agosto de 2019) relativamente reciente.

InformationsquelleAutor whytheq | 2012-06-23

4 Kommentare

  1. 226

    SQL es un lenguaje declarativo, no un lenguaje procedimental. Es decir, construir una instrucción SQL para describir los resultados que desea. Usted no está diciendo al motor de SQL cómo para hacer el trabajo.

    Como regla general, es una buena idea dejar que el motor de SQL y SQL optimizer encontrar el mejor plan de consulta. Hay muchas persona-años de esfuerzo que vaya en el desarrollo de un motor de SQL, así que deja que los ingenieros hacen lo que saben hacer.

    Por supuesto, hay situaciones en las que el plan de consulta no es óptimo. A continuación, usted desea utilizar las sugerencias de consulta, la reestructuración de la consulta, actualización de estadísticas, el uso de tablas temporales, agregar los índices, y así sucesivamente para obtener un mejor rendimiento.

    Como por su pregunta. El rendimiento de Cte y subconsultas debería, en teoría, ser el mismo ya que ambos proporcionan la misma información para el optimizador de consultas. Una diferencia es que una CTE utilizado más de una vez puede ser fácilmente identificado y calculado una vez. Los resultados pueden ser guardados y leer varias veces. Por desgracia, SQL Server no parece tomar ventaja de esta información básica del método de optimización (puede llamar a esta común subconsulta eliminación).

    Tablas temporales son un asunto diferente, porque son proporcionar más orientación sobre cómo se debe ejecutar la consulta. Una diferencia importante es que el optimizador puede utilizar las estadísticas de la tabla temporal para establecer su plan de consulta. Esto puede resultar en mejoras en el rendimiento. También, si usted tiene un complicado CTE (subconsulta) que se utiliza más de una vez, luego guardarlo en una tabla temporal suele dar una mejora en el rendimiento. La consulta se ejecuta sólo una vez.

    La respuesta a tu pregunta es que usted necesita para jugar para obtener el rendimiento que usted espera, especialmente para las consultas más complejas que se ejecutan sobre una base regular. En un mundo ideal, el optimizador de consultas encontrar la mejor ruta de ejecución. Aunque con frecuencia lo hace, usted puede ser capaz de encontrar una manera de obtener un mejor rendimiento.

    • Algunas de Microsoft Investigaciones sobre posibles mejoras futuras en esta área es en la publicación de la «Explotación Eficiente de Similar Subexpresiones para el Procesamiento de Consultas» Disponible a partir de aquí
    • Dado que este documento fue presentada en el año 2007, alguna idea de si se han incorporado en SQL Server 2012?
    • Yo no lo creo. No he leído nada a ese efecto por desgracia.
    • Una gran respuesta! Sólo para enfatizar: SQL es un lenguaje declarativo, y no tenemos control sobre CÓMO los datos se extraen. Por lo tanto, el rendimiento/velocidad varía de consulta para la consulta.
    • «El rendimiento de Cte y subconsultas debería, en teoría, ser el mismo ya que ambos proporcionan la misma información para el optimizador de consultas.» Esto no es cierto en el caso específico de postgres, porque Cte son la optimización de las cercas de la como se describe aquí. Llegué a esta pregunta cuando una CTE-pesado de la consulta estaba tomando demasiado tiempo, y resultó el uso de subconsultas de varias órdenes de magnitud más rápido.
    • La pregunta y esta respuesta está dirigida específicamente a SQL Server. Lo que dices es cierto en casi todos los demás de la base de datos, que se materializará Cte (como Postgres) siempre o a veces (como Oracle).
    • ¡vaya! No sé cómo he perdido las etiquetas sobre esta cuestión. Yo voy a dejar mi comentario por si alguien comete el mismo error.
    • Hay alguna mejora en el rendimiento si queremos crear índices para las tablas temporales?
    • Los índices de las tablas temporales mejorar definitivamente las consultas que pueden tomar ventaja de los índices — como con los índices de una tabla permanente. Pero, si se materializan de una subconsulta como una tabla temporal, puede perder la ventaja de los índices en las tablas originales.
    • Yo no podía entender lo que usted le dijo en el último punto, es decir, si se materializan de una subconsulta como una tabla temporal.
    • un motor de base de datos se materializa una subconsulta/CTE en el curso de la ejecución de una consulta compleja, no se le añade que los índices de la materialización. Usted puede hacer esto de forma manual utilizando las tablas temporales.
    • Una diferencia es que una CTE utilizado más de una vez puede ser fácilmente identificado y calculado una vez 🙂 yeah! pero se ejecuta una vez en todos los lugares utilizados??
    • En SQL Server, Cte son tratados como puntos de vista y se ejecutan cada vez que se hace referencia. La mayoría de las bases de datos, sin embargo, hacer lo que usted sugiere.
    • queridos @gordon-linoff Una manera que me fue útil es : seleccione repetible datos de la bruja necesitaba en sub consultas de una vez en una tabla de variables (no Tabla #Temp 🙂 y lo utilizó en donde estoy lo necesitan. Esto reduce el coste de ejecución significativamente 🙂 Como los amigos, dijo, CTE simplemente hace que nuestro código de declaración más hermosa 🙂
    • Re. «CTE utilizado más de una vez puede ser fácilmente identificado y calculado una vez» y la idea de que la Tabla temporal podría ser más rápido debido a la garantizados Estadísticas: Parece que es cierto sólo si la sub-Consulta es independiente del resto de la Consulta principal. Con la mayoría de los sub-Consultas (o las Cláusulas where) he visto / tenido que escribir, la sub-Consulta depende de un determinado valor de la Fila(s) de otra Tabla en la Consulta principal.
    • Nadie menciona los efectos de lo que nuestro DBA llamadas «Contención» / «puntos muertos». Él casi siempre crea Tablas temporales, principalmente para evitar la «Contención» / «Bloqueos» para evitar la disminución de perf de otro las Consultas (y sólo como un lejano razón secundaria para aumentar perf de la Consulta que él está escribiendo). Él cita a algo que él ha leído acerca de cómo, si una Consulta de referencias demasiadas Filas de una Tabla, SQL Server sólo se Bloquea toda la Tabla. Sospecho que la violación de YAGNI y la BESO, como he trabajado en tiendas w/ MUCHO más / mucho más-usado DB y que nunca se necesita de Tablas temporales si las Consultas correctamente escrito y Tablas Indexadas.

  2. 73

    No hay ninguna regla. Me parece Cte más legible, y el uso de ellos menos que presentan algún problema de rendimiento, en cuyo caso puedo investigar el problema real, en lugar de suponer que el CTE es el problema y tratar de volver a escribir con un enfoque diferente. Por lo general hay más a la cuestión de la forma en que me eligió de forma declarativa del estado de mis intenciones con la consulta.

    Ciertamente hay casos en los que usted puede desentrañar Cte o eliminar las subconsultas y reemplazarlos con una tabla #temp y reducir su duración. Esto puede ser debido a varias cosas, como obsoletos estadísticas, la imposibilidad de obtener estadísticas precisas (por ejemplo, unirse a una función con valores de tabla), el paralelismo, o incluso la incapacidad para generar un óptimo plan debido a la complejidad de la consulta (en el que caso de que se rompa puede dar el optimizador de una oportunidad de luchar). Pero también hay casos en que el I/O participan en la creación de una tabla #temp pueden ser mayores que los otros aspectos del rendimiento que puede hacer un plan en particular, la forma de usar una CTE menos atractivo.

    Sinceramente, hay demasiadas variables para proporcionar una respuesta «correcta» a su pregunta. No hay ninguna manera predecible para saber cuando una consulta se puede inclinar en favor de un enfoque u otro – sólo sé que, en teoría, la misma semántica de una CTE o una subconsulta debe ejecutar el mismo. Creo que tu pregunta sería más valiosa si usted presenta algunos de los casos donde esto no es cierto – puede ser que usted ha descubierto una limitación en el optimizador (o descubierto un conocido), o puede ser que las consultas no son semánticamente equivalentes o que uno de ellos contiene un elemento que impide la optimización.

    Por eso sugiero escribir la consulta de una manera que parece más natural, y sólo se desvían cuando se descubre un verdadero problema de rendimiento que el optimizador está teniendo. Personalmente creo que el rango de CTE, entonces subconsulta, con tabla #temp ser un último recurso.

    • +1 en cuanto a ser una cuestión subjetiva; espero que no se cierran por ser demasiado vaga como las respuestas que hasta el momento son de carácter informativo. Me doy cuenta 🙂 no te gusta cuando las preguntas cambian, pero ¿tiene alguna sugerencia para el estrechamiento de la pregunta del OP?
    • Creo que esta pregunta está bien, te darás cuenta de que no hay un solo voto-para-cerrar, pero si las respuestas de inicio agitándose alrededor salvajemente probablemente se apague. Como he indicado en mi respuesta, si usted tiene un particulares caso donde se ve una gran diferencia entre una CTE y una subconsulta, iniciar una nueva pregunta con las consultas y los planes de ejecución (y podría ser un mejor ajuste en dba.se). Acaba de darse cuenta de que la respuesta a ayudar con que de la consulta no puede ser la misma respuesta para una consulta diferente con el mismo escenario.
    • Justo debajo de tu pregunta hay enlaces link / edit / close / flag – si ha habido algún votos para cerrar la cuestión, verá close (n) donde n representa el número de usuarios que han votado a cerca de su pregunta. Si usted haga clic en el enlace podrás ver las razones por las cuales los usuarios seleccionados.
    • véase también este reciente post del blog de Bob Beauchemin. No tratar CTE vs subconsulta en concreto, pero el mismo tipo de concepto se aplica: si usted elige un patrón intuitivo por motivos de rendimiento, documento a la mierda y volver a visitar para asegurarse de que la peculiaridad de que has descubierto es todavía real. Yo incluso podría sugerir dejando la más natural de la versión de la consulta comentado, a menos que usted tiene una fuente fiable sistema de control en el lugar que tiene la versión anterior.
    • Corregido el enlace de arriba: sqlskills.com/blogs/bobb/…
    • Gracias, muchas cosas pueden suceder en 7 años. Es uno de los desafortunados efectos secundarios de tan valiosa, de larga duración sitios como este: enlaces eventualmente la putrefacción.
    • Por las referencias que no quiero romper, yo uso este: archive.org/web
    • Hago uso de ese recurso después de que los enlaces se rompen, pero no voy a adelantarse que tirando de un enlace estático para cada URL que he puesto, no solo porque es un trabajo mucho más dado que es imposible predecir qué enlaces se rompen, pero también desde los que no break es probable que se actualiza a lo largo del tiempo, mientras que el enlace estático a un determinado copia archivada no reflejar esos cambios. Puedo actualizar las entradas que tienen enlaces muertos (y lo han hecho de forma exhaustiva para los descansos como conectar y sqlblog.com), pero es imposible hacerlo de 6 años de edad, comentarios.
    • Ah, tiendo a querer mi referencia exacta a permanecer sin cambios y creemos que los usuarios pueden ver una versión más reciente si se desea obtener. Si yo no lo archiva en el momento en que estoy de referencia, entonces es posible que nunca se archiva. A cada uno lo suyo.
    • Si miras mi perfil en este y otros sitios, usted puede obtener un sentido de por qué yo no puede archivar todos los enlaces en el momento en que estoy referencia a ella, o tomar el tiempo para dar prioridad a los vínculos son más propensos a morir y/o ser más perjudicial si lo hacen. Se puede mostrar algunos ejemplos en los que usted ha hecho esto preventiva archivado?

  3. 17

    #temp es materalized y CTE no.

    CTE es sólo la sintaxis así que en teoría es sólo una subconsulta. Es ejecutado. #temp se materializa. Así que una cara CTE en una combinación que se ejecutan muchas veces puede ser mejor en un #temp. En el otro lado si es un fácil de evaluación que no se ejecuta, pero un par de veces, entonces no vale la sobrecarga de #temp.

    La gente en LO que no les gusta la variable de tabla pero me gustan como se materializan y rápido crear de #temp. Hay veces cuando el optimizador de consultas no mejor con un #temp en comparación con una tabla de variables.

    La capacidad de crear un PK en una #temp o variable de tabla da el optimizador de consultas más información que una CTE (como usted no puede declarar una PK en una CTE).

    • ¿cuál es el acrónimo de «TVP» … algo similar a #temp ?
    • TVP se está convirtiendo en un término común, porque el sonido es impresionante (para algunos). En definitiva, una TVP es una tabla que se pasa como parámetro. Cualquier persona que ha utilizado la Tabla de variables se sentirán como en casa con ellos.
    • ADVERTENCIA – Tvp no tienen planes de ejecución! No uso de Tvp para nada el más simple de los cortos de las listas de búsqueda. Si usted no hace ningún tipo de complejos, se une, inserciones o actualizaciones sobre ellos, podría ejecutar en enormes problemas de optimización. Confía en mí, he sido quemada por este.
  4. 11

    Solo 2 cosas que pienso hacer es SIEMPRE preferible utilizar una Tabla # Temp y no en el CTE son:

    1. No se puede poner una clave principal en una CTE de modo que los datos que se accede por el CTE se tiene que recorrer cada uno de los índices en el CTE mesas del lugar, a continuación, sólo acceder a la PK o Índice en la tabla temporal.

    2. Porque no se puede agregar restricciones, índices y claves principales para una CTE son más propensos a insectos rastreros y de mala de datos.


    -onedaywhen ayer

    Este es un ejemplo donde #restricciones de tabla puede prevenir el mal de datos que no es el caso en el CTE de la

    DECLARE @BadData TABLE ( 
                           ThisID int
                         , ThatID int );
    INSERT INTO @BadData
           ( ThisID
           , ThatID
           ) 
    VALUES
           ( 1, 1 ),
           ( 1, 2 ),
           ( 2, 2 ),
           ( 1, 1 );
    
    IF OBJECT_ID('tempdb..#This') IS NOT NULL
        DROP TABLE #This;
    CREATE TABLE #This ( 
                 ThisID int NOT NULL
               , ThatID int NOT NULL
                            UNIQUE(ThisID, ThatID) );
    INSERT INTO #This
    SELECT * FROM @BadData;
    WITH This_CTE
         AS (SELECT *
               FROM @BadData)
         SELECT *
           FROM This_CTE;
    • ALWAYS es un poco demasiado lejos, pero gracias por responder. En términos de legibilidad el uso de las Cte puede ser una buena cosa.
    • No entiendo su segundo punto en absoluto. A mi modo de ver, la consulta que define el CTE es análogo a las restricciones que pone en la tabla temporal, señalando que el ex pueden ser arbitrariamente complejas predicados mientras que el segundo es mucho más limitado (por ejemplo, CHECK restricción refiriéndose a las múltiples filas o tablas no está permitido). Puedes publicar un ejemplo donde una CTE exhibe un error que la tabla temporal equivalente no?

Kommentieren Sie den Artikel

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

Pruebas en línea