skip to Main Content

A tip would be welcome. I tried to create an expression index on id_contrato but it gave me only 1s of speed. The query is taking 17s. I just need some medium to small improvement . I am using Firebird 2.0.

select sum(VL_EMPENHO) as VL_EMPENHO from (select distinct
 E.ID_EMPENHO || '/' || E.ID_EXERCICIO as NUM_EMPENHO,
 E.DATA,
    E.VALOR + coalesce((select SUM(E__.VALOR) from CONTABIL_EMPENHO E__ where E__.ID_EMPENHO = E.ID_EMPENHO and E__.NUMERO = E.NUMERO
     and E__.ID_ORGAO = E.ID_ORGAO and E__.ID_EXERCICIO = E.ID_EXERCICIO
        and (E.TIPO_DESPESA = 'EMO' and E__.TIPO_DESPESA = 'EOA')), 0) +
        coalesce((              select sum(V.VALOR)
             from CONTABIL_VARIACAO V
             LEFT JOIN CONTABIL_EVENTO ev on ev.ID_FICHA = v.ID_FICHA and ev.ID_EXERCICIO = v.ID_EXERCICIO
             LEFT JOIN CONTABIL_EVENTO_ITEM EI on EI.ID_EVENTO = ev.ID_EVENTO and EI.TIPO_EVENTO = ev.TIPO_EVENTO
             LEFT JOIN CONTABIL_PLANO_CONTA PD ON PD.ID_REGPLANO = EI.ID_DEBITO
             LEFT JOIN CONTABIL_PLANO_CONTA PC ON PC.ID_REGPLANO = EI.ID_CREDITO
             where ((PD.id_plano in ( '632910100', '631990000') or  PC.id_plano in ( '632910100', '631990000') )             or (PD.id_plano in ( '195920000', '195910000') or  PC.id_plano in ( '195920000', '195910000') ))
             AND V.ID_EMPENHO = E.ID_EMPENHO 
             and V.ANO = E.ID_EXERCICIO and V.ID_ORGAO = E.ID_ORGAO
         ), 0) as VL_EMPENHO,
(select first 1 P.DATA from CONTABIL_PAGAMENTO P
 inner join CONTABIL_EMPENHO E__ on E__.ID_REGEMPENHO = P.ID_REGEMPENHO
 where 
     (E.TIPO_DESPESA = 'EMO' and E__.TIPO_DESPESA in ('EMO', 'SEO', 'EMR', 'SER'))
    and E__.ID_EXERCICIO = E.ID_EXERCICIO and E__.ID_ORGAO = E.ID_ORGAO and 
    E__.ID_EMPENHO = E.ID_EMPENHO and P.ANULACAO = 'N' order by P.ID_PAGTO desc) as DT_PAGTO,
 (select sum(P.VALOR) from CONTABIL_PAGAMENTO P
 inner join CONTABIL_EMPENHO E__ on E__.ID_REGEMPENHO = P.ID_REGEMPENHO
 where 
     (E.TIPO_DESPESA = 'EMO' and E__.TIPO_DESPESA in ('EMO', 'SEO', 'EMR', 'SER'))
    and E__.ID_EXERCICIO = E.ID_EXERCICIO and E__.ID_ORGAO = E.ID_ORGAO and 
    E__.ID_EMPENHO = E.ID_EMPENHO) as VL_PAGO     
from CONTABIL_CONTRATO C
left join CONTABIL_EMPENHO E on substring(E.ID_CONTRATO from 1 for 8) = substring(C.ID_CONTRATO from 1 for 8) and
 E.ID_ORGAO = C.ID_ORGAO and E.TIPO_DESPESA in ('EMO')
where C.ID_ORGAO = '020000' and C.ID_CONTRATO like '00072017%' and E.ID_COMPRA <> 0 order by 1, 2)

2

Answers


  1. In outer query you are only using column VL_EMPENHO. So I have removed all the other columns from inner query. It should be executed faster now.

    select sum(VL_EMPENHO) as VL_EMPENHO from (select distinct  
        E.VALOR + coalesce((select SUM(E__.VALOR) from CONTABIL_EMPENHO E__ where E__.ID_EMPENHO = E.ID_EMPENHO and E__.NUMERO = E.NUMERO
         and E__.ID_ORGAO = E.ID_ORGAO and E__.ID_EXERCICIO = E.ID_EXERCICIO
            and (E.TIPO_DESPESA = 'EMO' and E__.TIPO_DESPESA = 'EOA')), 0) +
            coalesce((              select sum(V.VALOR)
                 from CONTABIL_VARIACAO V
                 LEFT JOIN CONTABIL_EVENTO ev on ev.ID_FICHA = v.ID_FICHA and ev.ID_EXERCICIO = v.ID_EXERCICIO
                 LEFT JOIN CONTABIL_EVENTO_ITEM EI on EI.ID_EVENTO = ev.ID_EVENTO and EI.TIPO_EVENTO = ev.TIPO_EVENTO
                 LEFT JOIN CONTABIL_PLANO_CONTA PD ON PD.ID_REGPLANO = EI.ID_DEBITO
                 LEFT JOIN CONTABIL_PLANO_CONTA PC ON PC.ID_REGPLANO = EI.ID_CREDITO
                 where ((PD.id_plano in ( '632910100', '631990000') or  PC.id_plano in ( '632910100', '631990000') )             or (PD.id_plano in ( '195920000', '195910000') or  PC.id_plano in ( '195920000', '195910000') ))
                 AND V.ID_EMPENHO = E.ID_EMPENHO 
                 and V.ANO = E.ID_EXERCICIO and V.ID_ORGAO = E.ID_ORGAO
             ), 0) as VL_EMPENHO
    from CONTABIL_CONTRATO C
    left join CONTABIL_EMPENHO E on substring(E.ID_CONTRATO from 1 for 8) = substring(C.ID_CONTRATO from 1 for 8) and
     E.ID_ORGAO = C.ID_ORGAO and E.TIPO_DESPESA in ('EMO')
    where C.ID_ORGAO = '020000' and C.ID_CONTRATO like '00072017%' and E.ID_COMPRA <> 0 order by 1, 2)
    
    Login or Signup to reply.
  2. Many items that may help your query. For indexes, I would start by having the following to help optimize

    table                index
    CONTABIL_EMPENHO     ( ID_ORGAO, TIPO_DESPESA, ID_CONTRATO, ID_EMPENHO, ID_EXERCICIO )
    CONTABIL_VARIACAO    ( ID_ORGAO, ID_EMPENHO, ANO )
    CONTABIL_PAGAMENTO   ( ID_REGEMPENHO )
    

    Your final WHERE clause includes the ‘E’ alias which turns your LEFT JOIN to an INNER JOIN, so I just moved the ‘AND’ clause to the ‘E’ join section. Since your outer query is doing a SUM() of the inner table results, you do not need the ‘order by 1, 2’ clause, so I removed that.

    You join between

        CONTABIL_CONTRATO C join CONTABIL_EMPENHO E 
                on    substring( E.ID_CONTRATO from 1 for 8) 
                    = substring( C.ID_CONTRATO from 1 for 8) 
    

    will most probably give you duplicate / false answers because you are comparing on the left 8 characters of the contractor id. So, lets take a look at the following sample data. Notice each of the IDs starts with "12345678" representing the substring from 1 for 8 of equality. This leaves the remainder of the ID that will cause false join results as you will see.

    CONTABIL_CONTRATO C
    ID_CONTRATO
    12345678A
    12345678B
    12345678C
    12345678D
    
    
    CONTABIL_EMPENHO E 
    ID_CONTRATO
    12345678E
    12345678F
    12345678G
    12345678H
    

    Without seeing actual data, this will create 16 times results via

    C.ID =12345678(A) joins to E.12345678(E), E.12345678(F), E.12345678(G) and E.12345678(H)
    C.ID =12345678(B) joins to E.12345678(E), E.12345678(F), E.12345678(G) and E.12345678(H)
    C.ID =12345678(C) joins to E.12345678(E), E.12345678(F), E.12345678(G) and E.12345678(H)
    C.ID =12345678(D) joins to E.12345678(E), E.12345678(F), E.12345678(G) and E.12345678(H)
    

    but then you’ll have the same with

    (B) joined to (E) (F) (G) (H)
    (C) joined to (E) (F) (G) (H)
    (D) joined to (E) (F) (G) (H)
    

    and then the inverse

    (E) joined to (A) (B) (C) (D)
    (F) joined to (A) (B) (C) (D)
    (G) joined to (A) (B) (C) (D)
    (H) joined to (A) (B) (C) (D)
    

    So, for each instance you keep re-querying the same columns sub-selects over and over to get the same repeats of data which I think is COMPLETELY in-accurate to what you want. But not seeing actual data, can not confirm.

    What you PROBABLY want is where the C.contractor ID = E.contractor ID. Your FINAL where clause that is explicitly limiting the scope to contractor like ‘00072017%’ would limit down your results to just those contractors in question. So, I changed the JOIN to be on same matching contractor ID.

    But that gets completely eliminated because you are never really using your CONTABIL_CONTRATO table except to do the join to the CONTABIL_EMPENHO. Since the two common columns are in both tables, I just changed the where clause to reference the ‘E’ columns to the ID_ORGAO and ID_CONTRATO values.

    With each of your coalesce(), since the main where clause already has E.TIPO_DESPESA = ‘EMO’, it is not needed in the inner column-select query.

    select 
            sum( PQ.VL_EMPENHO) VL_EMPENHO 
        from 
        (
        select distinct
                E.ID_EMPENHO || '/' || E.ID_EXERCICIO NUM_EMPENHO,
                E.DATA,
                E.VALOR 
        
                + coalesce( ( select SUM(E__.VALOR) 
                                from CONTABIL_EMPENHO E__ 
                                where 
                                        E.ID_ORGAO = E__.ID_ORGAO 
                                    and E__.TIPO_DESPESA = 'EOA'
                                    AND E.ID_EMPENHO = E__.ID_EMPENHO
                                    and E.NUMERO = E__.NUMERO
                                    and E.ID_EXERCICIO = E__.ID_EXERCICIO )
                            , 0) 
    
                + coalesce( ( select sum(V.VALOR)
                                from CONTABIL_VARIACAO V
                                    LEFT JOIN CONTABIL_EVENTO ev 
                                        on v.ID_FICHA = ev.ID_FICHA
                                        and v.ID_EXERCICIO = ev.ID_EXERCICIO
                                        LEFT JOIN CONTABIL_EVENTO_ITEM EI 
                                            on ev.ID_EVENTO = EI.ID_EVENTO 
                                            and ev.TIPO_EVENTO = EI.TIPO_EVENTO 
                                            LEFT JOIN CONTABIL_PLANO_CONTA PD
                                                ON EI.ID_DEBITO = PD.ID_REGPLANO
                                                LEFT JOIN CONTABIL_PLANO_CONTA PC 
                                                    ON EI.ID_CREDITO = PC.ID_REGPLANO
                                where 
                                        E.ID_ORGAO = V.ID_ORGAO
                                    and E.ID_EMPENHO = V.ID_EMPENHO 
                                    and E.ID_EXERCICIO = V.ANO 
                                    AND ( 
                                            (   PD.id_plano in ( '632910100', '631990000')
                                            or  PC.id_plano in ( '632910100', '631990000')
                                            )
                                        or 
                                            (   PD.id_plano in ( '195920000', '195910000')
                                            or  PC.id_plano in ( '195920000', '195910000')
                                            )
                                        )
                                )
                            , 0) as VL_EMPENHO,
                ( select first 1 
                        P.DATA 
                    from 
                        CONTABIL_EMPENHO E__ 
                            inner join CONTABIL_PAGAMENTO P
                                on E__.ID_REGEMPENHO = P.ID_REGEMPENHO 
                                AND P.ANULACAO = 'N' 
                    where
                            E.ID_ORGAO = E__.ID_ORGAO 
                        and E.ID_EMPENHO = E__.ID_EMPENHO
                        and E.ID_EXERCICIO = E__.ID_EXERCICIO 
                        and E__.TIPO_DESPESA in ('EMO', 'SEO', 'EMR', 'SER')
                    order by 
                        P.ID_PAGTO desc ) as DT_PAGTO,
                ( select 
                        sum(P.VALOR) 
                    from 
                        CONTABIL_EMPENHO E__ 
                            inner join CONTABIL_PAGAMENTO P
                                on E__.ID_REGEMPENHO = P.ID_REGEMPENHO 
                    where
                            E.ID_ORGAO = E__.ID_ORGAO
                        and E.ID_EMPENHO = E__.ID_EMPENHO 
                        and E.ID_EXERCICIO = E__.ID_EXERCICIO  
                        and E__.TIPO_DESPESA in ('EMO', 'SEO', 'EMR', 'SER') ) as VL_PAGO
            from 
                CONTABIL_EMPENHO E 
            where 
                    E.ID_ORGAO = '020000' 
                and E.ID_CONTRATO like '00072017%'
                and E.TIPO_DESPESA in 'EMO'
                and E.ID_COMPRA <> 0  ) PQ
    

    I believe I am accurate in my assessment of your query needs. That and the indexes will perform significantly better.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search