Optimización de consulta SQL

ifigini · 17 Junio, 2014 16:19

Estoy en guarani 2.7.0.
Necesito hacer la siguiente consulta (para obtener sólo los datos actualizados de los alumnos):

SELECT * FROM "dba".sga_datos_censales D
WHERE D.fecha_actualiz = (SELECT MAX(D2.fecha_actualiz) FROM "dba".sga_datos_censales D2
				WHERE D2.nro_inscripcion = D.nro_inscripcion);

Pero el problema es que tarda unos 20-25 minutos en responder… Y necesito algo más “instantáneo”.
Alguna manera de optimizarla? O ya hay algo hecho?
Desde ya, muchas gracias!!

adelu · 17 Junio, 2014 17:11

A la consulta le falta el join por el campo unidad academica, ya que como esta no entra por indice a la tabla.
La consulta debería ser:

SELECT * FROM "dba".sga_datos_censales D
   WHERE D.fecha_actualiz = (SELECT MAX(D2.fecha_actualiz) 
                                                FROM "dba".sga_datos_censales D2
                                              WHERE D2.unidad_academica = D.unidad_academica
                                                   AND D2.nro_inscripcion = D.nro_inscripcion);

Que tengas todos los indices y foreign keys creados
Tener las estadisticas de la base actualizadas

dturriaga · 17 Junio, 2014 23:39

Estas son la PK y la FK que deberías tener ya creadas:

ALTER TABLE "dba".sga_datos_censales ADD CONSTRAINT
   PRIMARY KEY (unidad_academica, nro_inscripcion, fecha_relevamiento) CONSTRAINT "dba".pk_datos_censales;

ALTER TABLE "dba".sga_datos_censales ADD CONSTRAINT
   FOREIGN KEY (unidad_academica, nro_inscripcion) REFERENCES "dba".sga_personas CONSTRAINT "dba".fk_dat_cen_persona;

Esta es la instrucción para actualizar las estadisticas:

UPDATE STATISTICS HIGH FOR TABLE sga_datos_censales;

La consulta que antes te dejó Alejandro en mi caso tardó 16,359 segundos en traerme 44169 tuplas y bajó a 11,844 segundos después de actualizar las estadísticas en el servidor de desarrollo.

adelu · 18 Junio, 2014 02:18

Volviendo a leer tu consulta, vos queres recuperar solo los alumnos que actualizaron sus datos censales, no aquellos que nunca lo modificaron desde que se los dio de alta en el sistema?

Porque ni la consulta tuya ni la que yo te pase creo trae lo que necesitas.

Espero por tu respuesta para volver a responder con la query correcta.

ifigini · 18 Junio, 2014 12:34

Alejandro, la consulta es correcta, quiero consultar los datos de todas las personas, pero sólo las filas más actuales, es decir los datos válidos. No me refiero solo los alumnos que actualizaron sus datos censales.
La PK y la FK están definidas cómo me indican y UPDATE STATISTICS HIGH ya había probado también, sin obtener grandes mejoras.
Muchísimas gracias por ambas respuestas. Es increíble la optimización que hay al incluir el join por el campo unidad académica!!! De 20-25 minutos bajó a unos 6 segundos!!! No lo había incluido ya que trabajamos con única unidad académica. Eso tiene que ver por el FK cómo está definido, que incluye ambos campos? Así lo tengo en cuenta de ahora en más!!
Nuevamente, gracias!!!

ibisso · 18 Junio, 2014 13:24

Hola,

Veo que ya el tema esta resuelto, Solo para aclarar un poco la causa de que ande mas rapido

si tenes una tabla T1 con columnas A, B y C y tenes un indice cuya clave es A+B. ( como en nuestro caso unidad_academica+nro_inscripcion)

y haces un SELECT … FROM T1 WHERE B = ‘xx’, informix no va a usar el indice,

Por el contrario si haces

SELECT … FROM T1 WHERE A = ‘zz’ AND B = ‘xx’, informix SI va a usar el indice,

Y si haces

SELECT … FROM T1 WHERE A = ‘zz’ informix tambien va a USAR el indice,

saludos
Ignacio

ifigini · 18 Junio, 2014 14:06

Ignacio:
Muchísimas gracias por la aclaración! La voy a tener muy en cuenta de ahora en más, es muuuy notorio el cambio en el tiempo de respuesta de la consulta.
Saludos.

adelu · 19 Junio, 2014 13:57

Entonces la consulta debe ser usando el maximo valor de fecha_relevamiento para cada persona:

      
-- Recupera el ultimo dato censal de cada persona.
SELECT *
   FROM sga_datos_censales as a
  WHERE fecha_relevamiento =  (SELECT MAX(fecha_relevamiento)
                                                            FROM sga_datos_censales as b
                                                        WHERE a.unidad_academica = b.unidad_academica
                                                             AND a.nro_inscripcion = b.nro_inscripcion)

ifigini · 27 Junio, 2014 15:59

Alejandro:
Cuál es la diferencia entre fecha_relevamiento y fecha_actualiz?

Ignacio:
No me queda del todo claro la diferencia (para optimizar) entre:
si tenes una tabla T1 con columnas A, B y C y tenes un indice cuya clave es A+B.
y haces un SELECT … FROM T1 WHERE B = ‘xx’, informix no va a usar el indice,
Y si haces SELECT … FROM T1 WHERE A = ‘zz’ informix tambien va a USAR el indice,
Gracias!!

dturriaga · 27 Junio, 2014 20:12

Tabla A+B+C+D+…+Z
Indice A+B+C

Si filtras por A usa el índice
Si filtras por A+B usa el índice
Si filtras por A+B+C usa el índice

Si filtras por B no usa indice
Si filtras por B+C no usa indice
Si filtras por C no usa indice

Si filtras por A+C usa el indice para llegar hasta A pero no para llegar hasta C

Para reflejar lo de antes con un ejemplo supón una tabla así:
tabla_ejemplo(PROVINCIA, PARTIDO, LOCALIDAD, CALLE)
donde los primeros 3 campos son clave primaria

SELECT * FROM tabla_ejemplo WHERE provincia=IDPROV
Si buscas por provincia usarás el índice y te traerá todos los partidos, localidades y calles de la/s provincia/s buscada/s.

SELECT * FROM tabla_ejemplo WHERE provincia=IDPROV AND partido=IDPART
Si buscas por provincia+partido usarás el índice y te traerá todas las localidades y calles del/os partido/s de la/s provincia/s buscada/s.

SELECT * FROM tabla_ejemplo WHERE provincia=IDPROV AND partido=IDPART AND localidad=IDLOC
Si buscas por provincia+partido+localidad usarás el índice y te traerá todas las calles de la/s localidad/es buscada/s.

SELECT * FROM tabla_ejemplo WHERE partido=IDPART
No usa el indice… IDPART puede repetirse en todas las provincias

SELECT * FROM tabla_ejemplo WHERE partido=IDPART AND localidad=IDLOC
No usa el indice… IDPART+IDLOC puede repetirse en todas las provincias

SELECT * FROM tabla_ejemplo WHERE localidad=IDLOC
No usa el indice… IDLOC puede repetirse en todos los partidos de todas las provincias

SELECT * FROM tabla_ejemplo WHERE provincia=IDPROV AND localidad=IDLOC
Usa el indice para llegar hasta IDPROV… pero no usa indice para encontrar IDLOC porque puede repetirse en todos los partidos.

dturriaga · 27 Junio, 2014 21:18

Aclaración, decir que es clave primaria implica que hay un indice múltiple conformado por los campos de la clave.

Regresando al ejemplo si el indice de la PK es A+B+C pero por algún motivo sabés que harás consultas buscando solo por C deberías crear un índice en dicho campo.

En el caso de la base de datos del Guaraní en la que muchos de los campos clave son de tipo texto puede ser conveniente crear un índice extra sobre el campo en que estás haciendo la búsqueda o la junta. Si el indice tiene menos campos tardas menos en leerlo y hacés más rápido las búsquedas pero eso te hace también más lentos los inserts y deletes.

adelu · 28 Junio, 2014 13:01

Tabla A+B+C+D+...+Z Indice A+B+C
Si filtras por A usa el índice
Si filtras por A+B usa el índice
Si filtras por A+B+C usa el índice

Si filtras por B no usa indice
Si filtras por B+C no usa indice
Si filtras por C no usa indice

Si es correcto lo que indicas aca, asi es como trabaja informix con los indices. Si solo quisieras buscar por el campo C, tendrias que entrar a la tabla por A+B+C y sino crear un índice solo pr el campo C o C+D+…

Datos Censales: el campo fecha_relevamiento es parte de la PK. Como podes tener varios registros en datos censales, debes buscar el último y el último es el que tenga fecha de relevamiento mayor. Luego cada vez que se actualiza ese registro, lo que se hace es ir modificando el campo que representa la fecha de actualización de los datos (fecha_actualiz)

ifigini · 30 Junio, 2014 12:27

Muchas gracias a ambos!!
Ahora si me terminó de quedar claro!
Saludos.