How can I optmize this query?

VictorLM 2015-12-18 20:23:53


I was tracing an application to see what query it was using to understand the business rules and build a DW in sequence.
After making some adjustments, I got to this query. But it is very slow throught the point that I´m running only for a month, one of 5 offices and one client only. It takes 32 seconds.

The execution plan show a 61% cost in a Sort step although I don´t do any order by. I believe it is because of the date period filter.

Can anyone help me? Thanks!

Anonymous User 2015-12-21 13:29:52
I think if you put a non-clusterd index on file_servico.cod_file

CREATE NONCLUSTERED INDEX IX_FileServico_CodFile ON file_servico (cod_file) INCLUDE (cod_servico, cod_file_servico, data_servico)

It should increase the performance, i think the main issue is here that you are filtering 3.4 million records from File_Servico table while only using 39 of them, with the new index it should produce a Clustered Index Seek operator instead of a Clustered Index Scan and probably reduces the starting set before sorting.