How can I optmize this query?
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!
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.