slow query performance issue with partition and max

Question

this a poor performancing query I have ... what have I done so wrong? Please help me it is executed tons of times in my system, solving that will give me a ladder to heaven

I gave a check on the system with sp_Blitz and no mortal issues found

Here is the query :

SELECT MAX(F.id) OVER (PARTITION BY idstato ORDER BY F.id DESC) AS id
FROM jfel_tagxml_invoicedigi F
     INNER JOIN jfel_invoice_state S ON F.id = S.idinvoice
WHERE S.idstato = @idstato
  AND S.id = F.idstatocorrente
  AND F.sequence_invoice % @number_service_installed = @idServizio
ORDER BY F.id DESC,
         F.idstatocorrente OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

Here is the query plan

https://www.brentozar.com/pastetheplan/?id=SyYL5JOeE

I can send you privately my system properties

update: Made some modification , it is better , but I think it could be better ... here is the new query :

SELECT MAX(F.id) AS id
FROM jfel_tagxml_invoicedigi F
     INNER JOIN jfel_invoice_state S ON F.id = S.idinvoice
WHERE S.idstato = @idstato
  AND S.id = F.idstatocorrente
  AND F.sequence_invoice % @number_service_installed = @idServizio;

And the new plan: https://www.brentozar.com/pastetheplan/?id=SJ-5GDqeE

update: Made some modification , it is better , but I think it could be better ... here is the new query :

SELECT top 1 F.id as id 
FROM jfel_tagxml_invoicedigi AS F 
INNER JOIN jfel_invoice_state AS S 
ON F.idstatocorrente = S.id
WHERE S.idstato= 1 AND S.id = F.idstatocorrente 
and S.datastato > dateadd(DAY,-5,getdate())
AND F.progressivo_fattura % 1 = 0
ORDER BY S.datastato

And the new new plan https://www.brentozar.com/pastetheplan/?id=S1xRkL51S

Answer 1

Filtering by calculated fields used to affect performance negatively. You can do your other filters first, and as a last step do the calculated filter, to have less rows to match. Maybe it will fill TEMPDB because it will store the intermediate recordset there, but in this case you either increase the size of it, or use another method.
Here is your second query written like this (maybe you need to adjust it, I just wrote it in Notepad++:

SELECT MAX(id) AS id
FROM (
    SELECT F.id, F.sequence_invoice % @number_service_installed as [idServizio]
    FROM jfel_tagxml_invoicedigi F
         INNER JOIN jfel_invoice_state S ON F.id = S.idinvoice
    WHERE S.idstato = @idstato
        AND S.id = F.idstatocorrente
        -- AND F.sequence_invoice % @number_service_installed = @idServizio
)
WHERE idServizio = @idServizio
;

Instead of the subquery, you can try a temp table or CTE as well, maybe one is the clear winner above the others, worth a try for all if you want maximum performance.

Answer 2

The data calculation is Non-Sargable, you could try using a variable with OPTION RECOMPILE:

DECLARE @d Date
SET @d = dateadd(DAY,-5,getdate())

SELECT top 1 F.id as id 
FROM jfel_tagxml_invoicedigi AS F 
INNER JOIN jfel_invoice_state AS S 
ON F.idstatocorrente = S.id
WHERE S.idstato= 1 AND S.id = F.idstatocorrente 
and S.datastato > @d
AND F.progressivo_fattura % 1 = 0
ORDER BY S.datastato
OPTION (RECOMPILE)