30
Dic
La Query perezosa II
Ya hace algunos días les platique sobre una o más bien dicho unas cuantas consultas perezosas, que con el cambio de servidor su tiempo de ejecución se fue por los cielos, y como también les explique, no había mucho que yo pudiera hacer, al menos no en el servidor, es decir no podía modificar las vistas o los índices que existen en la base de datos.
Siendo honesto, en lo personal no me importaría mucho el tiempo que tarde la consulta en ejecutarse, ya que a final de cuentas yo no voy a ser el usuario del sistema hace uso de esas consultas, pero a la vez podría decirse que el hecho de mejorar el tiempo de respuesta de las consultas lo tome como algo personal, y más aun cuando una de las personas que trabajan en la empresa dueña de la base de datos me comento que ella había logrado reducir una consulta de 20 minutos a solo 15, y que eso era suficiente, tomando en cuenta la cantidad de datos que contiene la tabla sobre la que operaba.
Con eso en mente, y sabiendo que tanto la persona que labora con el cliente como yo, estábamos utilizando la misma tabla, me puse a averiguar bien cual era el problema especifico. Lo primero que hice fue buscar sobre el desempeño de Oracle, sobre todo con tablas grandes como la que nos ocupaba. Enseguida salto a mi vista que la tabla que yo llamaba grande, en realidad era una tabla de proporciones medianas, para Oracle, ya que en algunas cuantas páginas me tope con personas diciendo que sus consultas se tardaban algunos cuantos segundos cuando las ejecutaban, claro que de entrada aclaraban que su tabla contenía alrededor de 20 millones de registros, o alguna cantidad mayor a 10 millones, que obviamente superan por varias veces a la cantidad de datos que hay en nuestra tabla. En casi todos los casos el culpable era un índice que o estaba mal formado, es decir que no contenía todos los campos que requería o que de plano no existía. En todos los casos se logro reducir el tiempo de las consultas a milisegundos, gracias a que se hicieron los cambios pertinentes en los índices. Por desgracia, para mi la historia era otra, yo no puedo modificar los datos almacenados en la tabla y no se diga la estructura de la tabla. Si alguien alguna vez ha estado al frente de un servidor, sabrá que como administrador, no te gusta y sobre todo no aceptas que alguien de fuera, venga a decirte como llevar tu servidor, y un administrador de Base de Datos no es la excepción, si yo tengo la responsabilidad de mantener esto funcionando, porque voy a admitir que alguien más venga a decirme como debo de hacerlo, cuando si algo sale mal al que van a culpar es a mi y no a esa persona. Por lo tanto, agregar el índice estaba totalmente descartado.
Para resolver este tipo de problemas, normalmente basta desempolvar aquellas viejas enseñanzas de Bases de Datos que seguramente llevaste. En los que en vez de ver un manejador de Bases de Datos como FoxPro o DBIII, viste Teoría de Conjuntos, Algebra Relacional y las sentencias de SQL. Como se que la mayoría estará en el primer caso, es decir que vieron un manejador y no SQL, explicare paso a paso como resolver estas cosas. Pero para los que no requieren tanto rollo, basta decir que lo primero es crear un subconjunto usando el índice con clave del empleado, y de ahí seleccionar los que cumplan el criterio completo de nuestra consulta.
Gracias a que yo ya me quebré la cabeza un poco, veremos que salir de este tipo de encrucijadas es de lo más sencillo.
Aquí hay que hacer la aclaración que estas tablas, y las consultas son ficticias, y que si bien se parecen a las reales en algunas cosas, en muchas difieren.
Lo primero es conocer nuestra consulta y la tabla.
La tabla (en este caso vista) esa la siguiente:
vPagosEmpleados.-
Y nuestra consulta tiene que calcular que pago neto recibió nuestro empleado en cada periodo. Por lo que sumaremos los pagos por periodo, dada una matricula.
Select sum(cantidad) cantidad, periodo
From vPagosEmpleado
Where clave = _clave
Group by periodo
Order by periodo Desc;
_clave es el parámetro con la clave a buscar.
Siendo honesto, en lo personal no me importaría mucho el tiempo que tarde la consulta en ejecutarse, ya que a final de cuentas yo no voy a ser el usuario del sistema hace uso de esas consultas, pero a la vez podría decirse que el hecho de mejorar el tiempo de respuesta de las consultas lo tome como algo personal, y más aun cuando una de las personas que trabajan en la empresa dueña de la base de datos me comento que ella había logrado reducir una consulta de 20 minutos a solo 15, y que eso era suficiente, tomando en cuenta la cantidad de datos que contiene la tabla sobre la que operaba.
Con eso en mente, y sabiendo que tanto la persona que labora con el cliente como yo, estábamos utilizando la misma tabla, me puse a averiguar bien cual era el problema especifico. Lo primero que hice fue buscar sobre el desempeño de Oracle, sobre todo con tablas grandes como la que nos ocupaba. Enseguida salto a mi vista que la tabla que yo llamaba grande, en realidad era una tabla de proporciones medianas, para Oracle, ya que en algunas cuantas páginas me tope con personas diciendo que sus consultas se tardaban algunos cuantos segundos cuando las ejecutaban, claro que de entrada aclaraban que su tabla contenía alrededor de 20 millones de registros, o alguna cantidad mayor a 10 millones, que obviamente superan por varias veces a la cantidad de datos que hay en nuestra tabla. En casi todos los casos el culpable era un índice que o estaba mal formado, es decir que no contenía todos los campos que requería o que de plano no existía. En todos los casos se logro reducir el tiempo de las consultas a milisegundos, gracias a que se hicieron los cambios pertinentes en los índices. Por desgracia, para mi la historia era otra, yo no puedo modificar los datos almacenados en la tabla y no se diga la estructura de la tabla. Si alguien alguna vez ha estado al frente de un servidor, sabrá que como administrador, no te gusta y sobre todo no aceptas que alguien de fuera, venga a decirte como llevar tu servidor, y un administrador de Base de Datos no es la excepción, si yo tengo la responsabilidad de mantener esto funcionando, porque voy a admitir que alguien más venga a decirme como debo de hacerlo, cuando si algo sale mal al que van a culpar es a mi y no a esa persona. Por lo tanto, agregar el índice estaba totalmente descartado.
Para resolver este tipo de problemas, normalmente basta desempolvar aquellas viejas enseñanzas de Bases de Datos que seguramente llevaste. En los que en vez de ver un manejador de Bases de Datos como FoxPro o DBIII, viste Teoría de Conjuntos, Algebra Relacional y las sentencias de SQL. Como se que la mayoría estará en el primer caso, es decir que vieron un manejador y no SQL, explicare paso a paso como resolver estas cosas. Pero para los que no requieren tanto rollo, basta decir que lo primero es crear un subconjunto usando el índice con clave del empleado, y de ahí seleccionar los que cumplan el criterio completo de nuestra consulta.
Gracias a que yo ya me quebré la cabeza un poco, veremos que salir de este tipo de encrucijadas es de lo más sencillo.
Aquí hay que hacer la aclaración que estas tablas, y las consultas son ficticias, y que si bien se parecen a las reales en algunas cosas, en muchas difieren.
Lo primero es conocer nuestra consulta y la tabla.
La tabla (en este caso vista) esa la siguiente:
vPagosEmpleados.-
- Clave: Clave del empleado
- Concepto: Concepto del pago
- Cantidad: Cantidad del pago
- Periodo: Periodo en el que se realiza el pago.
Y nuestra consulta tiene que calcular que pago neto recibió nuestro empleado en cada periodo. Por lo que sumaremos los pagos por periodo, dada una matricula.
Select sum(cantidad) cantidad, periodo
From vPagosEmpleado
Where clave = _clave
Group by periodo
Order by periodo Desc;
_clave es el parámetro con la clave a buscar.
La otra consulta que veremos sera la que calcula solo una parte del
sueldo, es decir su sueldo sin comisiones, solamente se le sumara el
bono por asistencia y puntaualidad.
Select sum(cantidad) as cantidad, periodo
From vPagosEmpleado
Where clave = _clave and concepto in('sueldo','bono_asistencia')
Group by periodo
Order by periodo Desc;
Como ya mencione, el diseño de la base de datos no es algo que vayamos a modificar o a evaluar, por lo que si les resulta extraño que se usen conceptos en texto plano no es mi culpa.
Por medio de prueba y error sabemos que existe un índice en la columna clave, por lo que nos vamos a agarrar de ahí. La lógica nos dice que entre menos datos utilicemos, menor será el tiempo para procesarlos. Bien entonces obtengamos un subconjunto de datos, es decir solamente los pagos hechos a ese trabajador. Esto lo hacemos con la siguiente consulta:
Select cantidad, periodo
From vPagosEmpleado
Where clave = _clave;
Que si vemos se parece mucho a nuestra consulta inicial, solo que en esta no interviene el periodo y no hay ningun group ni order by. Pero a diferencia de la primera, esta consulta se ejecuta en solo milisegundos, lo cual nos indica que vamos por el buen camino.
El detalle esta en que esta consulta no nos sirve de mucho, puesto que no arroja los resultados que nosotros requerimos, por lo que ahora la integramos a nuestra consulta inicial, pero en una subquery.
Select sum(cantidad) as catidad, periodo
From
(Select cantidad, periodo
From vPagosEmpleado
Where clave = _clave
)
Group by periodo
Order by periodo Desc;
Con esta modificación, nuestra consulta baja a solo 1 segundo o menos. Esto se debe a que ahora en vez de hacer un escaneo de toda la tabla, ahora se opera solo sobre un subconjunto, que es una minima parte del conjunto original. Supongamos que un empleado tiene registrados 1000 pagos en 2 años y 5 meses, cuando se ejecuta la primera consulta, se extraen esos 1000 registros de la tabla principal y se mandan a una tabla temporal en memoria, luego sobre esa tabla que es el resultado de la subquery, se efectúa la operación sum y se ordenan por periodo.
En el segundo caso donde la suma es aparte regida por otras condiciones, es decir que el tipo de pago pertenezca a un grupo determinado, se debe de tomar en cuanta si existe un índice que aplique para ambos campos, si es así, entonces el where se pasaría competo hacia la parte de la subquery, pero en caso de que no existiera dicho índice, el where se partiría en dos de la siguiente manera:
Select sum(cantidad) as cantidad, periodo
From
(Select cantidad, periodo
From vPagosEmpleado
Where clave = _clave
)
Where concepto in('sueldo','bono_asistencia')
Group by periodo
Order by periodo Desc;
Bien hasta aquí ya tenemos calculados nuestros pagos totales y de puro sueldo, pero para mi caso, ser requería que ambos fueran juntos, es decir que en una sola tabla, por así decirlo tuviéramos esa información.
Bien la solución es meter ambas consultas como subconsultas y hacer un inner join entre ellas.
Select a.periodo ,a.cantidad as sueldoTotal, b.canditdad as sueldoBase
from (
Select sum(cantidad) as catidad, periodo
From (
Select cantidad, periodo
From vPagosEmpleado
Where clave = _clave
)
Group by periodo
Order by periodo Desc
) a
inner join (
Select sum(cantidad) as cantidad, periodo
From
(Select cantidad, periodo
From vPagosEmpleado
Where clave = _clave
)
Where concepto in('sueldo','bono_asistencia')
Group by periodo
Order by periodo Desc
) b on a.periodo = b.periodo
Esta consulta nos dara como resultado una tabla con 3 campos, Periodo, SueldoTotal y SueldoBase.
Como podemos observar, esta consulta resuelve el problema al que nos enfrentábamos, de una manera, que tal vez a la vista no resulte muy elegante, pero que en la práctica es completamente funcional y sobre todo veloz.
Saludos.
Select sum(cantidad) as cantidad, periodo
From vPagosEmpleado
Where clave = _clave and concepto in('sueldo','bono_asistencia')
Group by periodo
Order by periodo Desc;
Como ya mencione, el diseño de la base de datos no es algo que vayamos a modificar o a evaluar, por lo que si les resulta extraño que se usen conceptos en texto plano no es mi culpa.
Por medio de prueba y error sabemos que existe un índice en la columna clave, por lo que nos vamos a agarrar de ahí. La lógica nos dice que entre menos datos utilicemos, menor será el tiempo para procesarlos. Bien entonces obtengamos un subconjunto de datos, es decir solamente los pagos hechos a ese trabajador. Esto lo hacemos con la siguiente consulta:
Select cantidad, periodo
From vPagosEmpleado
Where clave = _clave;
Que si vemos se parece mucho a nuestra consulta inicial, solo que en esta no interviene el periodo y no hay ningun group ni order by. Pero a diferencia de la primera, esta consulta se ejecuta en solo milisegundos, lo cual nos indica que vamos por el buen camino.
El detalle esta en que esta consulta no nos sirve de mucho, puesto que no arroja los resultados que nosotros requerimos, por lo que ahora la integramos a nuestra consulta inicial, pero en una subquery.
Select sum(cantidad) as catidad, periodo
From
(Select cantidad, periodo
From vPagosEmpleado
Where clave = _clave
)
Group by periodo
Order by periodo Desc;
Con esta modificación, nuestra consulta baja a solo 1 segundo o menos. Esto se debe a que ahora en vez de hacer un escaneo de toda la tabla, ahora se opera solo sobre un subconjunto, que es una minima parte del conjunto original. Supongamos que un empleado tiene registrados 1000 pagos en 2 años y 5 meses, cuando se ejecuta la primera consulta, se extraen esos 1000 registros de la tabla principal y se mandan a una tabla temporal en memoria, luego sobre esa tabla que es el resultado de la subquery, se efectúa la operación sum y se ordenan por periodo.
En el segundo caso donde la suma es aparte regida por otras condiciones, es decir que el tipo de pago pertenezca a un grupo determinado, se debe de tomar en cuanta si existe un índice que aplique para ambos campos, si es así, entonces el where se pasaría competo hacia la parte de la subquery, pero en caso de que no existiera dicho índice, el where se partiría en dos de la siguiente manera:
Select sum(cantidad) as cantidad, periodo
From
(Select cantidad, periodo
From vPagosEmpleado
Where clave = _clave
)
Where concepto in('sueldo','bono_asistencia')
Group by periodo
Order by periodo Desc;
Bien hasta aquí ya tenemos calculados nuestros pagos totales y de puro sueldo, pero para mi caso, ser requería que ambos fueran juntos, es decir que en una sola tabla, por así decirlo tuviéramos esa información.
Bien la solución es meter ambas consultas como subconsultas y hacer un inner join entre ellas.
Select a.periodo ,a.cantidad as sueldoTotal, b.canditdad as sueldoBase
from (
Select sum(cantidad) as catidad, periodo
From (
Select cantidad, periodo
From vPagosEmpleado
Where clave = _clave
)
Group by periodo
Order by periodo Desc
) a
inner join (
Select sum(cantidad) as cantidad, periodo
From
(Select cantidad, periodo
From vPagosEmpleado
Where clave = _clave
)
Where concepto in('sueldo','bono_asistencia')
Group by periodo
Order by periodo Desc
) b on a.periodo = b.periodo
Esta consulta nos dara como resultado una tabla con 3 campos, Periodo, SueldoTotal y SueldoBase.
Como podemos observar, esta consulta resuelve el problema al que nos enfrentábamos, de una manera, que tal vez a la vista no resulte muy elegante, pero que en la práctica es completamente funcional y sobre todo veloz.
Saludos.
Escribir un comentario