Encapsular consultas

[darthsmog]

Un saludo,

Estoy trabajando con un programa en java que extrae los datos de una base de datos en PostgreSQL 8.1 y me encuentro con el siguiente problema:

- Dentro de una transacción necesito crear dos tablas temporales con la propiedad ON COMMIT DROP, añadir campos mediante dos consultas a estas tablas, y luego ejecutar una consulta que extrae datos de estas dos tablas y de la base de datos.

- Necesito crear las tablas temporales para evitar que las consultas se repitan con cada iteración y se eternice la query.

Y aquí viene el gran problema:

Utilizamos una capa de borland (JBuilder) para Java que requiere que, para extraer los datos al programa, utilicemos una única consulta.

¿Qué solución podría adoptar?

Había pensado en encapsular de alguna manera las 5 queries para engañar a la capa de borland.

Mi código SQL es, en esencia:

Creo dos tablas auxiliares con los identificadores de los objetos que cumplen ciertas propiedades que luego quiero mostrar en una tabla al usuario.

CREATE TEMPORARY TABLE aux_muestras_pendientes_facturar (muestraid INT) ON COMMIT DROP;
CREATE TEMPORARY TABLE aux_muestras_con_ensayos_sin_actas (muestraid INT) ON COMMIT DROP;

INSERT INTO aux_muestras_pendientes_facturar (muestraid)
SELECT DISTINCT muestraid FROM ensayo_muestra
WHERE (estadoalbaran = 0 OR estadoalbaran IS NULL) AND inactivo IS NULL;

INSERT INTO aux_muestras_con_ensayos_sin_actas SELECT muestraid FROM (
SELECT DISTINCT ensayo_muestra.muestraid FROM ensayo_muestra WHERE ensayo_muestra.inactivo IS NULL
EXCEPT
SELECT DISTINCT ensayo_muestra.muestraid FROM ensayo_muestra
JOIN actas_archivo
ON actas_archivo.ensayo_muestraid = ensayo_muestra.ensayo_muestraid
WHERE (acta_clase = 1 OR acta_clase IS NULL))alias;

SELECT
pendientes.muestraid AS sin_facturar,
sin_actas.muestraid AS ensayos_pendientes,
muestra.muestraid,
muestra.albaran,
FROM muestra
JOIN ensayos ON muestra.tipomuestraid = ensayos.ensayoid
LEFT OUTER JOIN aux_muestras_pendientes_facturar pendientes ON pendientes.muestraid = muestra.muestraid
LEFT OUTER JOIN aux_muestras_con_ensayos_sin_actas sin_actas ON sin_actas.muestraid = muestra.muestraid
WHERE muestra.inactivo IS NULL
AND ((pendientes.muestraid IS NOT NULL) OR (0 = variable1))
AND ((sin_actas.muestraid IS NOT NULL) OR 0 = variable2)


Un saludo y muchas gracias

[seyko]

La idea es realizarlo todo en una misma transación:

Puedes crearte una función con las 5 querys.

No obstante, a primera vista, creo que no es necesario que crees tablas temporales para esto, con una select puedes hacerlo todo de una vez, sin insert ni tablas temporales.

Salu2

[seyko]

Código:

SELECT
		pendientes.muestraid AS sin_facturar,
		sin_actas.muestraid AS ensayos_pendientes,
		muestra.muestraid,
		muestra.albaran
FROM	   muestra
		JOIN ensayos 		ON muestra.tipomuestraid = ensayos.ensayoid
		LEFT OUTER JOIN (SELECT DISTINCT muestraid 
						FROM 	ensayo_muestra
						WHERE 	(estadoalbaran = 0 OR estadoalbaran IS NULL) AND inactivo IS NULL
			) pendientes 	ON pendientes.muestraid = muestra.muestraid
		LEFT OUTER JOIN (SELECT DISTINCT ensayo_muestra.muestraid 
						FROM 	ensayo_muestra WHERE ensayo_muestra.inactivo IS NULL
						EXCEPT
						SELECT DISTINCT ensayo_muestra.muestraid 
						FROM 	ensayo_muestra
								JOIN actas_archivo  ON actas_archivo.ensayo_muestraid = ensayo_muestra.ensayo_muestraid
						WHERE 	(acta_clase = 1 OR acta_clase IS NULL)
			) sin_actas ON sin_actas.muestraid = muestra.muestraid
WHERE	muestra.inactivo IS NULL
		AND ((pendientes.muestraid IS NOT NULL) OR (0 = variable1))
		AND ((sin_actas.muestraid IS NOT NULL) OR 0 = variable2)

Si no entiendes algo.... te buscas la vida
jaja es broma, pregunta!

Salu2

[darthsmog]

El problema que me encuentro si creo un único SELECT en lugar de crear tablas temporales, es que ejecuta cada subconsulta por cada fila encontrada, convirtiendo una consulta que me puede tardar de 1 a 10 segundos en una que tarda 25 minutos (comprobado), ya que trabajo sobre algunas tablas de 65.000 y otras de más de 200.000 filas, así que cada milisegundo cuenta.

¿Podría crear una funcion que contenga las 5 queries y que me devolviera como resultado una tabla con el resultado de la consulta? Esque de funciones estoy un poco pez :S

Un saludo y muchas gracias por las respuestas ;)

[seyko]

El problema que me encuentro si creo un único SELECT en lugar de crear tablas temporales, es que ejecuta cada subconsulta por cada fila encontrada,

Incorrecto, esto ocurriría en el caso de que la subselect estuviera dentro del select, al estar en el from, lo que hace la BD internamente es crear una tabla temporal con la select.

En cuestión de rendimiento, lanzar las 3 select (dejando de lado los insert) es más costoso que lanzar la select que yo te he puesto.
Si la velocidad es crucial podemos mirar como mejorar el plan de ejecucuión, pero te aseguro que la select que te he puesto es más rapida que las 3 que tu tenias.

Salu2

[darthsmog]

Tenías razón Seiko, lo he probado y tarda aproximadamente lo mismo. Mi problema era que metía dentro del subselect una comparación con una columna de otra tabla, y debía estar volviéndole loco. Lo curioso es que nosotros trabajamos también con SQL-Server y, esta misma consulta que me tardaba 25 minutos el PostgreSQL, tardaba menos de 10 segundos en SQL Server :S
De cualquier modo, tema resuelto. Muchas gracias.

CORRIJO:

No se ha soluccionado el problema. La query se vuelve interminable cuando, en la cláusula WHERE global, intento filtrar por los campos de las subconsultas (quiero tener la posibilidad de filtrar por "IS NOT NULL" o mostrar todos).

Al pedir que aplique el filtro AND ((pendientes.muestraid IS NOT NULL) OR (0 = variable1)), la query se eterniza de nuevo. Da la sensación de que realmente vuelve a ejecutar el subselect...

¿Alguna idea de a qué se puede deber? Un saludo!

PD. En SQL Server la misma consulta va a la velocidad del rayo, ¿problema del gestor de PostgreSQL?

[seyko]

Es raro pero he tenido problemas con las variables en el where al lanzar informes desde JasperReports.
¿qué valor lleva la variable? ¿puede ser nula?
¿Desde donde llamas a la select?

A la espera

[darthsmog]

Las variables llevan valor 1 ó 0 en función de un checkbox. El SELECT lo ejecuta un DataSet de Java. Pero probándolo desde el gestor PgAdmin, también me encuentro con el mismo problema.
Lo estoy aislando todo lo posible, y aquí es donde me bloqueo:

Las subconsultas añadidas a una consulta genérica independientes una de otra...

SELECT * FROM muestra
LEFT OUTER JOIN (SELECT DISTINCT muestraid
FROM ensayo_muestra
WHERE (estadoalbaran = 0 OR estadoalbaran IS NULL) AND inactivo IS NULL
) pendientes ON pendientes.muestraid = muestra.muestraid
WHERE pendientes.muestraid IS NOT NULL

SELECT * FROM muestra
LEFT OUTER JOIN (SELECT DISTINCT ensayo_muestra.muestraid
FROM ensayo_muestra WHERE ensayo_muestra.inactivo IS NULL
EXCEPT
SELECT DISTINCT ensayo_muestra.muestraid
FROM ensayo_muestra
JOIN actas_archivo ON actas_archivo.ensayo_muestraid = ensayo_muestra.ensayo_muestraid
WHERE (acta_clase = 1 OR acta_clase IS NULL)
)sin_actas ON sin_actas.muestraid = muestra.muestraid
WHERE sin_actas.muestraid IS NOT NULL

... funcionan perfectamente (he quitado las variables incluso). Pero si las junto ambas en una consulta...

SELECT * FROM muestra
LEFT OUTER JOIN (SELECT DISTINCT ensayo_muestra.muestraid
FROM ensayo_muestra WHERE ensayo_muestra.inactivo IS NULL
EXCEPT
SELECT DISTINCT ensayo_muestra.muestraid
FROM ensayo_muestra
JOIN actas_archivo ON actas_archivo.ensayo_muestraid = ensayo_muestra.ensayo_muestraid
WHERE (acta_clase = 1 OR acta_clase IS NULL)
) sin_actas ON sin_actas.muestraid = muestra.muestraid
LEFT OUTER JOIN (SELECT DISTINCT muestraid
FROM ensayo_muestra
WHERE (estadoalbaran = 0 OR estadoalbaran IS NULL) AND inactivo IS NULL
) pendientes ON pendientes.muestraid = muestra.muestraid
WHERE sin_actas.muestraid IS NOT NULL AND pendientes.muestraid IS NOT NULL

.. Aquí ya revienta.

Es curioso porque esto mismo con las tablas auxiliares funcionaba perfectamente. Y ya digo que en SQL Server va como un tiro.

De momento creo que voy a soñar con esta query

[seyko]

Primero, cometes un fallo general, haces un left join pero luego en el where dice que tabla_del_left_join.id is not null, esto es como hacer un inner join pero perdiendo eficiencia.

prueba esta select (puede que falle algo, la he hecho corriendo)

Código:

select	*
from 	muestra
		join ensayo_muestra					ON ensayo_muestra.muestraid = muestra.muestraid
		left join actas_archivo 			ON actas_archivo.ensayo_muestraid = ensayo_muestra.ensayo_muestraid
where	ensayo_muestra.inactivo IS NULL
		and (acta_clase != 1 or acta_clase is not null)

y me comentas si los resultados son los mismos que

SELECT * FROM muestra
LEFT OUTER JOIN (SELECT DISTINCT ensayo_muestra.muestraid
FROM ensayo_muestra WHERE ensayo_muestra.inactivo IS NULL
EXCEPT
SELECT DISTINCT ensayo_muestra.muestraid
FROM ensayo_muestra
JOIN actas_archivo ON actas_archivo.ensayo_muestraid = ensayo_muestra.ensayo_muestraid
WHERE (acta_clase = 1 OR acta_clase IS NULL)
)sin_actas ON sin_actas.muestraid = muestra.muestraid
WHERE sin_actas.muestraid IS NOT NULL

Como la cosa se está complicando, muestra la estructura de las tablas indicadas, dime donde tiene FK, etc...

Las variables llevan valor 1 ó 0 en función de un checkbox

Porque no utilizas un boolean?

De momento creo que voy a soñar con esta query

No será la última vez... xD

Salu2

[darthsmog]

Utilizo el LEFT OUTER porque la variable que le paso (que en la simplificación he omitido) en el OR de la condición, hace que, si el usuario quiere, puede aplicar el filtro o no. Si utilzo INNER JOIN, me omitirá directamente los valores nulos.

Voy a tratar de explicar un poquito para qué utilizo esto, a ver si existe otro camino más sencillo, que yo le he dado tantas vueltas que seguro que lo he liado:

Trabajo en un programa para un laboratorio, en el que el elemento esencial es una muestra, almacenado en la tabla muestra con algunas de sus propiedades.

Cada muestra puede tener de 0 a n ensayos, guardados en la tabla ensayos_muestra, con el identificador de la muestra (muestraid). La columna estadoalbaran = 0 (o vacía) en esta tabla nos indica que un ensayo aún no ha sido enviado a facturar.

Cada ensayo puede emitir de 0 a n actas. Las actas emitidas se almacenan en la tabla actas_archivo, con el identificador del ensayo (ensayo_muestraid). Los documentos guardados en actas_archivo pueden ser de muchos tipos, sólo nos interesan los que tienen el campo acta_clase = 1 (o vacío).

Bien. Ahora el objetivo era crear en el programa en java una consulta para hacer una revisión de muestras, de modo que podamos (entre otras cosas) saber qué muestras...

- Contienen algún ensayo que aún no han sido facturados (lo que atacamos en la primera subconsulta)
- Contienen algún ensayo que no tenga acta emitida (no exista entrada en la tabla actas_archivo que se relacione con ningún ensayo de la muestra) (lo que atacábamos en la segunda subconsulta)

El resultado de la consulta se mostrará en una tabla. Y estos dos campos se representan la tabla como un Checkbox, indicando si la muestra está o no facturada (todos sus ensayos) y tiene o no ensayos pendientes. Para activar o desactivar el Checkbox, básicamente, analizo si el campo es vacío o no. Por lo que el valor que contenga el mismo nos es indiferente, siempre y cuando sea vacío cuando no se cumpla, y tenga un valor no nulo en caso contrario.

Aparte el usuario puede elegir mostrar todas las muestras, o filtrar por "pendientes de facturar" y además por "con ensayos pendientes", que es a lo que hacen referencia los WHERE pendientes.muestraid IS NULL OR Variable1 = 0, de modo que se muestran todas si la variable contiene valor 0 o se aplica el filtro cuando tiene otro valor.

Las dos subconsultas obtienen, respectivamente una lista de las muestras que cumplen las condiciones de los dos filtros.

Bueno, no se si lo he liado más. El caso es que esta query me está trayendo por la calle de la amargura ^^

Intuyo que lo tendré que dejar para que lo soluccione mi yo del futuro.

Si se os ocurre algo.. os pongo una estatua.

¡Un saludo y muchas gracias!

[seyko]

Iniciado por darthsmog

Utilizo el LEFT OUTER porque la variable que le paso (que en la simplificación he omitido) en el OR de la condición, hace que, si el usuario quiere, puede aplicar el filtro o no. Si utilzo INNER JOIN, me omitirá directamente los valores nulos.

ok, pero tambien podías haber comentado antes que esa select no está completa

Voy a tratar de explicar un poquito para qué utilizo esto, a ver si existe otro camino más sencillo, que yo le he dado tantas vueltas que seguro que lo he liado:

Trabajo en un programa para un laboratorio, en el que el elemento esencial es una muestra, almacenado en la tabla muestra con algunas de sus propiedades.

Cada muestra puede tener de 0 a n ensayos, guardados en la tabla ensayos_muestra, con el identificador de la muestra (muestraid). La columna estadoalbaran = 0 (o vacía) en esta tabla nos indica que un ensayo aún no ha sido enviado a facturar.

Cada ensayo puede emitir de 0 a n actas. Las actas emitidas se almacenan en la tabla actas_archivo, con el identificador del ensayo (ensayo_muestraid). Los documentos guardados en actas_archivo pueden ser de muchos tipos, sólo nos interesan los que tienen el campo acta_clase = 1 (o vacío).

Bien. Ahora el objetivo era crear en el programa en java una consulta para hacer una revisión de muestras, de modo que podamos (entre otras cosas) saber qué muestras...

- Contienen algún ensayo que aún no han sido facturados (lo que atacamos en la primera subconsulta)
- Contienen algún ensayo que no tenga acta emitida (no exista entrada en la tabla actas_archivo que se relacione con ningún ensayo de la muestra) (lo que atacábamos en la segunda subconsulta)

El resultado de la consulta se mostrará en una tabla. Y estos dos campos se representan la tabla como un Checkbox, indicando si la muestra está o no facturada (todos sus ensayos) y tiene o no ensayos pendientes. Para activar o desactivar el Checkbox, básicamente, analizo si el campo es vacío o no. Por lo que el valor que contenga el mismo nos es indiferente, siempre y cuando sea vacío cuando no se cumpla, y tenga un valor no nulo en caso contrario.

Aparte el usuario puede elegir mostrar todas las muestras, o filtrar por "pendientes de facturar" y además por "con ensayos pendientes", que es a lo que hacen referencia los WHERE pendientes.muestraid IS NULL OR Variable1 = 0, de modo que se muestran todas si la variable contiene valor 0 o se aplica el filtro cuando tiene otro valor.

Las dos subconsultas obtienen, respectivamente una lista de las muestras que cumplen las condiciones de los dos filtros.

Bueno, no se si lo he liado más. El caso es que esta query me está trayendo por la calle de la amargura ^^

Intuyo que lo tendré que dejar para que lo soluccione mi yo del futuro.

Si se os ocurre algo.. os pongo una estatua.

¡Un saludo y muchas gracias!

Vale hoy estoy un poco más liado que ayer, pero es "facil" ;), intentaré sacar un rato a lo largo de la mañana.

Salu2

[seyko]

Código:

select	m.muestraid, resto_campos, count(em.ensayo_muestraid) as ensayos, count(aa.ensayo_muestraid) as actas
from	muestras m
		left join ensayos_muestra em	ON m.muestraid = em.muestraid AND (estadoalbaran is null OR estadoalbaran = 0)
		left join actas_archivo aa		ON em.ensayo_muestraid = aa.ensayo_muestraid AND (acta_clases null OR acta_clase = 1)
group by m.muestraid, resto_campos [, em.ensayo_muestraid]

Con esta select, para cada muestra, tienes el numero de ensayos y actas que tienes agrupados por muestras, ya es facil sacar el listado que te interese.
Tienes la opción de añadir em.ensayo_muestraid al group by, para una agrupación por ensayos, si necesitas saber las actas de un ensayo en lugar de todos los ensayos de la misma muestra.

Con añadir un where como el que comentas para sacar todo o no, deberia de estar terminada.

A la espera