How to optimize this query query with other sub-consultations?

Question

I have a query query that is taking too long to run (almost 2min) and need to optimize it:

select Cidade.Id as CidadeId, Cidade.Nome as CidadeNome, 
       Cargo.Descricao as CargoDescricao, '{0}' as Grupo, Count(*) as Qtd
from Funcionario
inner join Cargo on Cargo.Id = Funcionario.CargoId
inner join Bairro on Bairro.Id = Funcionario.BairroId
inner join Cidade on Cidade.Id = Bairro.CidadeId
inner join FuncionarioHistorico on FuncionarioHistorico.Id in (
    select Top 1 FuncionarioHistorico.Id from FuncionarioHistorico FH
    where FH.FuncionarioId = Funcionario.Id
      and FH.EmpresaId = Funcionario.EmpresaId
      and year(FH.DataRegistro) = @AnoBase
      and FH.TipoHistoricoId = @TipoHistoricoId
      and FH.Id not in ( select NP.Id from FuncionarioHistorico NP
                         where NP.FuncionarioId = Funcionario.Id
                           and NP.EmpresaId = Funcionario.EmpresaId
                           and NP.DataRegistro >= FH.DataRegistro
                           and year(NP.DataRegistro) = year(FH.DataRegistro)
                           and NP.TipoHistoricoId in ({2}) )
    order by FH.DataRegistro desc )
inner join TipoHistorico on TipoHistorico.Id = FuncionarioHistorico.TipoHistoricoId
where Funcionario.EmpresaId = @EmpresaId
  and Cargo.Id <> @CargoId
  and Cidade.Id in ( {1} )
  and Cidade.Uf = @Uf
group by Cidade.Id, Cidade.Nome, Cargo.Descricao
order by Cidade.Nome

Table model:

Execution Plain:
Note: Is there any other way to get the Execution Plain? In text format I had problems posting here!

Such consultation shall consist of grouping the amount of Employees, who own a particular Historic, for their Posts in each City. However, it needs to be filtered by:

1. Enterprise (where Funcionario.EmpresaId = @EmpresaId);
2. State (and Cidade.Uf = @Uf);
3. Cities to be researched (and Cidade.Id in ( {1} )) - that {1} is a string that will be changed by the list of Ids of the desired cities.

And there are also some conditions to be observed about the Historic of each Official that will enter the countdown:

1. It’s for a Type of History specific (and FH.TipoHistoricoId = @TipoHistoricoId);

2. The Official will need to have the Type of History informed on Year you want to perform the search (and year(FH.DataRegistro) = @AnoBase);

   • That’s why the subconsultation that gets the Id of the last history record of the Official with the Type of History desired and the Base Year.

3. Finally, and also the motive for the second sub-concession, is that the Employee cannot have any of the other Types of History that will be informed after the Type of History that is sought in it. An Example: The Employee may not have the Type of History "Transferred" after searching the Type of History "Office Altered".

That is the whole question and I ask you what could be done to optimize the response time of the consultation?

Answer 1

TL;DR

What can improve query performance:

• Removal of ORDER BY unnecessary;
• Use of indexed field on filters in the WHERE;
• Do not use functions in WHERE (year(), month()) for field comparisons.

---

According to Execution Plan, the biggest villains in your query are the sorts. Only they are responsible for 40% of the execution time of your query.

Some actions that can optimize your query:

• On the line inner join FuncionarioHistorico on FuncionarioHistorico.Id in ( SubQuery ) this subquery returns only one result? Then you can change the comparison to simply:

  inner join FuncionarioHistorico on FuncionarioHistorico.Id = ( SubQuery )
  

• The most external state filter in my view can be eliminated. If you already have the list of cities, please note that the state has already been selected previously.

  where Funcionario.EmpresaId = @EmpresaId
    and Cargo.Id <> @CargoId
    and Cidade.Id in ( {1} )
  

• The use of functions in the WHERE simply ignore field indexes. Try a different approach instead of year(FH.DataRegistro) = @AnoBase

• Check that the contents of your table are in order. Ideally all fields where you make the joins have index.