Las siguientes pruebas han sido realizadas sobre SQL Server 2005 Developer x86 (32-bit) corriendo sobre una estación de trabajo física con Windows XP Professional SP2, procesador Intel Pentium-D (Dual-Core) a 3,4 GHz y 2GB de RAM.
Para cada prueba, se ha reiniciado la instancia de SQL Server, con el fin de evitar que la ejecución de consultas SQL posteriores muestren mejores tiempos de ejecución al tener en memoria RAM gran parte de las páginas de datos de las que necesita acceder (como consecuencia de la ejecución de consultas SQL anteriores). De este modo, se ha intentado garantizar que cada prueba sea realizada en igualdad de condiciones, para que se pueda comparar los resultados obtenidos, al no mantener en memoria ni páginas de datos, ni planes de ejecución, etc. Soy consciente que podía haber intentado utilizar comandos DBCC para vacíar dichas caches, pero bueno, al final me dió por reiniciar la instancia y fuera.
Todas las pruebas se han realizado sin carga, con el objetivo de garantizar que las pruebas estén realizadas en igualdad de condiciones.
Sin embargo, el hecho de realizar las pruebas en una estación de trabajo dónde hay abierto un cliente de correo, una o varias sesiones de Microsoft Internet Explorer, un software antivirus, etc., puede variar ligeramente los tiempos de ejecución. En consecuencia, hemos realizado todas las pruebas varias veces, con el objetivo de garantizar que los datos medidos aquí incluidos, reflejen lo más fielmente la realidad (dentro de lo que es un entorno de pruebas, claro ;-).
La tabla utilizada para las presentes pruebas de SELECT INTO e INSERT INTO, contiene 250.000 filas. Una exportación BCP.EXE out en formato nativo de dicha tabla, genera un fichero de 65MB de tamaño, aproximadamente al tamaño que ocupa dicha tabla en SQL Server (bueno, un poco más).
Empezamos con las pruebas y conclusiones de la comparativa de SELECT INTO vs INSERT INTO, para así analizar sus diferencias, dependiendo del Modo de Recuperación utilizando y del dimensionamiento de ficheros.
- SELECT INTO con Modo de Recuperación completo (base de datos recién creada con 3MB Datos y 1MB LOG).
Tiempo de ejecución de 10 seg. Crecimiento de ficheros hasta 58MB y 62MB de Datos y LOG respectivamente.
- SELECT INTO con Modo de Recuperación SIMPLE (base de datos recién creada con 3MB Datos y 1MB LOG).
Tiempo de ejecución de 3 seg. Crecimiento de ficheros hasta 58MB y 2MB de Datos y LOG respectivamente.
Las pruebas anteriores, demuestran de forma empírica la diferencia de rendimiento de una operación SELECT INTO en función del Modo de Recuperación utilizado, ya que al ejecutarse en Modo de Recuperación Simple (al igual que el Modo de Recuperación de Registro Masivo o Bulk-Logged) realiza un registro mínimo mientras que en un Modo de Recuperación Completo ve aumentado consideramente su tiempo de ejecución, así como las escrituras realizadas en el LOG, y también el tamaño del LOG de SQL server. Sigamos con las pruebas:
- INSERT INTO con Modo de Recuperación completo (base de datos recién creada con 3MB Datos y 1MB LOG).
Tiempo de ejecución de 13 seg. Crecimiento de ficheros hasta 56MB y 161MB de Datos y LOG respectivamente.
- INSERT INTO con Modo de Recuperación SIMPLE (base de datos recién creada con 3MB Datos y 1MB LOG).
Tiempo de ejecución de 13 seg. Crecimiento de ficheros hasta 56MB y 161MB de Datos y LOG respectivamente.
Evidentemente, INSERT INTO no es una operación de registro mínimo, por lo que en principio su ejecución generará la misma cantidad de acceso a disco, independientemente del Modo de Recuperación (Recovery). Aunque esto en principio es cierto, es muy importante tener en cuenta, que una vez finalizada la transacción, en el Modo de Recuperación Simple, el espacio ocupado podrá ser reutilizado por nuevas transacciones (ej: nuevas operaciones INSERT INTO, UPDATE, etc.), mientras que en los Modos de Recuperación Completo y de Copia Masiva, será necesario realizar un Backup Log (que tiene un coste) para así poder reutilizar dicho espacio del LOG (en caso contrario, no se podrá reutilizar, y el fichero de Log al llenarse necesitará aumentar su tamaño - AUTOGROW - algo también costoso). Por ello, debemos tener claro que INSERT INTO siempre es más costoso que SELECT INTO, pero además, el detalle de la reutilización de espacio de LOG, puede hacer aún más costosas las operaciones INSERT INTO al trabajar en Modo de Recuperación Completo o de Registro Masivo (ojo, que salvo en entornos de Data Warehouse, utilizar un Modo de Recuperación Simple es una operación de cierto riesgo).
También resulta curioso que el tamaño del fichero de LOG sea 161MB, cuando al realizar un SELECT INTO en Modo de Recuperación completo (FULL), el tamaño del fichero de LOG era de tan sólo 62MB. De hecho, en Modo de Recuperación completo (FULL), SELECT INTO tardó 10 segundos mientras que INSERT INTO tardó 13 segundos. Este comportamiento evidencia la mejora de rendimiento de SELECT INTO frente a INSERT INTO, incluso en Modo de Recuperación completo (aunque la verdadera mejora, es en Modo de Recuperación Simple o de Registro Masivo - Bulk Logged). Este es un dato que quería mostrar de forma empírica: insertar exactamente el mismo volumen de información, genera más escrituras en LOG con INSERT INTO que con SELECT INTO, lo cual, impacta directamente en el rendimiento. Entiendo, que la diferencia está en que INSERT INTO necesita almacenar más información que SELECT INTO para poder deshacer la transacción, puesto que en el caso de SELECT INTO el estado anterior es... la nada, mientras que con INSERT INTO realmente existía una tabla con o sin filas, y con o sin índices, siendo necesaria su vueltas atrás en caso de desastre. Pero vamos, que soy un amateur en este tipo de detalles... no hacerme demasiado caso...
Llegados a este punto, parece que está claro que SELECT INTO presenta un rendimiento muy superior a INSERT INTO, especialmente en el Modo de Recuperación Simple, cómo hemos demostrado con los datos obtenidos en las pruebas realizadas. Sigamos con las pruebas:
- SELECT INTO con Modo de Recuperación completo (base de datos recién creada con 100MB de Datos y 100MB de LOG).
Tiempo de ejecución de 1 seg. Los ficheros de Datos y LOG no han crecido.
- SELECT INTO con Modo de Recuperación SIMPLE (base de datos recién creada con 100MB de Datos y 100MB de LOG).
Tiempo de ejecución de 0 seg (instantáneo, como el café ;-). Los ficheros de Datos y LOG no han crecido.
En la prueba que acabamos de realizar con SELECT INTO sobre una base de datos bien dimensionada (a diferencia de la prueba anterior, mismo SELECT INTO sobre una base de datos sin dimensionar), hemos vuelto a obtener una enorme diferencia: en este caso, hemos podido comprobar que el correcto dimensionamiento de una base de datos, permite mejorar enormemente el rendimiento. Esto era más que evidente, pues el hecho de tener que aumentar el tamaño de los ficheros de base de datos, se trata de una operación de entrada/salida bastante costosa, en cualquier motor de base de datos. Resulta especialmente interesante poder comparar el INSERT INTO de 13 seg. y 161 Mb de Log, con este SELECT INTO equivalente de 0 seg. Aunque no está incluido en esta batería de pruebas, también quiero aprovechar para comentar que resulta vital la defragmentación, por lo que es importante crear "de una atacada" nuestros ficheros de base de datos y reindexar con frecuencia (dejar que crezcan, y reducirlos manualmente, genera gran fragmentación tanto en base de datos como en sistema de ficheros... y con el tiempo, se nota !! ).
- INSERT INTO con Modo de Recuperación completo (base de datos recién creada con 100MB Datos y 100MB LOG).
Tiempo de ejecución de 3 seg. Crecimiento del fichero de datos hasta 162MB.
- INSERT INTO con Modo de Recuperación SIMPLE (base de datos recién creada con 100MB Datos y 100MB LOG).
Tiempo de ejecución de 3 seg. Crecimiento del fichero de datos hasta 161MB.
Por último, nos quedaba comprobar la ejecución de INSERT INTO sobre una base de datos bien dimensionada. Aquí también se ha mejorado enormemente el rendimiento, y aunque no se ha conseguido un tiempo de ejecución instantáneo (como ocurrió con SELECT INTO), el hecho de reducir de 13 seg. a 3 seg., resulta una mejora más que sustancial.
En fin, tenía ganas de realizar estas pruebas y publicarlas por aquí, ya que me parece bastante interesante poder tener estas medidas realizadas de forma objetiva y disponibles... Espero que también os resulte útil a quienes me leáis.
