How to improve select performance

[magpie]

I have to use firebird, but I don't get very well with database, I did the select as follows:

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 that it gets extremely slow. How I could improve this select so it worked faster and bringing me the same data.

[terrea]

There are a few ways, but it depends on the firebird version that you are running. I'll tell you 3, if you have problems implementing them, send me the DDL or the fdb proprio to recreate the bank on my machine and give you the ready and tested select.

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

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

2 - create stored procedures with one 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 drought 'Common Table Expressions (“WITH ... AS ... SELECT”)’
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-cte