¿Cuáles son las tablas más efectivas, CTE o temporales?

¿Cuáles son las Temporary Tables más CTE , CTE o Temporary Tables ?

Yo diría que son conceptos diferentes pero no demasiado diferentes para decir “tiza y queso”.

  • Una tabla temporal es buena para reutilizar o para realizar múltiples pasadas de procesamiento en un conjunto de datos.

  • Un CTE se puede utilizar para recurse o simplemente para mejorar la legibilidad.
    Y, al igual que una vista o una tabla en línea, la función de valor también se puede tratar como una macro para expandir en la consulta principal

  • Una tabla temporal es otra tabla con algunas reglas sobre el scope

He almacenado procs donde uso ambas (y variables de tabla también)

Depende.

Ante todo

¿Qué es una expresión de tabla común?

Un CTE (no recursivo) se trata de manera muy similar a otras construcciones que también se pueden usar como expresiones de tablas en línea en SQL Server. Tablas derivadas, Vistas y funciones valoradas en la tabla en línea. Tenga en cuenta que, aunque BOL dice que un CTE “puede considerarse como un conjunto de resultados temporal”, esta es una descripción puramente lógica. La mayoría de las veces no está materializado en sí mismo.

¿Qué es una tabla temporal?

Esta es una colección de filas almacenadas en páginas de datos en tempdb. Las páginas de datos pueden residir parcial o totalmente en la memoria. Además, la tabla temporal puede indexarse ​​y tener estadísticas de columnas.

Datos de prueba

 CREATE TABLE T(A INT IDENTITY PRIMARY KEY, B INT , F CHAR(8000) NULL); INSERT INTO T(B) SELECT TOP (1000000) 0 + CAST(NEWID() AS BINARY(4)) FROM master..spt_values v1, master..spt_values v2; 

Ejemplo 1

 WITH CTE1 AS ( SELECT A, ABS(B) AS Abs_B, F FROM T ) SELECT * FROM CTE1 WHERE A = 780 

Plan 1

Aviso en el plan anterior no hay mención de CTE1. Simplemente accede a las tablas base directamente y se trata de la misma manera que

 SELECT A, ABS(B) AS Abs_B, F FROM T WHERE A = 780 

Reescribir materializando el CTE en una tabla temporal intermedia sería masivamente contraproducente.

Materializar la definición de CTE de

 SELECT A, ABS(B) AS Abs_B, F FROM T 

Implicaría copiar alrededor de 8 GB de datos en una tabla temporal, pero aún queda la sobrecarga de seleccionarlos también.

Ejemplo 2

 WITH CTE2 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY A) AS RN FROM T WHERE B % 100000 = 0) SELECT * FROM CTE2 T1 CROSS APPLY (SELECT TOP (1) * FROM CTE2 T2 WHERE T2.A > T1.A ORDER BY T2.A) CA 

El ejemplo anterior toma aproximadamente 4 minutos en mi máquina.

Solo 15 filas de los 1,000,000 de valores generados aleatoriamente coinciden con el predicado, pero el costoso escaneo de tabla ocurre 16 veces para localizarlos.

enter image description here

Este sería un buen candidato para materializar el resultado intermedio. La reescritura de la tabla temporal equivalente tomó 25 segundos.

 INSERT INTO #T SELECT *, ROW_NUMBER() OVER (ORDER BY A) AS RN FROM T WHERE B % 100000 = 0 SELECT * FROM #T T1 CROSS APPLY (SELECT TOP (1) * FROM #T T2 WHERE T2.A > T1.A ORDER BY T2.A) CA 

Con plan

La materialización intermedia de parte de una consulta en una tabla temporal a veces puede ser útil incluso si solo se evalúa una vez, cuando permite recomstackr el rest de la consulta aprovechando las estadísticas del resultado materializado. Un ejemplo de este enfoque es en el artículo de SQL Cat Cuándo romper consultas complejas .

En algunas circunstancias, SQL Server usará un spool para almacenar en caché un resultado intermedio, por ejemplo, de un CTE, y evitar tener que volver a evaluar ese subárbol. Esto se analiza en el elemento Connect (migrado). Proporcione una sugerencia para forzar la materialización intermedia de CTE o tablas derivadas . Sin embargo, no se crean estadísticas sobre esto e incluso si el número de filas en spool fuera enormemente diferente de lo estimado no es posible que el plan de ejecución en curso se adapte dinámicamente en respuesta (al menos en las versiones actuales. Los planes de consulta adaptativa pueden hacerse posibles en el futuro).

CTE tiene sus usos, cuando los datos en el CTE son pequeños y hay una gran mejora en la legibilidad como en el caso de las tablas recursivas. Sin embargo, su rendimiento no es mejor que las variables de tabla y cuando se trata de tablas muy grandes, las tablas temporales superan significativamente a CTE. Esto se debe a que no puede definir índices en un CTE y cuando tiene una gran cantidad de datos que requieren unirse a otra tabla (CTE es simplemente como una macro). Si se une a varias tablas con millones de filas de registros en cada una, CTE tendrá un rendimiento significativamente peor que las tablas temporales.

Las tablas de temperatura siempre están en el disco, por lo que siempre que su CTE pueda mantenerse en la memoria, lo más probable es que sea más rápido (como una variable de tabla también).

Pero, de nuevo, si la carga de datos de su CTE (o variable de tabla temporal) es demasiado grande, también se almacenará en el disco, por lo que no hay grandes beneficios.

En general, prefiero un CTE sobre una tabla temporal ya que se ha ido después de que lo usé. No necesito pensar en descartarlo explícitamente ni nada.

Entonces, al final no hay una respuesta clara, pero personalmente, preferiría CTE a las tablas temporales.

CTE no tomará ningún espacio físico. Es solo un conjunto de resultados que podemos usar join.

Las tablas temporales son temporales. Podemos crear índices, constriñe como tablas normales para eso necesitamos definir todas las variables.

Alcance de la tabla de temperatura solo dentro de la sesión. EX: abrir dos ventana de consulta SQL

 create table #temp(empid int,empname varchar) insert into #temp select 101,'xxx' select * from #temp 

Ejecute esta consulta en la primera ventana y luego ejecute la consulta siguiente en la segunda ventana para encontrar la diferencia.

 select * from #temp 

Por lo tanto, la consulta que se me asignó para optimizar se escribió con dos CTE en el servidor SQL. Estaba tomando 28 seg.

Pasé dos minutos convirtiéndolos en tablas temporales y la consulta tardó 3 segundos

Agregué un índice a la tabla temporal en el campo en el que se estaba uniendo y lo bajé a 2 segundos

Tres minutos de trabajo y ahora funciona 12 veces más rápido eliminando CTE. Personalmente, no usaré los CTE alguna vez sean más difíciles de depurar también.

Lo más loco es que los CTE solo se usaron una vez y aún así, ponerles un índice resultó ser un 50% más rápido.

He usado ambos, pero en los procedimientos complejos masivos siempre he encontrado que las tablas temporales son mejores para trabajar y más metódicas. Los CTE tienen sus usos, pero generalmente con datos pequeños.

Por ejemplo, he creado sprocs que regresan con resultados de cálculos grandes en 15 segundos, pero convierten este código para que se ejecute en un CTE y lo he visto funcionar en más de 8 minutos para lograr los mismos resultados.

Tarde a la fiesta, pero …

El entorno en el que trabajo está muy restringido, respalda algunos productos de proveedores y proporciona servicios de “valor agregado” como los informes. Debido a las limitaciones de las políticas y los contratos, normalmente no se me permite el lujo de espacio separado para tablas / datos y / o la capacidad de crear código permanente [mejora un poco, dependiendo de la aplicación].

IOW, generalmente no puedo desarrollar un procedimiento almacenado o UDF o tablas temporales, etc. Tengo que hacer casi todo a través de la interfaz de mi aplicación (Crystal Reports – agregar / vincular tablas, establecer las cláusulas de w / en CR, etc. ) Una PEQUEÑA salvaguarda es que Crystal me permite usar COMANDOS (así como también expresiones de SQL). Algunas cosas que no son eficientes a través de la capacidad de agregar / vincular tablas regulares pueden hacerse definiendo un Comando SQL. Utilizo CTE para eso y obtuve muy buenos resultados “de forma remota”. Los CTE también ayudan a informar el mantenimiento, no requieren que se desarrolle el código, se entregan a un DBA para comstackr, encriptar, transferir, instalar y luego requerir pruebas de múltiples niveles. Puedo hacer CTE a través de la interfaz local.

El inconveniente de utilizar CTE con CR es que cada informe es independiente. Cada CTE debe mantenerse para cada informe. Donde puedo hacer SP y UDF, puedo desarrollar algo que pueda ser utilizado por múltiples informes, requiriendo solo enlaces al SP y pasando parámetros como si estuviera trabajando en una tabla normal. CR no es realmente bueno en el manejo de parámetros en Comandos SQL, por lo que puede faltar ese aspecto del aspecto CR / CTE. En esos casos, por lo general bash definir el CTE para devolver suficientes datos (pero no TODOS los datos), y luego uso las capacidades de selección de registros en CR para cortar y cortar.

Entonces … mi voto es para CTE (hasta que obtenga mi espacio de datos).

Un uso en el que encontré el excelente desempeño de CTE fue donde necesitaba unirme a una consulta relativamente compleja en algunas tablas que tenían unos pocos millones de filas cada una.

Utilicé el CTE para seleccionar primero el subconjunto basado en las columnas indexadas para primero cortar estas tablas a unos miles de filas relevantes cada una y luego unirme al CTE a mi consulta principal. Esto redujo exponencialmente el tiempo de ejecución de mi consulta.

Si bien los resultados del CTE no están en la memoria caché y las variables de la tabla podrían haber sido una mejor opción, realmente solo quería probarlos y encontré el ajuste del escenario anterior.

Esta es una pregunta realmente abierta, y todo depende de cómo se usa y el tipo de tabla temporal (tabla variable o tabla tradicional).

Una tabla temporal tradicional almacena los datos en la base de datos temporal, lo que ralentiza las tablas temporales; sin embargo, las variables de tabla no.

Acabo de probar esto: tanto CTE como no CTE (donde la consulta se tipeó para cada instancia de unión) tardaron ~ 31 segundos. Sin embargo, CTE hizo que el código fuera mucho más legible, cortarlo de 241 a 130 líneas, lo cual es muy bueno. La tabla de temp por otro lado la cortó a 132 líneas, y tomó CINCO SEGUNDOS para correr. No es broma. todas estas pruebas se almacenaron en caché, todas las consultas se corrieron varias veces antes.

Desde mi experiencia en SQL Server, encontré uno de los escenarios donde CTE superó a la tabla Temp

Necesitaba usar un DataSet (~ 100000) de una Consulta compleja solo UNA VEZ en mi Procedimiento almacenado.

  • La tabla Temp estaba causando una sobrecarga en SQL donde mi Procedimiento estaba funcionando lentamente (ya que las Tablas temporales son tablas materializadas reales que existen en tempdb y Persisten durante la vigencia de mi procedimiento actual)

  • Por otro lado, con CTE, CTE persistir solo hasta que se ejecute la siguiente consulta. Por lo tanto, CTE es una práctica estructura en memoria con scope limitado. Los CTE no usan tempdb por defecto.

Este es un escenario en el que los CTE pueden ayudar a simplificar su código y superar la Tabla de temperatura. Yo había usado 2 CTE, algo así como

 WITH CTE1(ID, Name, Display) AS (SELECT ID,Name,Display from Table1 where ), CTE2(ID,Name,) AS (SELECT ID, Name,<> FROM CTE1 INNER JOIN Table2 ) SELECT CTE2.ID,CTE2. FROM CTE2 GO