sql - 如何优化这样一个需要 17 秒的查询

Question

欢迎提供小费。我试图在 id_contrato 上创建一个表达式索引，但它只给了我 1 秒的速度。查询耗时 17 秒。我只需要一些中到小的改进。我正在使用火鸟 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)

标签： sqlfirebirddatabase-performance

Answer 1

许多项目可能有助于您的查询。对于索引，我将首先使用以下内容来帮助优化

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 )

您最后的 WHERE 子句包括“E”别名，它将您的 LEFT JOIN 转换为 INNER JOIN，所以我只是将“AND”子句移到了“E”连接部分。由于您的外部查询正在执行内部表结果的 SUM()，因此您不需要 'order by 1, 2' 子句，所以我删除了它。

你加入

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

很可能会给您重复/错误的答案，因为您正在比较承包商 ID 的左侧 8 个字符。因此，让我们看一下以下示例数据。请注意，每个 ID 都以“12345678”开头，表示从 1 到 8 相等的子字符串。如您所见，这会留下将导致错误连接结果的 ID 的其余部分。

CONTABIL_CONTRATO C
ID_CONTRATO
12345678A
12345678B
12345678C
12345678D


CONTABIL_EMPENHO E 
ID_CONTRATO
12345678E
12345678F
12345678G
12345678H

在没有看到实际数据的情况下，这将通过以下方式创建 16 次结果

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)

但是你也会有同样的

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

然后是逆

(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)

因此，对于每个实例，您都会一遍又一遍地重新查询相同的列子选择，以获得相同的重复数据，我认为这些重复数据与您想要的完全不准确。但没有看到实际数据，无法确认。

您可能想要的是 C.contractor ID = E.contractor ID。您的 FINAL where 条款明确将范围限制为像“00072017%”这样的承包商，这会将您的结果限制在那些有问题的承包商。因此，我将 JOIN 更改为相同的匹配承包商 ID。

但这被完全消除了，因为除了连接到 CONTABIL_EMPENHO 之外，您从未真正使用过您的 CONTABIL_CONTRATO 表。由于两个公共列都在两个表中，我只是将 where 子句更改为将“E”列引用为 ID_ORGAO 和 ID_CONTRATO 值。

对于您的每个 coalesce()，由于主 where 子句已经具有 E.TIPO_DESPESA = 'EMO'，因此内部列选择查询中不需要它。

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.