How to improve select performance

Question

I have to use Firebird, but I do not get along very well with database, I selected the following way:

select first 5 skip ((1 - 1) * 10) distinct
 PRO.CODIGO,
 PRO.NIVEL_INTERESSE,
 PRO.DATA_RETORNO,
 PRO.RESPONSAVEL_PROSPECT,
 PRO.EMPRESA_PROSPECT,
 PRO.CIDADE_IBGE,
 PRO.VENDEDOR,
 PRO.STATUS,
 PRO.TIPO_CONTATO,
 PRO.EMAIL,
 PRO.DATA_ALTERACAO,
 PRO.DATA_CADASTRO,
 VEN.NOME as NOME_VENDEDOR,
 PRO.TELEFONE,
 (select first 1
   CODIGO
   from CAD_PROSPECTOS_PROPOSTAS WHERE PROSPECTO = PRO.CODIGO
   order by CODIGO desc) as COD_PROPOSTA,
 (select first 1
   CODIGO
   from CAD_PROSPECTOS_AGENDAMENTOS
   where PROSPECTO = PRO.CODIGO
   order by CODIGO desc) as COD_AGENDAMENTO,
 (select first 1
   CODIGO
   from CAD_PROSPECTOS_CONTRATOS
   where PROSPECTO = PRO.CODIGO
   order by CODIGO desc) as COD_CONTRATO,
 (select first 1
   CONTRATO_CONFIRMADO
   from CAD_PROSPECTOS_CONTRATOS
   where PROSPECTO = PRO.CODIGO
   order by CODIGO desc) as CONTRATO_CONFIRMADO,
 (select first 1
   NOME
   from CAD_MUNICIPIOS
   where IBGE = PRO.CIDADE_IBGE) as NOME_MUNICIPIO
 from CAD_PROSPECTOS PRO
 left join CAD_TERCEIROS VEN on PRO.VENDEDOR = VEN.CODIGO and PRO.EMPRESA = 
 VEN.EMPRESA
 left join CAD_TERCEIROS_VENDEDORES CTV on PRO.CODIGO = VEN.CODIGO and 
 PRO.EMPRESA = VEN.CODIGO
 where PRO.EMPRESA = 1 and
       PRO.ESTABELEC = 1
 order by PRO.CODIGO desc  

It works perfectly and meets all my needs, only problem is it gets extremely slow. How could I improve this select so that it works faster and bringing me the same data.

Answer 1

There are some forms, but it depends on the version of the Firebird you are running. I’ll tell you 3, if you have problems implementing them, send me the DDL or the fdb itself to recreate the bank on my machine and give you the select ready and tested.

1 - the inves of making subselect in the select clause, use derived query:

select tabela1.campo2, sub1.campo, ....
from tabela1,
     ( select campo
        from subtabela s
       where .....
      ) as sub1
where ....

2 - create stored procedures with a for:

 create procedure select_exemplo.....
 //....
  for
     select ...
      from ...
  do begin
    select subcampo
      from substabela
    where .....
    into :subcampo-retorno;     
    suspend;
  end

3 - use clausa with before select. See the following link in the 'Common Table Expressions' section ("WITH ... AS ... SELECT")'
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-Cte