How to improve select performance


  • QA Engineer

    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.



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2