Como puedo mejorar el rendimiento de SELECT .... UNION????

[Luis Turcios]

Buenos dias para tod@s!
Espero puedan ayudarme con un problema que tengo con una tabla a la cual le estoy haciendo un SELECT ... UNION
y la verdad está tardando demasiado (considero)... Bueno voy al punto:

La tabla actualmente contiene en este momento un promedio de 50,000 registros de prueba... pero que en la "vida real" pueden ser unos 750,000 (quizás un poco más)
La estructura de la tabla es mas o menos así:
casilla N(5)     correlativo N(3)      grupo N(2)  v01 C(5)  v02 C(5)  v03 C(5)  v04 C(5)  v06 C(5)  v07 C(5)  v08 C(5) ....... v24 C(5)  vtotal N(2)   combinado Tipo (Logico)
hablamos de mas de 30 campos pero solo menciono los 29 que participan:

Ahora bien, el select va mas o menos de la siguiente manera:

select casilla, correlativ, valor, grupo, vtotal FROM ;
(select casilla, correlativ, v01 as valor, grupo, vtotal from mitabla WHERE (BETWEEN(casilla,1, 3000) AND correlativ > 0 AND grupo=0 AND vtotal<24 AND combinado = .f.) OR (BETWEEN(casilla, 1, 3000) AND correlativ > 0 AND grupo>0 AND BETWEEN(vtotal, 1, 23)) UNION ;
select casilla, correlativ, v02 as valor, grupo, vtotal from mitabla WHERE (BETWEEN(casilla,1, 3000) AND correlativ > 0 AND grupo=0 AND vtotal<24 AND combinado = .f.) OR (BETWEEN(casilla, 1, 3000) AND correlativ > 0 AND grupo>0 AND BETWEEN(vtotal, 1, 23)) UNION ;
select casilla, correlativ, v03 as valor, grupo, vtotal from mitabla WHERE (BETWEEN(casilla,1, 3000) AND correlativ > 0 AND grupo=0 AND vtotal<24 AND combinado = .f.) OR (BETWEEN(casilla, 1, 3000) AND correlativ > 0 AND grupo>0 AND BETWEEN(vtotal, 1, 23)) UNION ;
.....................
select casilla, correlativ, v24 as valor, grupo, vtotal from mitabla WHERE (BETWEEN(casilla,1, 3000) AND correlativ > 0 AND grupo=0 AND vtotal<24 AND combinado = .f.) OR (BETWEEN(casilla, 1, 3000) AND correlativ > 0 AND grupo>0 AND BETWEEN(vtotal, 1, 23)) AS ff WHERE !EMPTY(valor) into cursor ffa

pues con 50,000 registros este Select se tarda aproximadamente 30 segundos!
el cursor obtenido (con esta cantidad de registros en mitabla) contiene alrededor de 400,000 registros.

la tabla la tenia con un indice en los campos casilla y correlativ.... y he probado modificandolo y lo dejé así:

indice = casilla+correlativ+grupo+vtotal

y pues la verdad no se agiliza en nada, no sé si habrá alguna forma de establecer o configurar un mejor índice que realemente le ayude a la consulta.

Consideran que hay posibilidades de poder mejorar este rendimiento? o será que sinceramente no puedo obtener nada mejor a menos que utilice un servidor de base de datos?

Espero puedan ayudarme,

Mi más sincera gratitud anticipadamente.

[Carlos Miguel FARIAS]

Para mejorar la velocidad de tu proceso puedes mejorar el SQL con algunas cositas.

En lugar de usar la función BETWEEN(que, desde, hasta) utiliza: que BETWEEN desde AND hasta, así transfieres lógica de selección de registros de una función de fox al motor de rushmore.

Donde tienes las cadenas de operadores lógicos  tal cosa AND tal otra AND etc, tienes que ubicar primero las condiciones que menos se cumplan, porque en una cadena de operaciones AND, en cuanto una no se cumpla, ya no analiza el resto y eso gana en velocidad.

Por el contrario, cuando tienes un grupo OR, pasa con esta OR pasa con esta otra, tienes que poner primero la que más se cumple, porque con que se cumpla una sola.

Hasta aquí sería una aceleración del proceso general para cualquier SQL.

Los indices combinados no sirven para optimizar porque los datos no aparecen de esa forma para SQL, es mejor en todo caso tener indices por cada campo por separado.

En tu caso específico, veo que en todos los selects filtras por ciertas condiciones comunes (como BETWEEN(casilla,1, 3000) AND correlativ > 0), entonces, como esto se repite siempre, lo más lógico sería crear un curso con esas condiciones.

SELECT * FROM mitabla;

   WHERE casilla BETWEEN 1 AND 3000 AND correlativ > 0

   INTO CURSOR cursor1

No me queda claro porque en varios lugares colocas de un lado del OR vTotal<24 y en otro BETWEEN(vTotal, 1, 23), porque si los valores de vTotal son enteros y positivos, la única diferencia entre una y otra sería que en el segundo caso no selecciona los vTotal = 0 y en el primero si (y si hubiese negativos, también)

Es más, en lugar de *, pondría exclusivamente los campos que realmente usaras en todo el proceso (eso achicará el cursor) salvo que en realidad uses todos.

Luego, tu siguiente proceso sería hacer la selección como lo planteas pero a partir del cursor1, de esa manera, tu cursor va a ser mucho más chico (bueno, eso dependerá de los datos), pero tu siguiente SELECT UNION, elimina todas esas condiciones comunes de selección, resultará en un select mucho más pequeño.

Lo que propongo, supongo podrá perfeccionarse o repensarse de otra manera.

Saludos: Miguel, La Pampa (RA)

Larga Vida y Prosperidad

Que la Fuerza los acompañe

[Luis Turcios]

Buenos días Miguel,

Disculpa que no te haya agradecido antes por tu valiosa ayuda ya que por la mañana leí tu respuesta pero fue hasta horas de la noche que empecé a poner en práctica tus consejos.

Primero que nada dejame decirte que estoy muy agradecido y admirado por la perspectiva y lógica planteada ante mi problema, no me habia detenido a verlo todo de esa manera.

Ahora te pongo al tanto de lo que he hecho y los respectivos resultados:

• He insertado más registros para la prueba, ahora tengo 150,000
• Modifiqué los indices, eliminé el combinado y dejé un indice por cada una de las columnas que participan en la condición (casilla, correlativ, grupo, vtotal
  
• Con el Query tal como lo tenía pues se tardaba unos 95 segundos
• Empecé por lo del BETWEEN, aplicandolo de la forma como lo aplico en MySQL... no sabía que se podía ejecutar en VFP tambien de esa forma.
• Continué simplificando las condiciones, eliminando condiciones "comunes"
• Luego incluso realicé lo que sugerías de crear un cursor previo con los registros que coincidían con las condiciones "comunes"

El cursor lo creo de la siguiente manera:

select casilla, correlativ, grupo, vtotal, ;
 v01, v02, v03, v04, v05, v06, v07, v08, ;
 v09, v10, v11, v12, v13, v14, v15, v16, ;
 v17, v18, v19, v20, v21, v22, v23, v24 FROM mitabla WHERE casilla BETWEEN 1 AND 3000 AND correlativ > 0 AND combinado = .F. AND ((grupo>0 AND vtotal BETWEEN 1 AND 23 ) OR (grupo=0 AND vtotal<24 ))INTO CURSOR miCursor

luego los SELECT .... UNION lo tengo así:

select casilla, correlativ, valor, grupo, vtotal FROM ;

(select casilla, correlativ, v01 as valor, grupo, vtotal from DBF('miCursor') UNION ;
select casilla, correlativ, v02 as valor, grupo, vtotal from DBF('miCursor') UNION ;
......
select casilla, correlativ, v24 as valor, grupo, vtotal from DBF('miCursor') ) as ff WHERE !EMPTY(valor) INTO CURSOR ffa

A pesar de todo esto quiero decirte que no ha mejorado, sigue tardándose los 95 segundos!
Luego se me ocurrió poner modificar el SELECT.... UNION, dejandolo de la siguiente forma:

select casilla, correlativ, valor, grupo, vtotal FROM ;

(select casilla, correlativ, v01 as valor, grupo, vtotal from DBF('miCursor') WHERE !EMPTY(v01) UNION ;
select casilla, correlativ, v02 as valor, grupo, vtotal from DBF('miCursor') WHERE !EMPTY(v02) UNION ;
......
select casilla, correlativ, v24 as valor, grupo, vtotal from DBF('miCursor') ) as ff WHERE !EMPTY(valor) INTO CURSOR ffa



Esa modificación logró una leve mejora, ya que ahora el tiempo es de 76 segundos!

Bueno, dejo todo esto para mantenerte al tanto y ver si se te ocurre algo más que se pueda hacer.

Nuevamente muchas gracias Miguel,

Saludos desde El Salvador!

[Carlos Miguel FARIAS]

Igual te aclaro que si tarda lo que indicas, no es un tiempo desesperante si lo tienes que hacer una vez al dia, si lo tienes que hacer una vez por minuto, evidentemente estas al horno (un programador argentino igualmente estaría más rico, porque aca estamos al horno con Papa)