Optimizando inserciones múltiples en SQL server y el mito del blogger gurú

Empezará por el final, por la moraleja: no hay que fiarse de todo aquél que escribe un blog y por supuesto tampoco hay que considerarlo un gurú. Esta afirmación no deja de ser paradójica ya que me incluye a mi mismo aunque ni mucho menos afirmo ser un gurú... pero en fin dejémoslo.

Imaginad que tenéis una tabla en una base de datos que se debe poblar de forma masiva con una información ya conocida que tras ser procesada mediante un script PHP o similar se materializará en miles de registros. En mySQL existe una bonita sintaxis que si no recuerdo mal es algo así como

INSERT INTO tabla (campo1,...,campoN)
          (valor1_1,..., valor1_N),
             ...
          (valorN_1,..., valorN_N)

Lamentablemente esto no existe en SQL Server que es el gestor de base de datos con el que me enfrento a diario, así que me puse a buscar en google alguna solución que me evitara lo que venimos haciendo hasta ahora:

INSERT INTO tabla (campo1,...,campoN) VALUES  (valor1_1,..., valor1_N)
...

INSERT INTO tabla (campo1,...,campoN) VALUES  (valorN_1,..., valorN_N)

Así es como fui a dar con un blog donde se proponía la siguiente opción como algo bastante más eficiente:

INSERT INTO tabla (campo1,...,campoN)
SELECT valor1_1,..., valor1_N
UNION ALL
SELECT valor2_1,..., valor2_N
...
UNION ALL

SELECT valorN_1,..., valorN_N

A partir de ahí un montón de comentarios al post original en plan, "¡fantástico!", "¡lo he probado con 200 registros y gano x ms!", que van degenerando poco a poco en los típicos "Lo he probado y me devuelve un error..." y la consiguiente respuesta "Has escrito mal esto o lo otro". Debido a esto y a que había más de 100 comentarios cometí mi primer error leyendo sólo una docena de ellos. El segundo fue no sospechar del operador UNION y el tercero simplemente darlo por bueno.

¿Y por qué no darlo por bueno? Era una solución escrita en un blog. Seguro que el tipo que aparece fotografiado es un gurú del SQL. Sólo había que ver el nombre del blog: SQLAuthority, como para no fiarse (leer esto imaginando la voz de Robert de Niro en Taxi Driver):

¡Joder tío! ¡Ese tipo es la puta autoridad del SQL, tío!

Tan convencido estaba de mi "descubrimiento" que escribí un mail a unos cuantos compañeros para que se aprovecharan de ello.

Afortunadamente un día después empecé a hacer algunas pruebas. A mi me interesaba que fuese rápido insertando unos 50.000 registros en una tabla con unos 20 campos, tres o cuatro de texto y el resto numéricos. Tras montar la consulta y ejecutarla en el servidor... sorpresa: el motor de la base de datos devuelve un error porque la consulta es demasiado grande. Empiezo a reducir la consulta para encontrar el límite: 25000 inserciones,10000, 5000... y así hasta que finalmente traga con 1000, bastante desesperanzador para mi propósito.

Aún así me empeñé en compararlo con el método que venía utilizando y... ¡era más lento! Tras varias pruebas siempre tardaba un par de segundos más. El método de siempre tardaba unos 8 segundos y el propuesto unos 10. Menuda decepción, además con el tradicional me ahorraba el problema del error con el tamaño de la consulta.

No me lo podía creer y la gente del blog maravillada con el invento. Volví al post y seguí leyendo. Había alguno que llegaba a las mismas conclusiones que yo pero la mayoría de la gente estaba encantada. El autor en un momento dado reconocía que existía el límite del tamaño de la consulta pero que el conseguía insertar así varios miles de registros.

En fin, a mi no me sirvió y acepto sugerencias a mi problema: quiero hacer unas 50.000 inserciones en una tabla sin tener que ejecutar 50.000 INSERTs y además que me resulte más rápido ¿alguna solución? Yo he dado con una que me sirve debido a las particularidades del entorno de producción que tenemos, o sea, no es una solución universal. La explicaré en otro post. Por cierto, envié un segundo mail a mis compañeros recomendando olvidar el primero.