Ayuda con consulta lenta, lenta, lenta

[Gregory Mazon]

Hola foro, por lo regular no pregunto por que casi todo lo encuentro en las busquedas pero esta ocasion si necesito de su ayuda. Espero me puedan ayudar

Trabajo con D7 Interbase 6 y utilizo componentes IBX

Estoy realizando un sistema para una cadena de tiendas, en donde tengo un reporte de ventas x articulo, todo iba bien pero con el tiempo el reporte se tarda 10 MINUTOS en mostrarlo. Este reporte debe de mostrar cuantos articulos se han vendido en toda la cadena en un rango de fechas, para este reporte hago una consulta sobre una vista en donde se sacan datos de 2 tablas basicamente detalle y maestro

mi vista es la siguiente:

Código SQL [-]
CREATE VIEW VDETALLE(
    IDTIENDA,
    IDREFER,
    IDARTICULO,
    FECHA,
    REFER,
    CANTIDAD,
    PRECIO,
    COSTO,
    DESCUENTO,
    DESCUENTOGRAL,
    PVNETO)
AS
select  d.idtienda, d.idrefer, d.idarticulo, m.fecha, m.refer,
d.cantidad, d.precio, d.costo, d.descuento, m.Descuento,
d.Precio * (1 - (100-(100 * (100-d.Descuento)/100) * (100-m.Descuento)/100) /100)
from detalle d
Left join maestro m on m.idTienda = d.idtienda and m.idRefer = d.idRefer;

La consulta que estoy realizando es:

Código SQL [-]
Select sum(d.Cantidad) Cantidad, sum(d.cantidad*d.pvNeto) Venta,
sum(d.cantidad * d.costo) Costo, d.idarticulo
from vDetalle d
Where d.fecha between :wDate1 and :wDate2 
Group by d.idArticulo

 
Tabla Maestro:
  idTienda       Integer                          (llave primaria)
  idRefer        Integer                           (llave primaria) 
  Fecha          Date
  Refer          Char(10)
  Nombre         VarChar(70)
  Impuesto       Numeric    12   2
  Importe        Numeric    12   2
  Costo          Numeric    12   2
  Unidades       Integer
  Descuento      Numeric    5  2

Tabla detalle:
   idTienda      Integer               (llave primaria)
   idRefer       Integer                (llave primaria)
   idArticulo    Integer                (llave primaria)
   Cantidad      Integer
   precio         Numeric   10    2
   costo         Numeric   10    2
   Descuento     Numeric    5    2

Nota: La tabla maestro tiene cerca de 2,000,000 de registros y la de detalle tiene aprox. 3,300,000 registros, la DB mide 1.3Gb y esta en un servidor windows 2000 server con 1Gb en RAM y se conectan unos 120 usuarios simultaneamente. Lo mas importante en la tabla de detalle se insertan unos 30,000 registros al dia

Cualquier sugerencia se los agradece y de antemano muchas gracias

[[Caral]]

Hola
Me extraña mucho, seguro que necesitas leer los 2 0 3 millones de registros de una tabla?, muy raro, normalmente uno lo que hace es un acomodo de la informacion, osea, que el reporte devuelva solo lo que interesa.
Yo aparte de esto usaria un Limit 50 en la consulta.
No se, solo opino.
Saludos

[Neftali [Germán.Estévez]]

Yo haría algunas pruebas con SP's.
Intentar primero "cortar" las tablas con los WHERE para hacerlas más pequeñas y volcarlas a temporales y luego aplicar la Join.
Asegurate de que los índices son correctos.
Otra opción es mantener los cálculos por trigger (para tenerlos ya hechos), eso minimizará las consultas posteriores, aunque desconozco si es viable en este caso.

Revisa el plan de ejecución si es posible para ver lo que se está haciendo y los tiempos de las distintas operaciones.

[Casimiro Notevi]

Además de tener muy en cuenta las recomendaciones de los compañeros, también ganarás muchísimo, muchísimo, muchísimo... si cambias ese obsoleto Interbase 6 por un Firebird 1.5 (interbase 6 es de 1999), no creo que tengas problema alguno, ya que son básicamente compatibles.
Por supuesto, otra necesidad muy, muy, muy importante: cambia ese windows 2000 por un Linux: Debian, Suse, Ubuntu, RedHat... la que quieras, pero cámbialo.
Ganarás muchíiiiiiiiiiiiiiiiiisimo, te lo aseguro.

[Gregory Mazon]

Muchas gracias por sus comentarios.

Pero aqui realmente el problema por lo que he podido checar no es tanto los 2 o 3 millones de registros, sino el Where d.fecha between :wDate1 and :wDate2, este campo se encuentra en al tabla maestro, por cierto ya intente poner un indice sobre este campo pero no mejora la velocidad, tambien ya intente hacer SP's y no me resultan

Empezado por Casimiro Notevi

si cambias ese obsoleto Interbase 6 por un Firebird 1.5
cambia ese windows 2000 por un Linux: Debian, Suse, Ubuntu, RedHat...

He oido maravillas sobre esos servidores pero de momento no tengo nada para hacer pruebas. Firebird 1.5 lo voy a instalar para ver como me funciona, pero con esto una duda:
puedo tener en el server Firebird y en las terminales IB?
pregunto esto por que las terminales se conectan de forma remota (VPN) y para porder cambiar TODAS estas terminales seria algo que me llevaria algunos dias

Hay manera de poner indeces a las vistas?

[Casimiro Notevi]

Empezado por Gregory Mazon

[..]puedo tener en el server Firebird y en las terminales IB? pregunto esto por que las terminales se conectan de forma remota (VPN) y para porder cambiar TODAS estas terminales seria algo que me llevaria algunos dias [..]

Poder, poder... puedes, aunque no es una buena idea hacerlo, más bien puede ser una fuente de problemas ocultos.

[RolphyReyes]

Saludos.

Yo opino que si te vas a cambiar a FireBird que sea a la 2.0.1 que es bastante estable tiene muchas mejoras y funciones ademas de muchos bugs corregidos.

[Gregory Mazon]

Gracias RolphyReyes,
ya descarge la version Firebird-2.0.0.1 la voy a instalar para hacer pruebas, pero tambien me comentan que deberia ir contemplando mejor algo como ORACLE o SQLServer por la cantidad de registros que se almacenan y por el tamaño de la DB

[[eduarcol]]

La verdad es que es mas una mania que algo valedero pero en lo posible evito usar funciones en consultas sql que manejen muchos registros asi qeu yo haria algo asi:

Código SQL [-]
Where (d.fecha >= :wDate1) and (dFecha <=:wDate2)

y crearia un indice por fecha

Pero repito es solo una mania no se que tantos recursos puede consumir la funcion between y si toma en cuenta o no los indices

//Suerte

[Neftali [Germán.Estévez]]

Por cierto, es una pregunta para los Masters de IB/FB relacionada con esto. ¿Se puede ver el plan de ejecución de la consulta en algun sitio? ¿Alguna herramienta externa? ¿IBConsole? ...

[Delfino]

La causa de la lentitud esta aqui :

Código SQL [-]
Left join maestro m on m.idTienda = d.idtienda and m.idRefer = d.idRefer;

Prueba a quitar el left a ver q pasa..

Se puede ver el plan de ejecución de la consulta en algun sitio?

Si, con el IBExpert, por lo menos, y se vera claramente q no utilza los indices, si no q recorre la tabla detalle secuecialmente, de alli la lentitud..

[Ñuño Martínez]

He estado buscando y he encontrado una aplicación llamada InterBase/Firebird Development Studio que incluye una utilidad llamada Query Analizer que al parecer ayuda en la optimización de las sentencias SQL. Es de pago pero tiene una versión de evaluación.

En cuanto a lo de ver la planificación de la ejecución, con MySQL se usa la sentencia EXPLAIN. Por ejemplo:

Código SQL [-]
EXPLAIN SELECT * FROM tabla

Te devuelve una tabla con distintos campos.

No sé si esto puede servir, porque he estado buscando y sólo encuentro explicaciones para MySQL o, como mucho, esta explicación en inglés sobre los índices de InterBase/Firebird.

[Casimiro Notevi]

El que yo uso desde hace años es el Interbase PlanAlyzer, muy útil.

[[Lepe]]

al menos en IB Expert Personal, me dice que el plan de ejecución no se puede ver.

En Marathon (openSource) si se ve... otra cosa es entender cada detalle que dice ¿alguna lectura recomendad? Asias

Saludos

[Gregory Mazon]

Gracias por todos sus comentarios

ya estube haciendo pruebas con lo que me han comentado, ya le quite la funcion BETWEEN tal como lo dice eduarcol, ya le quite el Left tal como dice Delfino. Me falta hacer la prueba con Firebird, y ya cheque que mis indices esten correctos, y si es verdad mejoro mi consulta en vez de tardarse 10 minutos como antes ahora se tarda 3 minutos, no se si todavia se pueda mejorar con Firebird lo voy probar, pero aqui una de las cosas q mas me preocupan es que estos 3 millones de registros son solo de informacion de solo 5 meses (es el tiempo que lleva funcionando el sistema), que va ha pasar con informacion acumulada de 1 o 2 años, por q pretenden tener reportes comparativos de cuanto se vendio el 21/jun/07 vs. el 21/jun/08.

Empezado por Neftali

Revisa el plan de ejecución si es posible para ver lo que se está haciendo y los tiempos de las distintas operaciones.

Que es el plan de ejecución como los debo de interpretar?
Utilizo el IB Expert Personal Edition y cuando hago mi consulta en la parte inferior me regresa:

Plan
PLAN SORT (MERGE (SORT (DR REM INDEX (REMISION_IDX1)),SORT (JOIN (DR DR NATURAL,DR ART INDEX (RDB$PRIMARY1)))))

Adapted Plan
PLAN SORT (MERGE (SORT (DR REM INDEX (REMISION_IDX1)),SORT (JOIN (DR DR NATURAL,DR ART INDEX (PK_ARTICULOS)))))

[[Lepe]]

En la propia ayuda de IB Expert viene que al usar un plan "NATURAL" realmente no se está usando índices, por tanto sería una consulta NO optimizada.

Según explica hay veces que es mejor no usar índices (aún cuando existan) porque la tabla es pequeña y el uso del índice lo hace tardar más.

Como ves yo ando buscando información sobre el tema, pero no puedo hablar mucho.

Saludos

[Delfino]

Puedes poner el plan de antes de quitar el left y despues? asi podremos ayudarte a leerlo y interpretarlo,
como dice lepe, natural significa q escanea todas las filas,
basicamente en un plan la informacion mas valiosa es cuando un indice se usa o no, y por cierto usando los indices adecuadamente daria igual q cantidad de registros hay, el acceso es directo..

[Gregory Mazon]

Gracias, con el Left me muestra lo siguiente:

Plan:
PLAN SORT (JOIN (DR DR NATURAL,DR REM INDEX (RDB$PRIMARY16)))

Plan Adaptado:
PLAN SORT (JOIN (DR DR NATURAL,DR REM INDEX (PK_REMISION)))

quitandole el Left me muestra lo siguiente:

Plan:
PLAN SORT (JOIN (DR REM INDEX (REMISION_IDX1),DR DR INDEX (RDB$PRIMARY28)))

Plan Adaptado:
PLAN SORT (JOIN (DR REM INDEX (REMISION_IDX1),DR DR INDEX (PK_DE_REM)))

con la primer opcion la consulta se tarda 8 minutos, con la segunda opcion la misma consulta se tarda 4 minutos

[Delfino]

Porque en la tabla maestro la clave primaria esta compuesta por dos campos? estas cosas se hacian en tiempos de los dbf,

Se agilizara bastante pero bastante la ejecucion de la query si le pones una clave primaria compuesta por un solo campo ID, y el join se haga por un solo campo, tb creando un indice sobre algun campo de la select, el campo cantidad por ejemplo.

Por cierto, el uso de between no se diferencia en nada a la otra forma, pq internamente el motor se encarga de traducirla, pero between es mejor legible en una consulta..

[seara2005]

Yo no soy experto en estas materias, pero la lógica y mi criterio me dicen que si hoy estas analizando como reducir de 8 a 4 o 3 min la consulta, en un año estarás viendo como reducirla de 20 a 10, etc. por tanto te recomendaría que acotaras la información a procesar para las consultas frecuentes, por ejemplo yo incluiría un campo Inicial por cada información que se necesite de forma tal que al pasar de un mes a otro, el sistema actualice un acumulado hasta ese cierre en este campo y las consultas siguientes estarían acotadas a ese saldo mas los movimientos del mes actual. Así el rendimiento del sistema prácticamente no mermaría con el paso del tiempo ya que las consultas siempre son de operaciones de un mes.
El cierre y asignación de saldos iniciales del mes se hace una sola vez en el período así que no es un problema crítico.