En estos días que parece estar todo un poco más relajado que de costumbre, he aprovechado para hacer un pequeño test para comprobar en qué medida puede afectar en los INSERT tener índices definidos en una tabla. Todos sabemos que una de las formas de optimizar las inserciones es que no existan índices, pero ¿qué hay de verdad en esto? ¿Hasta qué punto es negativo? Para comprobarlo he generado dos tests. La diferencia entre ambos es que en el primero se ejecutan 100.000 inserciones individuales dentro de un bucle, mientras que en el segundo se hace en una sola instrucción (INSERT … SELECT del mismo número de registros).

La tabla sobre la que se realizan las operaciones tiene una clave primaria IDENTITY, un campo CHAR(80) y otro VARCHAR(80) con el mismo valor predeterminado (una cadena de 14 caracteres), y por último un campo DATETIME con la fecha actual como valor predeterminado.

En ambos test, realizados sobre un SQL Server 2008 RTM 32x, Dual Core y 3Gb RAM, se comprueba el tiempo que se tarda en realizar todas esas inserciones:

  • Sin ningún tipo de índice
  • Con un índice sobre el campo CHAR(80)
  • Con un índice sobre el campo VARCHAR(80)
  • Con un índice sobre el campo DATETIME
  • Con un índice sobre los campos DATETIME y CHAR(80)
  • Con un índice sobre los campos CHAR(80) y DATETIME
  • Con un índice sobre los campos CHAR(80) y DATETIME y otro índice sobre CHAR(80)

Los resultados (en milisegundos) de ejecutar 10 veces cada uno de los test (la última fila son los valores medios) son los siguientes:

1

2

3

4

5

6

7

Bucle

 

4846

11123

6956

5773

8020

15523

18593

5150

11013

9430

6420

7426

11310

16940

4836

10983

6566

5820

7380

13840

20933

5786

13540

8253

6396

7880

12633

19503

6833

14196

9376

7990

8980

15256

23666

6176

14866

11406

9640

9703

17270

23806

6683

10656

6396

5460

6646

10746

15976

4570

10406

5990

5773

7036

11323

16400

4896

10893

6503

5856

6923

10373

16286

4400

10983

6756

6206

7006

11310

17096

5418

11866

7763

6533

7700

12958

18920

Masivo

 

1293

8100

2696

1856

7973

7973

14756

1120

7643

3183

1763

8033

7973

14666

1896

8570

3570

2043

8626

7973

15053

1123

7896

3056

1840

8363

8063

15146

1090

7740

3196

1826

8266

8220

15866

1373

7940

3573

1920

8473

8190

15243

1030

7786

3493

1950

8736

8656

15150

1476

8303

2810

1870

8550

7923

14620

1323

8910

3136

1950

8203

9126

18316

1200

8643

3150

2246

8893

9003

16846

1292

8153

3186

1926

8412

8310

15566

 

Resultaba obvio pensar que las inserciones con índices son siempre más lentas que sin ellas, pero los datos lo confirman: en el caso del bucle suponen casi el doble de media, mientras que en el otro caso son casi seis veces más.

Los datos también confirman que, cuanto más “ancho” es el índice (cuanto más ocupan las columnas que lo componen), más tarda la inserción.

Otra obviedad es que, a más índices, más lento se vuelve la inserción: siempre respecto de la inserción más lenta, en el caso del bucle supone 1.5 veces más, mientras que en el caso de la inserción masiva, supone casi el doble.

También era lógico pensar que un INSERT… SELECT de 100.000 registros iba a suponer menos tiempo que un bucle del mismo número de iteraciones en las que en cada una de ellas se inserta un registro. Pero aquí acaban las evidencias y empiezan las cosas curiosas:

Por ejemplo, ¿por qué el test número 5 (índice DATETIME+CHAR(80) ) es más lento en la inserción masiva que en el bucle? Es el único caso en el que esto ocurre. De hecho, sería lógico pensar que el test número 5 y el 6 arrojaran valores similares, ya que lo componen los mismos campos con la única diferencia del orden en los mismos. Sin embargo, mientras que en la inserción masiva arroja valores prácticamente iguales (apenas un 1% de desviación), para el bucle hay una gran diferencia a favor del test número 5 (40% más rápido de media). Es más, si vemos las pruebas del menor a menor tiempo por tamaño del índice, vemos que:

Test núm.

Posición

B

M

4 (8 bytes)
3 (14 bytes)
2 (80 bytes)
5 (88 bytes)
6 (88 bytes)
7 (88 + 8 bytes)

Realmente el que se adapta a la lógica que podríamos presuponer es el test de inserción masiva (columna “M”), en el que podemos ver que a mayor tamaño del índice, más tarda en ejecutarse la operación. Sin embargo, para las inserciones individuales (columna “B”) hay un baile de números curioso, apareciendo como invitado sorpresa el test número 5 en el segundo lugar. Podríamos pensar que a SQL Server le gustan más los números en la primera columna de un índice (un campo fecha internamente no es más que un número) que los caracteres, pero entonces esa misma conclusión deberíamos aplicarla respecto a la inserción masiva y, sin embargo, vemos que esto no se cumple.

La verdad es que no se me ocurre ahora mismo ninguna razón que explique el por qué de este comportamiento. He repetido el test varias veces y siempre da valores similares. Si alguno de vosotros tiene alguna idea al respecto, que deje un comentario, a ver si entre todos somos capaces de comprenderlo.

Dejando este último punto a un lado, ahora ya podemos saber cómo afecta tener índices de más (duplicados parcial o totalmente, o poco usados) en nuestra base de datos. No hemos descubierto nada que no intuyéramos, pero al menos ya podemos corroborarlo con cifras.

 

0 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada.

You May Also Like

Excel conectado a Analysis Services y la propiedad MDX Missing Member Mode

Encontramos muchas veces Excel como herramienta que los usuarios utilizan para pre cocinar datos, crearse informes, navegar cubos… En esta entrada voy a compartir una experiencia reciente, en un escenario en el que Excel es la aplicación cliente para mostrar datos de un cubo, algo bastante común. Lo que no resulta tan común es Excel muestre un error cuando puedo ejecutar la misma consulta MDX en SQL Server Management Studio.