How to improve select performance
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”)’