¿Cuáles son los buenos principios para calcular cuál es más rápido y por qué? Yo pensaría que Microsoft optimizaría para hacer insert into , igual de rápido, para una programación cuidadosa.
Los principios que trato de seguir cuando analizo algo como esta pregunta son:
- Evitar hacer suposiciones innecesarias.
- Leer la documentación oficial.
- Probar la carga de trabajo. La cantidad de pruebas depende de lo rápido que necesite que sea el código.
Conozco dos piezas de documentación que abordan tu pregunta. La primera es una entrada del blog que dice que SELECT INTO para las tablas temporales tiene un comportamiento diferente para las escrituras ansiosas a partir de SQL Server 2014. Eso es por diseño. Así que no creo que sea correcto decir que la diferencia es mínima para las tablas pequeñas. En todo caso, la optimización descrita en la entrada del blog parece diseñada para tablas pequeñas:
El cambio en SQL Server 2014 es relajar la necesidad de vaciar estas páginas, tan rápidamente, a los archivos de datos TEMPDB. Cuando se hace un select into … #tmp … o se crea un índice WITH SORT IN TEMPDB el SQL Server reconoce ahora que esta puede ser una operación de corta duración. Las páginas asociadas a dicha operación pueden ser creadas, cargadas, consultadas y liberadas en una ventana de tiempo muy pequeña.
Por ejemplo: Puedes tener un procedimiento almacenado que se ejecute en 8ms. En ese procedimiento almacenado se selecciona en … #tmp … y luego se utiliza el #tmp y se suelta a medida que el procedimiento almacenado se completa.
Antes del cambio de SQL Server 2014 el select into puede haber escrito todas las páginas acumuladas en el disco. El SQL Server 2014, comportamiento de escritura ansiosa, ya no fuerza estas páginas al disco tan rápidamente como las versiones anteriores. Este comportamiento permite que las páginas se almacenen en la memoria RAM (buffer pool), se consulten y la tabla se abandone (se elimine del buffer pool y se devuelva a la lista de libres) sin llegar nunca al disco mientras haya memoria disponible. Al evitar la E/S física cuando es posible, el rendimiento de la TEMPDB, la operación a granel se incrementa significativamente y reduce el impacto en los recursos de la ruta de E/S también.
La segunda pieza de documentación explica que la inserción paralela en tablas temporales con INSERT INTO ... SELECT está disponible sin una TABLOCK pista en SQL Server 2016 pero requiere una TABLOCK pista en SP1 y en futuras versiones.
El problema se soluciona por primera vez en SQL Server 2016 Service Pack 1 . Después de aplicar SQL Server 2016 SP1, los INSERT paralelos en INSERT..SELECT a tablas temporales locales están deshabilitados de forma predeterminada, lo que reduce la contención en la página PFS y mejora el rendimiento general para la carga de trabajo concurrente. Si se desea realizar INSERT paralelos a tablas temporales locales, los usuarios deben utilizar la sugerencia TABLOCK mientras se inserta en la tabla temporal local.
Volviendo a su declaración original, no se puede deducir lógicamente cuál de los dos será más rápido. Lo que es más rápido depende de cómo Microsoft diseñó el software y de las características de su carga de trabajo. Hacer conjeturas sobre la cantidad de tiempo necesario para crear las definiciones de las columnas no es útil. Las pruebas son útiles. Si sus pruebas sugieren que SELECT INTO es más rápido, entonces siga con eso. Por si sirve de algo, yo también trabajo en la carga de almacenes de datos con un ojo puesto en el rendimiento y no he visto que la diferencia entre los dos enfoques sea algo de lo que merezca la pena preocuparse.