Problema serios con predicados IN, NOT IN, EXISTS, ...

[[mlara]]

Estoy aterrado , enojado , confundido , en verdad...

, y no es juego.

En la documentación de FireBird (pág. 104) dice que la existencia de predicados NOT IN y ALL pueden ser muy lentos!!! En realidad "no pueden llegar a ser" o "no podrían serlo", porque en verdad LO SON. Parece que no han implementado la característica que hace que el motor use índices cuando se trabaja con estos predicados.

Bueno, digamos que pasa... habrá cierta forma de reemplazar un NOT IN, digamos por un NOT EXISTS bien escrito.

y entonces por qué el grito?

Pues estoy ejecutando ciertas consultas en Firebird sobre una base de datos migrada desde Paradox. Con exactamente la misma información podría decir que las consultas en Firebird son TOTALMENTE INSERVIBLES mientras que en Paradox se ejecutan en un par de segundos. Qué tipo de consultas son? Simples; este es un ejemplo:

Código SQL [-]
SELECT * FROM "Tabla1" WHERE "Campo1" || "Campo2" IN (SELECT DISTINCT "Cod1" || "Cod2" FROM "Tabla2" WHERE ...)

y por qué son "prácticamente inservibles"? En este mismo momento llevo más de 30 minutos esperando por los resultados de una consulta (esto no tiene sentido, sólo hacía una prueba, ya que en consultas similares sobre otras tablas obtenía respuesta a veces en 5 minutos... finalmente se opta por parar el servicio, cerrar forzadamente las aplicaciones, y reiniciar).

Buscando un poco entendí:

Por cada registro obtenido en la consulta externa (sucede igual cuando se usa la función EXISTS), se evalúa la consulta interna, es decir: si la consulta externa devuelve mil registros (absolutamente normal), la consulta interna se abre mil veces haciendo mil evaluaciones para evaluar la condición de que los campos 1 y 2 se encuentren dentro del conjunto de resultados retornado por la consulta interna.

Me pareció muy extraño y para nada comprensible. Yo supongo que naturalmente la consulta interna debería ejecutarse una sóla vez. En memoria los resultados de esta consulta pueden conformar un resultado, si es del caso indezado, y listo. Pero no sucede así rn Firebird. Inconsebible, inadmisible...

La pregunta entonces es la siguiente, aunque creo que peco por el simple hecho de hacerla, ya que lo que no funciona pues simplemente no funciona.

¿Habrá alguna manera de agilizar estas consultas, de tal forma que el tiempo de respuesta sea razonable?

y otras preguntas obvias:

¿Sabrá alguien si el equipo de trabajo de Firebird tienen pensado solucionar tal descalabro antes de que decida usar un RDMS responsable?

PD. He probado las mismas consultas sobre Firebird 2.0.4 y Firebird 2.1.0. En ambos sistemas sucede lo mismo... TOTALMENTE INADMISIBLE... como escuché alguna vez: esto es ridículo! Es que si no se pueden usar estos predicados, entonces para qué existen.

PD2. Recuerden que en Paradox funciona de maravilla, y eso que hablo de tablas que llegan a tener hasta 1 millón de registros.

PD3. Había pensado que esperar 5 segundos era demasiado!

[[mlara]]

Esta es la consulta planteada:

Código SQL [-]
SELECT * FROM "Tabla1" WHERE "Campo1" || "Campo2" IN (
SELECT DISTINCT "Cod1" || "Cod2" FROM "Tabla2" WHERE ...
)

"Campo1" + "Campo2" deben estar dentro del conjunto de resultados, parejas diferentes compuestas por "Cod1" + "Cod2".

Si tengo un índice sobre "Tabla2" conformado por los campos "Cod1" y "Cod2", yo espero que el sistema use el índice. Al ejecutar la consulta veo que el plan adoptado realiza un ordenamiento "natural" (<natural_scan>), por lo que intento lo siguiente:

Código SQL [-]
SELECT * FROM "Tabla1" WHERE "Campo1" || "Campo2" IN (
SELECT DISTINCT "Cod1" || "Cod2" FROM "Tabla2" WHERE ...
PLAN ("Tabla2" INDEX ("Tabla2_Cod1Cod2"))
)

, en donde "Tabla2_Cod1Cod2" es el nombre del índice sobre dichos campos.

Entonces el sistema retorna el siguiente mensaje:

index Tabla2_Cod1Cod2 cannot be used in the specified plan.

Entonces opto por cambiar la consulta de tal manera que no deba usar la concatenación (esto lo descubro después de hacer varias pruebas), de la siguiente manera:

Código SQL [-]
SELECT * FROM "Tabla1" T1 WHERE EXISTS (
SELECT * FROM "Tabla2" T2 WHERE (T1."Campo1" = T2."Cod1" AND T1."Campo2" = T2."Cod2") AND ...
)

y de esta manera, sin necesidad de especificar el plan, el sistema por sí sólo adopta el plan adecuado:

PLAN (T2 INDEX (Tabla2_Cod1Cod2))

En mi consulta, que es en realidad un poco más "extensa" el tiempo de la consulta disminuyó de varios minutos a algunos segundos.

Aún así, el tiempo no me parece "adecuado". Debo seguir trabajando... mmm

Curioso

La primera consulta en Paradox sólo tomaba un par de segundos. La segunda consulta, usando EXISTS, llevó el uso del procesador al 100% y demoró varios minutos. Contrario a lo que sucede en Firebird. Aunque en Firebird la primera consulta demoraba en realidad demasiados minutos.

[RolphyReyes]

Saludos.

No se si se aplica para tu caso, pero Firebird > 2 puedes utilizar expresiones en los indices y así agilizar el resultado de tu consulta.

Hasta luego.

[[mlara]]

Expresiones en índices?

[roman]

Pienso que las subconsultas siempre serán lentas en cualquier motor, aunque unos puedan hacerlas un poco más eficientes que otros.

Sin estar del todo seguro, yo creo que tu consulta la puedes convertir en un join con mucho mejores resultados:

Código SQL [-]
select distinct tabla1.* from tabla1
left join tabla2 on tabla2.cod1 = tabla1.campo1 and tabla2.cod2 = tabla1.campo2
where tabla2.cod1 is not null and tabla2.cod2 is not null

// Saludos

[[mlara]]

Buena la sugerencia. A pesar de haber trabajado varias veces con join (de hecho uso mucho left outer join), no se me ocurrió en primera instancia. Lo probaré con más tiempo para ver qué sucede. Por lo pronto, la actualización se fue usando exists.

[celades1]

Creo que no hace falta hacer left join sino join (con lo que ira mucho mas rapido si hay indices bien creados (PK i FK), el left join siempre es mas lento
no hace falta el where ya que el join ya obliga a que no sean nulos


Código SQL [-]
select distinct tabla1.* from tabla1
join tabla2 on tabla2.cod1 = tabla1.campo1
and tabla2.cod2 = tabla1.campo2







Saludos

[roman]

Tienes razón. Yo estaba pensando en el caso contrario del NOT IN en donde se hace necesario el join externo precisamente para obtener los valores null. Tal como lo pones es mucho mejor.

// Saludos