Consulta Super Lenta

[AGAG4]

Bienvenido Club Delphi, y muchas Felicidades por su nuevo aspecto.

Uso Firebird 1.53 con IBExpert 2006

Buen día tengan compañeros, les informo mi problema, tengo una consulta que tiene la finalidad de Sacar las Ventas Netas = Ventas - (Devoluciones + Notas de Crédito) del Año Actual y el Año Anterior, ahora bien, todo marcha bien hasta toparme con el ORDER BY , del cual quiero ordenar por el TOTAL del Año Actual Descendentemente donde este campo Virtual esta posicionado en el campo 6 de la propia consulta pero si la ejecuto tarda más de 6 minutos, pongo la Sentencia SQL:

Código SQL [-]
SELECT FIRST 20 F.CLAVEART,A.DESART,A.MARCA,A.GRUPO,
  COALESCE(SUM(F.CANTIDAD),0)-
  (SELECT COALESCE(SUM(CANTIDAD),0) FROM TINV_INVMOVIM
   WHERE (TIPOMOV='DM' OR TIPOMOV='DX') AND
   CLAVEART = F.CLAVEART AND
   FECHAMOV BETWEEN '01.01.2006' AND '24.03.2006') CANTIDAD,
  COALESCE(SUM(F.IMPSIVA),0)-
  ((SELECT COALESCE(SUM(IMPORTE),0) FROM TINV_INVMOVIM
    WHERE (TIPOMOV='DM' OR TIPOMOV='DX') AND
    CLAVEART = F.CLAVEART AND
    FECHAMOV BETWEEN '01.01.2006' AND '24.03.2006')+
   (SELECT CASE WHEN COALESCE(SUM(C.IMPARTNC),0)>0 THEN COALESCE(SUM(C.IMPARTNC),0)   ELSE 0 END FROM TCXC_CLIMOVIM C
    RIGHT JOIN TFAC_FACTURAS FF ON (FF.TIPOMOV = SUBSTRING(C.FACTURA FROM 1 FOR 2) AND FF.FOLIOFISCAL= CAST(SUBSTRING(C.FACTURA FROM 3 FOR 6) AS INTEGER))
    RIGHT JOIN TFAC_DFACTURAS FD ON (FD.TIPOMOV = FF.TIPOMOV AND FD.FOLIO = FF.FOLIO AND FD.CLAVEART=F.CLAVEART)
    WHERE (TIPOMOV='NM' OR TIPOMOV='NX') AND
    FECHAMOV BETWEEN '01.01.2006' AND '24.03.2006')) TOTAL,
   (SELECT COALESCE(SUM(IMPSIVA),0) FROM TFAC_DFACTURAS
    WHERE CLAVEART = F.CLAVEART AND TIPOMOV ='FM' AND
    FECHAFAC BETWEEN '01.01.2005' AND '24.03.2005') -
   ((SELECT COALESCE(SUM(IMPORTE),0) FROM TINV_INVMOVIM
     WHERE (TIPOMOV='DM' OR TIPOMOV='DX') AND
     CLAVEART = F.CLAVEART AND
     FECHAMOV BETWEEN '01.01.2005' AND '24.03.2005') +
    (SELECT CASE WHEN COALESCE(SUM(C.IMPARTNC),0)>0 THEN COALESCE(SUM(IMPARTNC),0) ELSE 0 END FROM TCXC_CLIMOVIM C
     RIGHT JOIN TFAC_FACTURAS FF ON (FF.TIPOMOV = SUBSTRING(C.FACTURA FROM 1 FOR 2) AND FF.FOLIOFISCAL = CAST(SUBSTRING(C.FACTURA FROM 3 FOR 6) AS INTEGER))
     RIGHT JOIN TFAC_DFACTURAS FD ON (FD.TIPOMOV = FF.TIPOMOV AND FD.FOLIO = FF.FOLIO AND FD.CLAVEART=F.CLAVEART)
     WHERE (TIPOMOV='NM' OR TIPOMOV='NX') AND
     FECHAMOV BETWEEN '01.01.2005' AND '24.03.2005')) TOTANT,
    (SELECT COALESCE(SUM(CANTIDAD),0) FROM TFAC_DFACTURAS
     WHERE CLAVEART = F.CLAVEART AND
     TIPOMOV ='FM' AND
     FECHAFAC BETWEEN '01.01.2005' AND '24.03.2005') -
    (SELECT COALESCE(SUM(CANTIDAD),0) FROM TINV_INVMOVIM
     WHERE (TIPOMOV='DM' OR TIPOMOV='DX') AND
     CLAVEART = F.CLAVEART AND
     FECHAMOV BETWEEN '01.01.2005' AND '24.03.2005') CANTANT
FROM TFAC_DFACTURAS F
JOIN TINV_ARTICULOS  A ON (F.CLAVEART = A.CLAVEART)
WHERE F.FECHAFAC BETWEEN '01.01.2006' AND '24.03.2006' AND
F.TIPOMOV ='FM'
GROUP BY F.CLAVEART,A.DESART,A.MARCA,A.GRUPO
ORDER BY 6 DESC

Ahora bien, si cambio el Order By a :
ORDER BY COALESCE(SUM(F.IMPSIVA),0) DESC
tarda 6 Segundos, pero no me ordenará correctamente la consulta debido a que solo estoy ordenando las VENTAS y no estoy considerando las
Devoluciones y Notas de Crédito, mi duda es la siguiente:
Será lo mismo poner ORDER BY TOTAL DESC que ORDER BY 6 DESC ????
en el primero me marca error que columna no se reconoce, lo que pasa es que la columna virtual TOTAL tiene el importe TOTAL NETO que quiero ordenar, mi objetivo es Disminuir el tiempo de la consulta a 10 ó 20 segundos, espero que alguien me haya entendido, si no estoy a sus ordenes.

Nota: Al poner ORDER BY 6 DESC si me hace el ordenamiento correcto pero la consulta es demasiada lenta, con decirles que si quito el ORDER BY 6 DESC y First 20 la consulta tarda 10 Segundos para traerme todos los registros y apenas son 5,000 Registros

Se podrá Crear un INDICE TEMPORAL PARA EL CAMPO CALCULADO TOTAL ????

Agradezco cualquier Ayuda.

[FOURIER]

Pues Yo Probaria

Código Delphi [-]
Select First 20 F.claveart,a.desart,a.marca,a.grupo,  Coalesce(sum(f.cantidad),0)-  (select Coalesce(sum(cantidad),0) From 
Tinv_invmovim   Where (tipomov='dm' Or Tipomov='dx') And   Claveart = F.claveart And   Fechamov Between '01.01.2006' And 
'24.03.2006') cantidad,  Coalesce(sum(f.impsiva),0)-  ((select Coalesce(sum(importe),0) From Tinv_invmovim    Where 
(tipomov='dm' Or Tipomov='dx') And    Claveart = F.claveart And    Fechamov Between '01.01.2006' And '24.03.2006')+   (select 
Case When Coalesce(sum(c.impartnc),0)>0 Then Coalesce(sum(c.impartnc),0)   Else 0 End From Tcxc_climovim C    Right Join 
Tfac_facturas Ff On (ff.tipomov = Substring(c.factura From 1 For 2) And Ff.foliofiscal= Cast(substring(c.factura From 3 For 6) As 
Integer))    Right Join Tfac_dfacturas Fd On (fd.tipomov = Ff.tipomov And Fd.folio = Ff.folio And Fd.claveart=f.claveart)    Where 
(tipomov='nm' Or Tipomov='nx') And    Fechamov Between '01.01.2006' And '24.03.2006')) total,   (select 
Coalesce(sum(impsiva),0) From Tfac_dfacturas    Where Claveart = F.claveart And Tipomov ='fm' And    Fechafac Between 
'01.01.2005' And '24.03.2005') -   ((select Coalesce(sum(importe),0) From Tinv_invmovim     Where (tipomov='dm' Or Tipomov='dx')
 And     Claveart = F.claveart And     Fechamov Between '01.01.2005' And '24.03.2005') +    (select Case When 
Coalesce(sum(c.impartnc),0)>0 Then Coalesce(sum(impartnc),0) Else 0 End From Tcxc_climovim C     Right Join Tfac_facturas Ff 
On (ff.tipomov = Substring(c.factura From 1 For 2) And Ff.foliofiscal = Cast(substring(c.factura From 3 For 6) As Integer))     Right
 Join Tfac_dfacturas Fd On (fd.tipomov = Ff.tipomov And Fd.folio = Ff.folio And Fd.claveart=f.claveart)     Where (tipomov='nm' Or 
Tipomov='nx') And     Fechamov Between '01.01.2005' And '24.03.2005')) totant,    (select Coalesce(sum(cantidad),0) From 
Tfac_dfacturas     Where Claveart = F.claveart And     Tipomov ='fm' And     Fechafac Between '01.01.2005' And '24.03.2005') -    
(select Coalesce(sum(cantidad),0) From Tinv_invmovim     Where (tipomov='dm' Or Tipomov='dx') And     Claveart = F.claveart And    
 Fechamov Between '01.01.2005' And '24.03.2005') cantantfrom Tfac_dfacturas Fjoin Tinv_articulos  A On (f.claveart = 
A.claveart)where F.fechafac Between '01.01.2006' And '24.03.2006' Andf.tipomov ='fm'group By 
F.claveart,a.desart,a.marca,a.grupoorder By   Coalesce(sum(f.cantidad),0)-  (select Coalesce(sum(cantidad),0) From 
Tinv_invmovim   Where (tipomov='dm' Or Tipomov='dx') And   Claveart = F.claveart And   Fechamov Between '01.01.2006' And 
'24.03.2006') cantidad,  Coalesce(sum(f.impsiva),0)-  ((select Coalesce(sum(importe),0) From Tinv_invmovim    Where 
(tipomov='dm' Or Tipomov='dx') And    Claveart = F.claveart And    Fechamov Between '01.01.2006' And '24.03.2006')+   (select 
Case When Coalesce(sum(c.impartnc),0)>0 Then Coalesce(sum(c.impartnc),0)   Else 0 End From Tcxc_climovim C    Right Join 
Tfac_facturas Ff On (ff.tipomov = Substring(c.factura From 1 For 2) And Ff.foliofiscal= Cast(substring(c.factura From 3 For 6) As 
Integer))    Right Join Tfac_dfacturas Fd On (fd.tipomov = Ff.tipomov And Fd.folio = Ff.folio And Fd.claveart=f.claveart)    Where 
(tipomov='nm' Or Tipomov='nx') And    Fechamov Between '01.01.2006' And '24.03.2006')) total,   (select 
Coalesce(sum(impsiva),0) From Tfac_dfacturas    Where Claveart = F.claveart And Tipomov ='fm' And    Fechafac Between 
'01.01.2005' And '24.03.2005') -   ((select Coalesce(sum(importe),0) From Tinv_invmovim     Where (tipomov='dm' Or Tipomov='dx')
 And     Claveart = F.claveart And     Fechamov Between '01.01.2005' And '24.03.2005') +    (select Case When 
Coalesce(sum(c.impartnc),0)>0 Then Coalesce(sum(impartnc),0) Else 0 End From Tcxc_climovim C     Right Join Tfac_facturas Ff 
On (ff.tipomov = Substring(c.factura From 1 For 2) And Ff.foliofiscal = Cast(substring(c.factura From 3 For 6) As Integer))     Right
 Join Tfac_dfacturas Fd On (fd.tipomov = Ff.tipomov And Fd.folio = Ff.folio And Fd.claveart=f.claveart)     Where (tipomov='nm' Or 
Tipomov='nx') And     Fechamov Between '01.01.2005' And '24.03.2005')) totant,    (select Coalesce(sum(cantidad),0) From 
Tfac_dfacturas     Where Claveart = F.claveart And     Tipomov ='fm' And     Fechafac Between '01.01.2005' And '24.03.2005') -    
(select Coalesce(sum(cantidad),0) From Tinv_invmovim     Where (tipomov='dm' Or Tipomov='dx') And     Claveart = F.claveart And    
 Fechamov Between '01.01.2005' And '24.03.2005') Desc

[FOURIER]

Código Delphi [-]
Select First 20 F.claveart,a.desart,a.marca,a.grupo, Coalesce(sum(f.cantidad),0)- (select Coalesce(sum(cantidad),0) From Tinv_invmovim Where (tipomov='dm' Or Tipomov='dx') And Claveart = F.claveart And Fechamov Between '01.01.2006' And '24.03.2006') cantidad, Coalesce(sum(f.impsiva),0)- ((select Coalesce(sum(importe),0) From Tinv_invmovim Where (tipomov='dm' Or Tipomov='dx') And Claveart = F.claveart And Fechamov Between '01.01.2006' And '24.03.2006')+ (select Case When Coalesce(sum(c.impartnc),0)>0 Then Coalesce(sum(c.impartnc),0) Else 0 End From Tcxc_climovim C Right Join Tfac_facturas Ff On (ff.tipomov = Substring(c.factura From 1 For 2) And Ff.foliofiscal= Cast(substring(c.factura From 3 For 6) As Integer)) Right Join Tfac_dfacturas Fd On (fd.tipomov = Ff.tipomov And Fd.folio = Ff.folio And Fd.claveart=f.claveart) Where (tipomov='nm' Or Tipomov='nx') And Fechamov Between '01.01.2006' And '24.03.2006')) total, (select Coalesce(sum(impsiva),0) From Tfac_dfacturas Where Claveart = F.claveart And Tipomov ='fm' And Fechafac Between '01.01.2005' And '24.03.2005') - ((select Coalesce(sum(importe),0) From Tinv_invmovim Where (tipomov='dm' Or Tipomov='dx') And Claveart = F.claveart And Fechamov Between '01.01.2005' And '24.03.2005') + (select Case When Coalesce(sum(c.impartnc),0)>0 Then Coalesce(sum(impartnc),0) Else 0 End From Tcxc_climovim C Right Join Tfac_facturas Ff On (ff.tipomov = Substring(c.factura From 1 For 2) And Ff.foliofiscal = Cast(substring(c.factura From 3 For 6) As Integer)) Right Join Tfac_dfacturas Fd On (fd.tipomov = Ff.tipomov And Fd.folio = Ff.folio And Fd.claveart=f.claveart) Where (tipomov='nm' Or Tipomov='nx') And Fechamov Between '01.01.2005' And '24.03.2005')) totant, (select Coalesce(sum(cantidad),0) From Tfac_dfacturas Where Claveart = F.claveart And Tipomov ='fm' And Fechafac Between '01.01.2005' And '24.03.2005') - (select Coalesce(sum(cantidad),0) From Tinv_invmovim Where (tipomov='dm' Or Tipomov='dx') And Claveart = F.claveart And Fechamov Between '01.01.2005' And '24.03.2005') cantantfrom Tfac_dfacturas Fjoin Tinv_articulos A On (f.claveart = A.claveart)where F.fechafac Between '01.01.2006' And '24.03.2006' Andf.tipomov ='fm'group By F.claveart,a.desart,a.marca,a.grupoorder By Coalesce(sum(f.cantidad),0)- (select Coalesce(sum(cantidad),0) From Tinv_invmovim Where (tipomov='dm' Or Tipomov='dx') And Claveart = F.claveart And Fechamov Between '01.01.2006' And '24.03.2006') cantidad, Coalesce(sum(f.impsiva),0)- ((select Coalesce(sum(importe),0) From Tinv_invmovim Where (tipomov='dm' Or Tipomov='dx') And Claveart = F.claveart And Fechamov Between '01.01.2006' And '24.03.2006')+ (select Case When Coalesce(sum(c.impartnc),0)>0 Then Coalesce(sum(c.impartnc),0) Else 0 End From Tcxc_climovim C Right Join Tfac_facturas Ff On (ff.tipomov = Substring(c.factura From 1 For 2) And Ff.foliofiscal= Cast(substring(c.factura From 3 For 6) As Integer)) Right Join Tfac_dfacturas Fd On (fd.tipomov = Ff.tipomov And Fd.folio = Ff.folio And Fd.claveart=f.claveart) Where (tipomov='nm' Or Tipomov='nx') And Fechamov Between '01.01.2006' And '24.03.2006')) total, (select Coalesce(sum(impsiva),0) From Tfac_dfacturas Where Claveart = F.claveart And Tipomov ='fm' And Fechafac Between '01.01.2005' And '24.03.2005') - ((select Coalesce(sum(importe),0) From Tinv_invmovim Where (tipomov='dm' Or Tipomov='dx') And Claveart = F.claveart And Fechamov Between '01.01.2005' And '24.03.2005') + (select Case When Coalesce(sum(c.impartnc),0)>0 Then Coalesce(sum(impartnc),0) Else 0 End From Tcxc_climovim C Right Join Tfac_facturas Ff On (ff.tipomov = Substring(c.factura From 1 For 2) And Ff.foliofiscal = Cast(substring(c.factura From 3 For 6) As Integer)) Right Join Tfac_dfacturas Fd On (fd.tipomov = Ff.tipomov And Fd.folio = Ff.folio And Fd.claveart=f.claveart) Where (tipomov='nm' Or Tipomov='nx') And Fechamov Between '01.01.2005' And '24.03.2005')) totant, (select Coalesce(sum(cantidad),0) From Tfac_dfacturas Where Claveart = F.claveart And Tipomov ='fm' And Fechafac Between '01.01.2005' And '24.03.2005') - (select Coalesce(sum(cantidad),0) From Tinv_invmovim Where (tipomov='dm' Or Tipomov='dx') And Claveart = F.claveart And Fechamov Between '01.01.2005' And '24.03.2005') Desc

[Sick boy]

Pues yo me he perdido !!!!

El primer mensaje se lee, pero los otros dos no hay quien los entienda.

Una pregunta, si usas FireBird, por que no pruebas los MDO?? Me parece que iran más rapido.

Se podrá Crear un INDICE TEMPORAL PARA EL CAMPO CALCULADO TOTAL ????

No lo creo, lo unico que se me ocurre es crear una vista que incluya el campo total y realizar un indice descendente sobre él.

Nota: Al poner ORDER BY 6 DESC si me hace el ordenamiento correcto pero la consulta es demasiada lenta, con decirles que si quito el ORDER BY 6 DESC y First 20 la consulta tarda 10 Segundos para traerme todos los registros y apenas son 5,000 Registros

El problema esta, casi seguro, en el DESC. IB/FB no es muy eficiente ordenando descendentemente a no ser que tengas un indice.

Prueba dejando el ORDER BY 6, y comprueba si la velocidad es suficiente.

Si el DESC es obligatorio en tu caso, lo unico que se me ocurre para ganar velocidad son vistas.

[AGAG4]

Gracias por su tiempo en elaborar sus respuestas, pero he intentado crear vistas, y no puedo como verán uso 3 tablas para determinar las Ventas Netas, donde los campos que estan dentro del Where son IGUALES como el TIPOMOV por lo que para una vista mandarle el VALOR a este campo a cada TABLA ignoro y no tengo ídea como hacerlo, aparte en las vistas no se pueden mandar parámetros como los SP, las vistas son producto de 1 ó varias tablas pero con diferentes campos, espero haberme explicado.

Gracias por su tiempo.