[firebase-br] Problemas com desempenho em query na versão 2.1

[Rodrigo Teodoro]

Douglas, não conhecia a CTE até você comentar.

Li a documentação e uma apresentação do Vlad Khorsun e implementei como 
abaixo. Porém teve um desempenho pior que a query modificada, gerando 8 
mil registros não indexados na tabela de PrecoProduto.

No caso, acho que migrar para o Firebird 2.1 agora não compensa, já que 
está tendo desempenho pior que o 2.0 com minha aplicação, já que 
atualizar os clientes, significa mandar a versão 2.1  do firebird e 
muitas querys na minha aplicação.

Achei CTE muito bacana, deixa a query mais organizada e resolve algumas
situações complicadas, inclusive a questão da recursividade.

Muito obrigado a todos pelas dicas!

Segue só por curiosidade a CTE que fiz.

WITH
     DESCONTOS (CODDESCONTO) AS
     (SELECT
         D.CODDESCONTO AS CODDESCONTO
     FROM
         DESCONTO D,
         FORMADECOMPRA F,
         PESQUISADESCONTO PD,
         CLIENTEFORMADECOMPRA CF
     WHERE
       D.CODDESCONTO = PD.CODDESCONTO AND
       PD.CODFORMADECOMPRA = F.CODFORMADECOMPRA                  AND
       (NOT F.CODFORMADECOMPRAFORNEC LIKE :I_CODFORMADECOMPRAFORNEC || 
'_%') AND
       CF.CODFORMADECOMPRA = F.CODFORMADECOMPRA AND
       CF.CODCLIENTE = :I_CODCLIENTE AND
       (
         (UPPER(F.DESCRICAO) CONTAINING 'AM') OR (UPPER(F.DESCRICAO) 
CONTAINING 'DIA')
       )
     ),
     DESC_ITEM (CODPRODUTO) AS
     (SELECT DISTINCT
         ID.CODPRODUTO  AS CODPRODUTO
     FROM
         DESCONTOS
             JOIN ITEMDESCONTO ID           ON  (DESCONTOS.CODDESCONTO = 
ID.CODDESCONTO AND
                                                     (ID.DESCONTO > 0 OR 
ID.DESCONTOFINANCEIRO > 0))
     ),
     DESC_LAB (CODLABORATORIO) AS
     (SELECT DISTINCT
         LD.CODLABORATORIO AS CODLABORATORIO
     FROM
        DESCONTOS
             JOIN LABORATORIODESCONTO LD    ON (DESCONTOS.CODDESCONTO = 
LD.CODDESCONTO AND
                                                    (LD.DESCONTO > 0 OR 
LD.DESCONTOFINANCEIRO > 0))
     ),
     DESC_GRUPO (GRUPOSUBSORTIMENTO) AS
     (SELECT DISTINCT
         GD.GRUPOSUBSORTIMENTO AS GRUPOSUBSORTIMENTO
     FROM
        DESCONTOS
             JOIN GRUPOSUBSORTIMENTODESCONTO GD ON 
(DESCONTOS.CODDESCONTO = GD.CODDESCONTO AND
                                                        (GD.DESCONTO > 0 
OR GD.DESCONTOFINANCEIRO > 0))
     ),
     PRODUTOS (CODPRODUTO) AS
     (SELECT
         P.CODPRODUTO
      FROM
         PRECOPRODUTO P
         LEFT JOIN
             DESC_ITEM ON (DESC_ITEM.CODPRODUTO = P.CODPRODUTO)
         LEFT JOIN
             DESC_LAB ON (DESC_LAB.CODLABORATORIO = P.CODLABORATORIO)
         LEFT JOIN
             DESC_GRUPO ON (DESC_GRUPO.GRUPOSUBSORTIMENTO = 
P.GRUPOSUBSORTIMENTO)
      WHERE
         NOT DESC_ITEM.CODPRODUTO            IS NULL OR
         NOT DESC_LAB.CODLABORATORIO         IS NULL OR
         NOT DESC_GRUPO.GRUPOSUBSORTIMENTO   IS NULL
     )

SELECT
     DISTINCT
     CODPRODUTO
FROM
     PRODUTOS
ORDER BY
     CODPRODUTO ASC



Plano
PLAN SORT (JOIN (JOIN (JOIN (PRODUTOS P NATURAL, SORT (JOIN (JOIN 
(PRODUTOS DESC_ITEM ID INDEX (FKPRODUTO_ITEMDESCONTO), JOIN (PRODUTOS 
DESC_ITEM DESCONTOS D INDEX (RDB$PRIMARY16), PRODUTOS DESC_ITEM 
DESCONTOS PD INDEX (FK_PESQUISADESCONTO_DESC))), JOIN (PRODUTOS 
DESC_ITEM DESCONTOS F INDEX (RDB$PRIMARY22), PRODUTOS DESC_ITEM 
DESCONTOS CF INDEX (FKCLIENTEFORMADECOMPRA_FORMCOM, 
FKCLIENTE_CLIENTEFORMADECOMPRA))))), SORT (JOIN (JOIN (PRODUTOS DESC_LAB 
LD INDEX (FKLABORATORIO_LABORATORIODESC), JOIN (PRODUTOS DESC_LAB 
DESCONTOS D INDEX (RDB$PRIMARY16), PRODUTOS DESC_LAB DESCONTOS PD INDEX 
(FK_PESQUISADESCONTO_DESC))), JOIN (PRODUTOS DESC_LAB DESCONTOS F INDEX 
(RDB$PRIMARY22), PRODUTOS DESC_LAB DESCONTOS CF INDEX 
(FKCLIENTEFORMADECOMPRA_FORMCOM, FKCLIENTE_CLIENTEFORMADECOMPRA))))), 
SORT (JOIN (JOIN (PRODUTOS DESC_GRUPO GD INDEX 
(GRPOSUBSORTIMENTO_GRUPO), JOIN (PRODUTOS DESC_GRUPO DESCONTOS D INDEX 
(RDB$PRIMARY16), PRODUTOS DESC_GRUPO DESCONTOS PD INDEX 
(FK_PESQUISADESCONTO_DESC))), JOIN (PRODUTOS DESC_GRUPO DESCONTOS F 
INDEX (RDB$PRIMARY22), PRODUTOS DESC_GRUPO DESCONTOS CF INDEX 
(FKCLIENTEFORMADECOMPRA_FORMCOM, FKCLIENTE_CLIENTEFORMADECOMPRA))))))

Plano Adaptado
PLAN SORT (JOIN (JOIN (JOIN (PRODUTOS P NATURAL, SORT (JOIN (JOIN 
(PRODUTOS DESC_ITEM ID INDEX (FKPRODUTO_ITEMDESCONTO), JOIN (PRODUTOS 
DESC_ITEM DESCONTOS D INDEX (INTEG_478), PRODUTOS DESC_ITEM DESCONTOS PD 
INDEX (FK_PESQUISADESCONTO_DESC))), JOIN (PRODUTOS DESC_ITEM DESCONTOS F 
INDEX (INTEG_484), PRODUTOS DESC_ITEM DESCONTOS CF INDEX 
(FKCLIENTEFORMADECOMPRA_FORMCOM, FKCLIENTE_CLIENTEFORMADECOMPRA))))), 
SORT (JOIN (JOIN (PRODUTOS DESC_LAB LD INDEX 
(FKLABORATORIO_LABORATORIODESC), JOIN (PRODUTOS DESC_LAB DESCONTOS D 
INDEX (INTEG_478), PRODUTOS DESC_LAB DESCONTOS PD INDEX 
(FK_PESQUISADESCONTO_DESC))), JOIN (PRODUTOS DESC_LAB DESCONTOS F INDEX 
(INTEG_484), PRODUTOS DESC_LAB DESCONTOS CF INDEX 
(FKCLIENTEFORMADECOMPRA_FORMCOM, FKCLIENTE_CLIENTEFORMADECOMPRA))))), 
SORT (JOIN (JOIN (PRODUTOS DESC_GRUPO GD INDEX 
(GRPOSUBSORTIMENTO_GRUPO), JOIN (PRODUTOS DESC_GRUPO DESCONTOS D INDEX 
(INTEG_478), PRODUTOS DESC_GRUPO DESCONTOS PD INDEX 
(FK_PESQUISADESCONTO_DESC))), JOIN (PRODUTOS DESC_GRUPO DESCONTOS F 
INDEX (INTEG_484), PRODUTOS DESC_GRUPO DESCONTOS CF INDEX 
(FKCLIENTEFORMADECOMPRA_FORMCOM, FKCLIENTE_CLIENTEFORMADECOMPRA))))))

------ Info. Desempenho ------
Tempo de Preparação = 0ms
Tempo de Execução = 3s 78ms
Tempo Médio Leitura = 87,94 ms
Memória Actual = 9.281.556
Memória Max = 11.727.604
Buffers de Memória = 2.048
Leituras do disco para a Memória Intermédia = 0
Escritas da Memória Intermédia para o disco = 0
Leituras da Memória Intermédia = 809.040