How to improve select performance

Question

How to improve select performance

Navigation

#1 by (1 votes)

0

I have to use firebird, but I do not get very well with the database, I made 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, the only problem is that it gets extremely slow. How could I improve this select so that it works faster and bringing me the same data.

node.js firebird

asked by anonymous 23.11.2017 / 17:13

1 answer

How do I automatically delete flash_data message after it is displayed in codeigniter Search by number in MySQL table with PHP [closed]

score 1 · Accepted Answer

There are a few forms, but it depends on which version of firebird you are running. I'll tell you 3, if you have problems implementing them, send me the DDL or the fdb itself to re-create the database on my machine and give you the select ready and tested. Home 1 - the inves of doing 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 the clause with before the select. See the following link in the Common Table Expressions section ("WITH ... AS ... SELECT") '
link