Acelerar query

[Angel.Matilla]

Buenos días. Necesito que me ayudéis con un problema. Tengo este query que se ejecuta en FB 2.5:

Código SQL [-]
SELECT A.CodPrv, A.Codigo, A.Apellidos, A.Nombre, A.Referencia, A.Situacion, A.F_alta, B.Cuota
  FROM Persona A, Cuotas B
 WHERE A.CodPrv = :PrvIns
   AND A.Codigo NOT IN (SELECT DISTINCT Codigo FROM Recibos WHERE CodPrv = A.CodPrv AND RefInt LIKE '1%' OR RefInt LIKE '2%') 
   AND A.CodPrv = B.CodPrv AND A.Codigo = B.Codigo AND B.Tipo = 'A' AND B.Cuota > 0

Las tablas Persona y Cuotas tienen como clave primaria CodPrv y Codigo; además Cuotas tiene una clave externa sobre esos mismos campos de Persona. La tabla Recibos tiene como clave primaria CodPrv, Codigo, Fecha y RefInt y tiene un índice sobre CodPrv y Codigo. La tabla Persona tiene ahora 15130 registros, Cuotas 4282 y Recibos 35471; tampoco son unas cifras excesivas.
El problema que tengo es este:

Como veis todas las lecturas que se hacen sobre la tabla Recibos no se hacen siguiendo el índice a pesar de que el campo que se compara está tanto en la clave primaria como en un índice y por lo tanto la ejecución se demora muchísimo, casi 4 minutos. Es más: si ejecuto el SELECT DISTICNT etc. sólo todas las lecturas que se hacen (70942) se hacen sin seguir ningún índice.

Mi pregunta es: ¿hay alguna forma de forzar que la lectura de la tabla Recibos se haga siguiendo un índice?

[Casimiro Notevi]

Puedes indicarle qué "plan" debe usar, mira esto.
También puedes cambiar el orden de las condiciones y demás para probar.
Aparte de eso supongo que (a simple vista) por cada registro debe hacer el "select distinct en recibos" por lo que eso lo ralentiza.
Habría que hacer diversas pruebas, por ejemplo, cambia el "not int" por "not exists".

[chenech]

La conexión entre las tablas Persona y Cuota cambialas por un JOIN, ahi siempre he obtenido yo mejores resultados.
En lugar de LIKE usa STARTING, tengo entendido por la documentación de Firebird que está mas optimizado y por lo que veo es lo que empieza por 1 o 2.
Usa alias tambien para la tabla Recibos.
Espero ayude algo esas cosas.
Un saludo.

[Casimiro Notevi]

Empezado por chenech

... En lugar de LIKE usa STARTING...

Siempre que se pueda es mucho mejor starting

[Neftali [Germán.Estévez]]

Además del acceso por índice, yo intentaría evitar la clausula IN, porque suelen ser bantante lentas.
La mayoría de veces, se puede hacer lo mismo utilizando una JOIN. Revísalo.

[Angel.Matilla]

Gracias por las respuestas. En cuanto pueda las probaré. No obstante me he dado cuenta de que hay un error de sintaxis en el query: la línea

Código SQL [-]
AND RefInt LIKE '1%' OR RefInt LIKE '2%'

está mal escrita y debería ser:

Código SQL [-]
AND (RefInt LIKE '1%' OR RefInt LIKE '2%')

me faltaban los paréntesis.

Empezado por Casimiro Notevi

Puedes indicarle qué "plan" debe usar, mira esto.
También puedes cambiar el orden de las condiciones y demás para probar.
Aparte de eso supongo que (a simple vista) por cada registro debe hacer el "select distinct en recibos" por lo que eso lo ralentiza.
Habría que hacer diversas pruebas, por ejemplo, cambia el "not int" por "not exists".

Esa estructura de un SELECT no la conocía, pero ¿cómo sé que plan aplicar? Si yo ejecuto ese query tal como está me da esta información de ejecución:

Plan:
------------------------------------------------
PLAN SORT ((RECIBOS NATURAL))
PLAN SORT ((RECIBOS NATURAL))
PLAN JOIN (B INDEX (FK_CODPERCUO), A INDEX (PK_PERSONA))

¿Cómo hago para forzar que use el índide que me interesa?

Empezado por Neftali [Germán.Estévez]

Además del acceso por índice, yo intentaría evitar la clausula IN, porque suelen ser bantante lentas.
La mayoría de veces, se puede hacer lo mismo utilizando una JOIN. Revísalo.

Sí, lo sé, pero en este caso lo que necesito es que me encuentre los códigos que no estén ya en la tabla Recibos y no se me ocurre como reemplazar el IN por JOIN

Empezado por chenech

En lugar de LIKE usa STARTING, tengo entendido por la documentación de Firebird que está mas optimizado y por lo que veo es lo que empieza por 1 o 2.

¡Increíble! Ha pasado de hacer ciento y pico millones de lecturas no indexadas a poco más de 30 mil indexadas. Muchas gracias a todos por la ayuda.

[mamcx]

Empezado por Angel.Matilla

Sí, lo sé, pero en este caso lo que necesito es que me encuentre los códigos que no estén ya en la tabla Recibos y no se me ocurre como reemplazar el IN por JOIN

Eso puede variar por motor y que tan bien o mal es su query planner, aquí algunas opciones:

https://stackoverflow.com/questions/...in-on-firebird
https://stackoverflow.com/questions/...ery-using-join

[Casimiro Notevi]

Empezado por Angel.Matilla

¡Increíble! Ha pasado de hacer ciento y pico millones de lecturas no indexadas a poco más de 30 mil indexadas. Muchas gracias a todos por la ayuda.

Claro, es que like hace una comparación de textos por cada uno de los registros, sin embargo starting utiliza el índice del campo.

[mamcx]

PD. Me dio curiosidad si FB soporta indices sobre expresiones y si!:

https://firebirdsql.org/rlsnotesh/in...xpression.html

Es muy util para acelerar consultas de este tipo...

[Angel.Matilla]

Empezado por mamcx

PD. Me dio curiosidad si FB soporta indices sobre expresiones y si!:

https://firebirdsql.org/rlsnotesh/in...xpression.html

Es muy util para acelerar consultas de este tipo...

Muy interesante.

[marco3k]

Por lo que se ve en la imagen de tu post, el problema es en la tabla recibos.

Código SQL [-]
SELECT DISTINCT Codigo FROM Recibos WHERE CodPrv = A.CodPrv AND RefInt LIKE '1%' OR RefInt LIKE '2%'

He de suponer que el campo CodPrv y RefInt tiene su indice respectivo.

Con repecto a :

¿Cómo hago para forzar que use el índide que me interesa?

Todo lo que este en la clausula where debe tener un indice para que las consultas sean rápidas.

[[duilioisola]]

Prueba con la sentencia EXISTS().

Cuando haces un SQL con "Campo IN (select ...)" el motor de base de datos tiene que obtener los datos del select y luego hacer la comparación.
Con "EXISTS (select ...)" solo recorre los datos y se para cuando existe el primero. No tiene que guardar los datos que recupera del select en memoria.

Código SQL [-]
select a.codprv, a.codigo, a.apellidos, a.nombre, a.referencia, a.situacion, a.f_alta, b.cuota
from persona a, cuotas b
where
a.codprv = rvins and
/*
a.codigo not in (select distinct codigo
                 from recibos
                 where
                 codprv = a.codprv and
                 refint like '1%' or refint like '2%') and
*/
not exists(select codigo
           from recibos
           where
           codigo = a.codigo and
           codprv = a.codprv and
           (refint starting with '1' or refint starting with '2')) and
a.codprv = b.codprv and
a.codigo = b.codigo and
b.tipo = 'A' and
b.cuota > 0

La tabla Recibos tiene como clave primaria CodPrv, Codigo, Fecha y RefInt

Además, puedes probar a cambiar el orden de la clave primaria para que codigo, codprv y refint sean los primeros campos y fecha el último.
Quizás ayude, pero puede ser que haga que otras consultas vayan mas lentas.

NOTA:
La parte "(refint starting with '1' or refint starting with '2')" ponla entre paréntesis para "desambiguar" la sentencia.

[Angel.Matilla]

Gracias. Es una sentencia de la que nunca me acuerdo.

[[duilioisola]]

También puedes probar con JOINs como indican más arriba.
Dependiendo de los datos, puedes tener mejores resultados con un "JOIN" o con un "LEFT JOIN".

Código SQL [-]
select a.codprv, a.codigo, a.apellidos, a.nombre, a.referencia, a.situacion, a.f_alta, b.cuota
from persona a
/*LEFT*/ JOIN cuotas b on a.codprv = b.codprv and a.codigo = b.codigo
where
a.codprv = rvins and
/*
a.codigo not in (select distinct codigo
                 from recibos
                 where
                 codprv = a.codprv and
                 refint like '1%' or refint like '2%') and
*/
not exists(select codigo
           from recibos
           where
           codigo = a.codigo and
           codprv = a.codprv and
           (refint starting with '1' or refint starting with '2')) and
b.tipo = 'A' and
b.cuota > 0

[Angel.Matilla]

Gracias por la sugerencia, pero en este caso no me vale porque con el JOIN podrían salir registros con cuota cero y esos no me interesan. Ya lo había probado.

[marco3k]

Una consulta tu campo CodPrv es numerico? si fuera afirmativo intenta este truquillo que se usar alguna veces "CodPrv >0", tu campo RefInt esta indexado?

..La tabla Recibos tiene como clave primaria CodPrv, Codigo, Fecha y RefInt y tiene un índice sobre CodPrv y Codigo

Por lo que entiendo al parecer esta indexado pero varios campos para clave primaria o no, de preferencia "RefInt" debe tener un indice individual.
Al final seria asi esa parte del query:

SELECT DISTINCT Codigo FROM Recibos WHERE CodPrv > 0 and CodPrv = A.CodPrv AND RefInt LIKE '1%' OR RefInt LIKE '2%'

[Angel.Matilla]

Muchas gracias por los consejos.

Empezado por marco3k

Una consulta tu campo CodPrv es numerico? si fuera afirmativo intenta este truquillo que se usar alguna veces "CodPrv >0", tu campo RefInt esta indexado?

No, es un campo alfanumérico. Son los dos primeros dígitos del código postal. En principio, al diseñar la BB.DD., lo había puesto numérico pero lo tuve que cambiar porque con esa definición me daba problemas en algunas inserciones.

Empezado por marco3k

Por lo que entiendo al parecer esta indexado pero varios campos para clave primaria o no, de preferencia "RefInt" debe tener un indice individual.

La clave primaria es CodPrv (Código de provincia), Codigo (Codigo de persona), Fecha (Fecha de emisión del recibo) y RefInt (Refª interna del recibo); además hay un índice sólo con CodPrv y RefInt.

Empezado por marco3k

Al final seria asi esa parte del query:

Como comenté en una de mis respuestas, el query puesto así:

Código SQL [-]
AND RefInt LIKE '1%' OR RefInt LIKE '2%'

está mal; lo correcto es esto otro:

Código SQL [-]
AND (RefInt LIKE '1%' OR RefInt LIKE '2%')

En el primer mensaje le faltan los paréntesis y es una de las razones por las que se frenaba la ejecución. También, siguiendo un muy buen consejo, reemplacé el LIKE por STARTING; es una instrucción de la que casi nunca me acuerdo.