Optimización de consultas

[alyciashape]

Últimamente ando manejando consultas muy grandes con PHP y ODBC a una DBase y tengo problemas de optimización continuamente. Al final lo que hago normalmente para ahorrar tiempo al script es hacer cosas así:

Código PHP:

$consulta_tabla_a = "SELECT * FROM tabla a";
$consulta_tabla_b = "SELECT * FROM tabla b";
 
foreach ($consulta_tabla_a AS $tabla_a) {
consulto en tabla b los datos relacionados con a
}
 

A esto se puede añadir la variante de meter todos los datos en arrays y luego ir enlazandolos sin hacer consultas. Es la única manera que logro de mantener un tiempo de respuesta estable.

Pero ahora ya me encuentro totalmente atascada. Tengo que extraer el stock actual para todos los artículos. Para ello, debido al mal diseño de la base de datos, es contar los movimientos que tiene cada artículo hasta el día de hoy, desde 2008. Además como tenemos más de una empresa esos movimientos están ligados a empresas.

Por lo tanto. Hay unos 30 mil movimientos para 8 mil artículos que a su vez hay que conectar con las empresas, pero también quieren separar compras y ventas, así que tengo que conectarlo a los pedidos, albaranes y facturas. Como se puede ver la cantidad de datos es muy grande. Lo tenía así:

articulos = meto todos los datos en un array

por cada articulo consulto los movimientos de ese artículo y a que empresa corresponde y los voy metiendo un array los resultados.

El problema es que la consulta ya tarda unos 20 minutos o 30 y me parece excesivo. Quería reducirlo metiendo todos los movimientos en un array, pero me da error el apache de que me paso de 128 mb de memoria en una sóla consulta. Ampliar la memoria me parece una solución pero creo que es una animalada una consulta de 128 mb.

La duda es esa, si tenéis algún consejo para optimizar estos temas. Veo complicado el asunto.

Es mejor meter consultas en variables y trabajar con ellas desde ahí o lanzar muchas consultas pequeñas?

El mal diseño de la base de datos incluso me obliga a hacer diferencias entre facturas, albaranes y demás de años distintos pues los precios no son los que deberian y tengo que hacer calculos sobre ellos.

¿Como trabajais con consultas muy grandes?

[gnzsoloyo]

¿Como trabajais con consultas muy grandes?

La primera regla de buenas consultas masivas es... diseñar correctamente la base.

Lamentablemente te has metido en un proyecto que puede sacarle canas verdes a cualquiera, y no es sencillo de resolver.
En primer lugar, no tiene sentido ni utilidad intentar resolver las consultas con la base como está, porque jamás serán buenas. Lo que te conviene es migrar la base a una estructura mejor, realizando la normalización y depuración de todos los datos-basura que tienes actualmente. SI no lo haces, sólo estarás perdiendo tiempo y esfuerzo.
En resumen: Todo lo que puedas invertir de tiempo para normalizar esa "cosa" que tienes de fuente de datos, será tiempo ganado al realizar las consultas.

[alyciashape]

Si, está planeado convertir toda la base de datos a otro sistema e incluso el programa de gestión pero tengo una semana para realizar el proyecto y no hay tiempo para pasarlo a otra base de datos.

Bueno, les daré los datos que pueda extraer y que me permita el sistema actual. No me preocupa demasiado porque ya les explique profundamente por qué esta base de datos está mal diseñada (cualquiera con mínimos de conocimientos verá errores de diseño por todos lados) y lo han visto con sus propios ojos, por lo que saben lo que hay.

No se, me preguntaba si había magia o algo que desconocía. Porque tampoco se pueden hacer índices a esta base de datos, aunque no se si me servirían de mucho.

En fin, gracias por el consejo.

[Malenko]

Yo trabajo en una empresa relacionada con el tema de telefonia que tiene millones de registros diarios y las consultas tardan segundos. Las dos consultas iniciales son una aberración, no puedes hacer un "SELECT *" porque te estás traiendo toda la información de la bbdd y seguramente no necesitas todos los campos.

Además, en lugar de hacer dos selects y el foreach, podrías ahorrarte faena y tiempo haciendo una "inner join". Por último, una opción muy buena para reducir el tiempo es trabajar contra vistas que tengan los campos justos y necesarios y que ataquen a las tablas por detrás.

Y se me olvidaba! Incluye todo el código en un store procedure. De esta forma el plan de ejecución de la consulta solo se compila una vez. Es decir, la primera vez que lances la consulta te puede ir un poco lenta, pero las siguientes irá como el rayo. En cambio al realizar las consultas directamente en SQL, el plan de ejecución se ha de compilar cada vez que lo ejecutas.

[alyciashape]

Gracias por el consejo Malenko.

El tema es, que la base de datos es DBase y me conecto a ella por ODBC. No soporta ni índices, ni procedures (que yo sepa) ni vistas.

El tema es que lo separé en 2 consultas precisamente por eso, porque era muy lenta haciendolo con un left join (algunos artículos no tienen todavía facturas pero tienen que salir) y si, tengo que recorrer todos los artículos porque se trata de sacar un informe con todos los artículos, stock actual, ventas por empresa, compras por empresa, precio medio venta, etc etc etc.

Y haciendolo bien no son 2, sino unas 5 o más tablas vinculadas pero haciendo pruebas a mi me pareció bastante más rápido meter las consultas en arrays y consultarlas así que haciendo una consulta con tablas enlazadas. No se si por capricho del ODBC o por lo qué.

Pero me apunto que en principio con los join debería ir mejor. Sigo dándole vueltas y pruebas al asunto. Ojalá soportara vistas la bd...

[Malenko]

Podrias poner las consultas? Para saber como las cruzas y como las haces.

Si vais por odbc,un cambio de gestor de bbdd tendría que ser transparente para un futuro ;)

[alyciashape]

Pues en cuanto pueda podré algunas. Gracias por la atención.

Por cierto, me estaba preguntando si es más rápido pedir todos los campos (*) o filtrar y pedir sólo aquellos que necesitas, aunque sean prácticamente todos. Vale la pena? Lo probaré también.

[Malenko]

Iniciado por alyciashape

Pues en cuanto pueda podré algunas. Gracias por la atención.

Por cierto, me estaba preguntando si es más rápido pedir todos los campos (*) o filtrar y pedir sólo aquellos que necesitas, aunque sean prácticamente todos. Vale la pena? Lo probaré también.

Esa es precisamente una de las cosas que te dije :P Es más rápido pedir solo los campos que necesitas porque como has de pasar menos información desde la BBDD hasta la aplicación, consumes menos recursos y los datos pasan con mayor velocidad.

[alyciashape]

Un ejemplo:

Código MySQL:

SELECT subctas38.clasct, subctas38.numero, subctas38.titulo, SUM(diario38.debe) AS 'debe', SUM(diario38.haber) AS 'haber'
    FROM subctas38, diario38 WHERE subctas38.clasct = diario38.clasct AND year(diario38.fecha)=2012 AND diario38.fecha <= DATE(2012, 11, 30) AND numero >=430000000 AND numero<440000000
    GROUP BY subctas38.titulo;
  
//Efectos totales
SELECT SUM(importe) AS 'Importe' FROM efectos, clientes 
WHERE efectos.clacli=clientes.clacli AND clientes.nctacon=$cuenta_cliente            
            AND libram <= DATE(2012, 11, 30) AND claemp=2;
 
//Efectos devueltos
SELECT SUM(importe) AS 'Importe' FROM efectos, clientes 
WHERE efectos.clacli=clientes.clacli AND clientes.nctacon=$cuenta_cliente            
            AND libram <= DATE(2012, 11, 30) AND claemp=2 AND devuelto=1;
 
//Efectos pendientes
SELECT SUM(importe) AS 'Importe' FROM efectos, clientes 
WHERE efectos.clacli=clientes.clacli AND clientes.nctacon=$cuenta_cliente            
            AND libram <= DATE(2012, 11, 30) AND claemp=2 AND resuelto=0;
 
//Efectos posteriores
SELECT SUM(importe) AS 'Importe' FROM efectos, clientes 
WHERE efectos.clacli=clientes.clacli AND clientes.nctacon=$cuenta_cliente            
            AND libram > DATE(2012, 11, 30) AND claemp=2 AND resuelto=1;
 
//Total
 

Meto los datos en un array que finalmente se mostrarán en el CSV

Tarda unos 10 minutos. Al final del script tengo todos los datos en $resultado y los imprimo en un CSV con una clase que tengo.

Funciona así. Con la primera consulta saco el nombre, cuenta y algunos datos que necesito de los clientes, así como el haber y el debe total del año hasta el mes que le indico.

Luego para cada cliente tengo que sacar diferentes cantidades de los efectos, vienen de otra base de datos y es para comparar la contabilidad con el programa de gestión y ver si coinciden los datos.

Claro, por cada usuario tiene que sacar mogollón de información. Además los efectos me pidieron que sean desde "siempre", en este caso 2008 que es desde cuando existe la empresa, hasta el mes que le digan.

Si se te ocurre alguna forma de optimizarlo bienvenida sea.

Además tenemos 3 empresas o nombres comerciales y tengo que hacer eso por triplicado, pero ya ni me planteo sacar todos los datos juntos. Los saco de una en una eligiendo en otra ventana con un select la empresa que te interese, la fecha, etc.

Recuerda que es un ODBC que va con DBase y no soporta muchas opciones de BD modernas.

No se si es que no se lo suficiente o es lo que hay y no puedo sacar los datos de forma más eficiente.

PD: Si vas a sugerir subconsultas que sepas que no las soporta en la parte SELECT, solo en la parte WHERE puedo realizar subconsultas.

[gnzsoloyo]

alyciashape: Ese script es código PHP, y eso es programación, tema que está completamente off-topic en este foro, y transgrede las normas de los Foros de Bases d Datos.
Si quieres tratar este problema desde el punto de vista de la programación, puedo pasarte el post al Foro de PHP, pero de lo contrario tendré que quitar todo el código PHP y dejar el SQL, que es lo que tratamos en este foro.
Dime qué quieres hacer.

Para que se entienda mejor: No todos los que hacemos BBDD programamos aplicaciones, ni tampoco todos programan en PHP.
A los efectos de este foro, todo lenguaje de programaciñón "nesucia" al SQL, esconde problemas detrás de variables que son inaccesibles para los DBMS y encima hace que resulte dificultoso de leer.
Por esas y otras razones más, programación no se trata en este foro. Para eso existen los subforos de los lenguajes de programación.

[alyciashape]

Si, de hecho lo único que interesa de ahí es el código SQL, el resto da igual. Es lo que quería enseñar.

Lo dejé así para que se viera un poco que el primer SQL es digamos el principal y los otros extraen datos a partir de ese. Y la parte del array la dejé para que se viera los campos que necesito al final.

Iba a modificarlo yo para retirar el resto pero no veo el botón editar por ningún lado.