Optimizando
el Rushmore
Por Jaime Ferrer
Una de las características más alabadas
de Visual Foxpro es su velocidad realizando consultas en sus tablas nativas. La
tecnología que lo hace posible fue llamada Rushmore y es ampliamente
aprovechada por todos los programadores VFP. Básicamente consiste en que VFP
utiliza de forma transparente los índices para filtrar y combinar más
rápidamente las tablas que intervienen en una operación. Tan efectivo es que
alguna vez he leído que una de las
razones por las que Microsoft compro el VFP fue para poder implementar esta
tecnología en SQL-Server.
Sin embargo, todos sabemos que un SELECT
nunca es bastante rápido, y muchas veces quedamos decepcionados cuando vemos que, a pesar de haber creado
índices en todos los campos y expresiones que intervienen en la consulta, ésta
aún se demora unos interminables segundos, si es que no son minutos.
Afortunadamente, a menudo el problema
viene del mismo Rushmore y se soluciona fácilmente. Vamos a poner un ejemplo.
He creado una base de datos y le he añadido tres tablas con las siguientes instrucciones:
CREATE TABLE maestro1
(m1_clave I, m1_tipo N(1))
CREATE TABLE maestro2
(m2_clave I, m2_tipo I)
INDEX ON m2_clave TAG
CLAVE
INDEX ON m2_tipo TAG TIPO
CREATE TABLE transacciones
(tra_clave1 N(1), tra_clave2 I)
INDEX ON tra_clave1 TAG
CLAVE1
INDEX ON tra_clave2 TAG
CLAVE2
La tabla maestro1 la he rellenado con 9
registros, en tres grupos de tres con
tipos 1, 2 y 3, es decir, existen tres registros de cada tipo. Esta tabla no
tiene ningún índice, pues al ser tan pequeña no es necesario.
La tabla maestro2 contiene 100.000 registros y el
rango de tipos va de 1 a 999 distribuidos aleatoriamente.
La tabla transacciones contiene 2.000.000 de
registros y sus campos se han rellenado aleatoriamente con las claves de los
maestros correspondientes.
Cogemos
la tabla maestro2 y cambiamos el tipo de 4 registros a 1000.
Ponemos el SET
DELETED OFF
Bien, ya tenemos los datos preparados para la prueba.
Primer intento, probemos con:
SELECT COUNT(*) FROM transacciones;
JOIN
maestro1 ON tra_clave1=m1_clave;
JOIN
maestro2 ON tra_clave2=m2_clave;
WHERE m1_tipo=2 AND m2_tipo=1000
En teoría existen índices para todos los campos
involucrados en la consulta, menos para m1_clave. La primera vez que ejecuto el
SELECT me tarda 48 segundos, la segunda vez, con los buffers ya llenos, me
tarda sólo 4, todo ello para darme un resultado de 34 registros contados.
Utilizo un Pentium IV a 1.7 Gh con Windows XP y estoy accediendo a los datos
del servidor Windows 2000 por una red de 10Mb/seg.
Bien, me esperaba algo mejor. Si activamos SYS(3054,
11) el informe de optimización nos dice:
Nivel de optimización Rushmore para tabla transacciones: nada
Nivel de optimización Rushmore para tabla maestro1: nada
Utilizando índice etiqueta Tipo para optimizar con Rushmore tabla maestro2
Nivel de optimización Rushmore para tabla maestro2:completo
Combinando tabla maestro1 y tabla transacciones utilizando índice etiqueta Clave1
Combinando tabla maestro2 y resultado intermedio con índice temporal
Esto no es lo que nosotros hubiésemos hecho “a mano”.
Vemos que Rushmore ha combinado primero las transacciones con el maestro1. Dado
que la tercera parte de registros esta asociado a un registro del maestro1 con
tipo 2, esto nos da un resultado intermedio de aproximadamente
2.000.000/3=666.666 registros. Si se hubiese combinado primero la tabla de
transacciones con el maestro2, el primer resultado intermedio hubiera dado, en
mi caso, de sólo 98.
Por tanto hemos de encontrar la manera de decirle al
fox que combine las tablas en el orden adecuado. Probemos cambiando el orden de
los JOIN y, por si acaso, el orden de las condiciones en el WHERE.
SELECT COUNT(*) FROM transacciones;
JOIN maestro2 ON tra_clave2=m2_clave;
JOIN maestro1 ON tra_clave1=m1_clave;
WHERE m2_tipo=1000 AND m1_tipo=2
Ni caso, nos hace exactamente lo mismo
que en el caso anterior. Es una característica del Rushmore, él escoge el orden
que le parece más apropiado, a menos que pongamos la cláusula FORCE. Vamos a
intentarlo otra vez.
SELECT COUNT(*) FROM FORCE transacciones;
JOIN maestro2 ON tra_clave2=m2_clave;
JOIN maestro1 ON tra_clave1=m1_clave;
WHERE m2_tipo=1000 AND m1_tipo=2
Esto me tarda unos 25 segundos la primera vez, ¡pero
12 segundos la segunda y sucesivas¡. Veamos lo que me cuenta el Rushmore
Nivel de optimización Rushmore para tabla transacciones: nada
Nivel de optimización Rushmore para tabla maestro2: nada
Combinando tabla transacciones y tabla maestro2 utilizando índice etiqueta Clave
Nivel de optimización Rushmore para resultado intermedio: nada
Nivel de optimización Rushmore para tabla maestro1: nada
Combinando resultado intermedio y tabla maestro1 con índice temporal
Hemos conseguido que nos combine primero la tabla
transacciones con maestro2, pero el método que utiliza no es el que queremos.
En primer lugar no optimiza el filtro en la tabla maestro2, cuando sí que
existe un índice para maestro2.m2_tipo y, además, en las pruebas anteriores nos
lo estaba utilizando. En segundo lugar vemos que para combinar la tabla
transacciones con maestro2 esta utilizando el índice Clave de maestro2 en vez
del Clave2 de transacciones. Bien, probémoslo al revés:
SELECT COUNT(*) FROM FORCE maestro2;
JOIN transacciones ON tra_clave2=m2_clave;
JOIN maestro1 ON tra_clave1=m1_clave;
WHERE m2_tipo=1000 AND m1_tipo=2
Esto nos da unos decepcionantes 50 segundos el primer intento y 6 en el segundo. El informe del Rushmore da:
Nivel de optimización Rushmore para tabla maestro2: nada
Nivel de optimización Rushmore para tabla transacciones: nada
Combinando tabla maestro2 y tabla transacciones utilizando índice etiqueta Clave2
Nivel de optimización Rushmore para resultado intermedio: nada
Nivel de optimización Rushmore para tabla maestro1: nada
Combinando resultado intermedio y tabla maestro1 con índice temporal
Hemos conseguido que nos combine maestro2 y transacciones por el índice Clave2, pero no conseguimos que optimice el filtro de maestro2. Probemos un último intento desesperado:
SELECT COUNT(*) FROM FORCE maestro2;
JOIN transacciones ON tra_clave2=m2_clave AND m2_tipo=1000;
JOIN maestro1 ON tra_clave1=m1_clave;
WHERE m1_tipo=2
¡EUREKA¡, 0 segundos y 0 segundos (las centésimas no importan), el informe de Rushmore ahora sí es el que yo quiero:
Utilizando índice etiqueta Tipo para optimizar con Rushmore tabla maestro2
Nivel de optimización Rushmore para tabla maestro2: completo
Nivel de optimización Rushmore para tabla transacciones: nada
Combinando tabla maestro2 y tabla transacciones utilizando índice etiqueta Clave2
Nivel de optimización Rushmore para resultado intermedio: nada
Nivel de optimización Rushmore para tabla maestro1: nada
Combinando resultado intermedio y tabla maestro1 con índice temporal
¿Qué conclusiones
podemos sacar de todo esto?
La primera es saber cómo decide el Rushmore el orden
de combinaciones si no ponemos la cláusula FORCE. En nuestro ejemplo VFP
detecta que hay 3 registros de maestro1 que cumplen las condiciones del WHERE
por 4 de maestro2. VFP supone, y supone mal, que será más eficiente combinar
primero maestro1 con transacciones al tener sólo 3 registros en un lado de la
combinación. De hecho, si en vez de cambiar 4 registros de la tabla maestro2 al
tipo 1000, hubiésemos cambiado sólo 2, la consulta nos la habría optimizado
bien desde un principio ya que el Rushmore habría combinado primero maestro2.
La segunda y más sorprendente, (o cómo mínimo yo no
le he encontrado explicación), es que hay veces que no optimiza las condiciones
que ponemos en el WHERE, en cambio sí las optimiza si las ponemos en el JOIN.
Es importante, pues, que cuando diseñemos un SELECT
con varias tablas, nos lo pensemos dos veces cómo mínimo, hagamos pruebas con
datos reales y reflexionemos sobre las condiciones de filtrado que más les
interesarán a los usuarios. Si en el sencillo caso aquí expuesto hemos obtenido
una ganancia de varios miles por ciento, ¿qué mejoras no podremos obtener
optimizando nuestras SELECT en aplicaciones reales?
FoxPress – Septiembre de
2002