How to optimize this query query with other subqueries?

Question

How to optimize this query query with other subqueries?

Navigation

#1 by (5 votes)

6

I have a query query that is taking too long to execute (almost 2min) and needs to be optimized:

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

Tables template :

ExecutionPlain:
Note:DoyouhaveanyotherwaytogetExecutionPlain?IntextformatIhadtroublepostinghere!

This query consists of grouping Employees with a given History by their Positions in each City . However, it needs to be filtered by:

Company ( where Funcionario.EmpresaId = @EmpresaId );

Status ( and Cidade.Uf = @Uf );

Cities that will be searched ( and Cidade.Id in ( {1} ) ) - this {1} is a string that will be changed by the list of Ids of desired cities

.

And there are also some conditions to be observed about the History of each Employee that will enter the count:

It is for a specific History Type ( and FH.TipoHistoricoId = @TipoHistoricoId );

The Employee will need to have the History Type informed on the Year you want to search for ( and year(FH.DataRegistro) = @AnoBase ); p>

So from the subquery that gets the Id of the last Employee history record with the History Type and the Base Year .

Lastly, and also the reason for the second subquery, is that the Employee can not have any of the other History Types that will be informed after the Type of History that is searched for in it. An Example: The Employee can not have the History Type "Transferred" after searching the History Type "Changed Position."

This is the whole question and I ask you what could be done to optimize query response time?

sql sql-server sql-server-2008

asked by anonymous 10.09.2014 / 15:58

1 answer

Is there any audio transcription API that can be used in PHP, C library or Java? How to implement the default presented in C # with EntityFramework?

score 5 · Accepted Answer

TL; DR

What can improve query performance:

Removal of% unnecessary%;
Use of indexed field in ORDER BY ;
Do not use functions in WHERE ( WHERE , year() ) for field comparisons.

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

Some actions that can optimize your query:

At line sorts does this subquery return only one result? Soon you can change the comparison to simply:
```
inner join FuncionarioHistorico on FuncionarioHistorico.Id = ( SubQuery )
```
The most external status filter in my view can be deleted. If you already have the list of cities, I assume the state has already been selected previously.
```
where Funcionario.EmpresaId = @EmpresaId
  and Cargo.Id <> @CargoId
  and Cidade.Id in ( {1} )
```
Using functions in inner join FuncionarioHistorico on FuncionarioHistorico.Id in ( SubQuery ) simply ignores field indexes. Try a different approach instead of WHERE
Verify that your table's indexes are in order. Ideally, all fields where you make joins have indexes.