Mejorar Consulta

[YAcosta]

Saludos

Me han asignado el mantenimiento de una BD que yo no desarrollé y entre algunas tareas tenia que mejorar una consulta sql que en hispano pide: Tráeme todos los registros de la tabla1 pero que no estén en la tabla2, la consulta entonces es esta que tiene como fin llenar números en un DataCombo:

Código: (VB) [Seleccionar]

SELECT T1.CAMPO1
From TABLA1 T1
WHERE T1.CAMPO1 not in
(Select T2.CAMPO1 from TABLA2 T2 where T2.CAMPO2 = 'A' and T2.CAMPO3 = '2014/09/12')
La tabla Tabla1 tiene 100 registros que van simplemente del 1 al 100 y son unas Casillas que alquilan.
La tabla Tabla2 tiene 450,000 registros y el campo1 existe en ambas, el campo2 indica el estado donde A lo ponen como Abierto y C como cerrado, cuando un cliente toma una casilla meten un registro en la Tabla2 y de alli excluyen esa casilla del datacombo para que no lo ofrezcan a otro cliente en ese dia, cuando el cliente paga y se va cambian el estado a C. Pero la consulta tarda 12 segundos y me parece que esta lentitud pasa porque por cada registro en Tabla1 se hace la consulta en Tabla2.

De momento los tengo contentos porque lo que hice fue crear una nueva tabla donde inserto la casilla usada cuando registran y luego la quito cuando liberan la casilla, entonces la consulta la cambie a:

Código: (VB) [Seleccionar]

SELECT T1.CAMPO1
From TABLA1 T1
WHERE T1CAMPO1 not in
(Select CAMPO1 from TABLANUEVA)
Y todo la consulta tarda menos de 1 segundo, ellos felices pero yo no y obviamente va rápido porque la tabla es mucho mas pequeña y como máximo sera 100 contra 100 y no 100 contra 450,000. Sin embargo no estoy seguro de que sea la mejor solución, no me gusto eso de crear una tabla auxiliar y quería ver como poder resolverlo con un solo query ¿alguien tiene alguna idea de si esto se puede lograr o mejorar?

Gracias


EDITO: Aclaro que los indices están creados.

[Albertomi]

Estimado YAcosta

De lo que entiendo del caso, me parece es más un tema de como han diseñado la estructara de la base de datos, ya que la tabla T2 me parece que es una tabla histórica. La consulta de cual o tal casilla esta Abierta o cerrada debe realizarse contra una tabla trancional que obviamente va tener mucho menos registros (uno por cada Casilla que se alquila)

Con respecto a los indices, los que necesitas tener son los indices CAMPO1 en T1 y CAMPO2, CAMPO3 en T2. No estaría mal una re indexada.
Tambian habría que revisar si el query se ejecuta como tal (como query) desde una aplicacion cliente o esta dentro de un Store Procedure (SP) que seria lo recomendable, claro esta hay quienes tratan de evitar hacer uso de SP aduciendo temas de portabilidad; pero ese es otro cuento.
 
En el servidor revisa el plan de ejecución del query para ver en donde hay mayor costo.
 
Tampoco esta demás revisar lo relacionado a I/O a disco; si es una DB que se ejuta en un servidor virtualizado o fisico. Esta en un servidor dedicado, con que otros servicios se compite.
 
Saludos, desde algún lugar de Lima-Perú

[YAcosta]

Hola amigo, te comento:
La tabla T2 la usan para meter alli los servicios que contratan por dia (te lo digo, es un sauna), esto implica usar un casillero (separar un numero) y luego al cambiar de estado de A a C reusan ese numero. Vale decir que el numero o casilla se usa una sola vez por vez, pero puede usarse varias veces en el dia pq en el dia varios clientes pueden tomar en diferentes momentos esa casilla. Entonces en la tabla T2 estan metiendo todas esas transacciones.
Por ello cree la tabla nueva a manera de tabla transaccional como comentas y esto fue la solución de momento.

Efectivamente en esos campos están los indices, es mas, recompute la selectividad y fue lo mismo.

El query esta en VB6 y la BD es Firebird (por eso conseguí este cliente)

Esto me arroja el plan y el costo lo tiene la tabla que mencione T2

Aquí estoy probando con 100 registros en T1 y 45,000 en T2 y la demora es de 3 segundos y fíjate que hace muchas vueltas (5 millones de leídas) porque esta prueba la estoy haciendo con una BD de meses atras que lo único que ha cambiado en estos tiempos son la cantidad de registros.

Y lo esta tomando Natural, ya he corrido otros querys diferentes a la mismas tablas y campos y si usan los indices, quizá algo se me esta pasando aquí.


La BD se ejecuta en una PC normal con Windows 7 de 64 y con 8GB de RAM y si esta prácticamente dedicada esta PC para el uso de este sistema. Y las pruebas que yo hago las hago en una maquina virtual pero mi PC tiene 16GB de ram W7-64 y en esta maquina virtual ya he corrido consultas mas grandes sin esa lentitud.

Entonces ¿consideras que esta bien mejor usar esta tabla intermedia de manera formal o crees que se pueda mejorar el query? ya probé algo similar a esto:

Código: (VB) [Seleccionar]

SELECT T1.CAMPO1
FROM TABLA1 T1
LEFT JOIN TABLA2 T2 ON T1.CAMPO1 = T2.CAMPO1 AND
         T2.CAMPO1 IS NULL
WHERE
   T2.CAMPO2 = 'A'
   AND T2.CAMPO3 = '2014/09/12'

Pero me da el mismo ratio de lentitud.

[Albertomi]

Estimado YAcosta

Y si pruebas reemplazar NOT IN por NOT EXISTS
 

Código: [Seleccionar]

SELECT T1.CAMPO1
FROM TABLA1 T1
WHERE T1.CAMPO1 NOT EXISTS
(SELECT T2.CAMPO1 FROM TABLA2
T2 WHERE T2.CAMPO2 = 'A' and T2.CAMPO3 = '2014/09/12')

 
Saludos, desde algún lugar de Lima-Perú

[YAcosta]

Maestrazo!!! esa era, habia olvidado por completo ese predicado. La consulta quedo asi:

Código: (VB) [Seleccionar]

SELECT T1.CAMPO1 AS CASILLA
From TABLA1 T1
WHERE NOT EXISTS
(Select T2.CAMPO1 as CASILLA from TABLA2 T2 where
T1.CAMPO1 = T2.CAMPO1
and T2.CAMPO2 = 'A'
and T2.CAMPO3 = '2014/09/12')
order BY CASILLA
Y ya salio relámpago, apenas un pestañeo y los indices se usan tal cual:


Los registros que obtengo con la consulta poco eficiente son exactamente los mismos pero esta vez mucho mas rápido, mañana lo pruebo con la BD grande pero desde ya estoy seguro que ya estare quitando esa tabla que en todo caso seria para otros fines y no para "acelerar" la consulta.

Gracias estimado... le debo un par de chelas cuando quieras.

Saludos

[Albertomi]

Estimado YAcosta

Que gusto saber que tus resultados han mejorado, para el nuevo query que probaste por curiosidad prueba reemplazar LEFT JOIN por LEFT OUTER JOIN
 

Código: [Seleccionar]

SELECT T1.CAMPO1
FROM TABLA1 T1
LEFT OUTER JOIN TABLA2 T2 ON T1.CAMPO1 = T2.CAMPO1 AND
T2.CAMPO1 IS NULL
WHERE
T2.CAMPO2 = 'A'
AND T2.CAMPO3 = '2014/09/12'

 
 
Saludos, desde algún lugar de Lima-Perú

[YAcosta]

Me disculpo porque esa consulta (La de LEFT JOIN) ya me estaba arrojando 0 registros, pasa que en el ibExpert tengo varias versiones del mismo query y probando una y otra y copiando y probando en el planAnalyzer termine pegando aqui la que me arroja 0 registros, entonces pasa lo mismo poniéndole el OUTER.
Debería funcionar siendo que el Is Null excluye a las filas de Tabla2 que cumpla t1.campo1 = t2.campo1 pero me arroja null (0 registros). Como ejercicio le sigo dando vueltas a ese query pero desde ya el Not Exists da una mejora abismal al query.

Gracias

[Albertomi]

Estimado YAcosta

Tomándote la palabra del par de chelas, seria interesante emular la reunión de nuestros amigos del team Argentina y propiciar una reunión con los integrantes del team Perú.
 
Saludos, desde algún lugar de Lima-Perú

[YAcosta]

Me parece buena idea, ¿podria ser pa mi diablo (27)?? que mejor regalo y encima cae sabado!!!.

Les escribire.