In my sql server 2017 standard CU 20 there is a very often excuted query that has this awful condition:
AND F.progressive_invoice % @numberofservicesInstalled = @idService
Is there a mathematical way to put it in a more convenient way for sql server?
The query lasts from 240 to 500 ms Can you help me to do better? Please
What do you think is particularly awful here?
Is this query performing badly? Are you sure, that this condition is responsible?
This % is the modulo operator.
SELECT 13 % 5 => Remainder is 3
--This is roughly the same your code is doing:
DECLARE @Divisor INT=5; --Switch this value
DECLARE @CompareRemainder INT=3;
SELECT CASE WHEN 13 % @Divisor = @CompareRemainder THEN 'Remainder matches variable' ELSE 'no match' END;
Your line of code will tell the engine to compute a integer division of F.progressive_invoice and the variable @numberofservicesInstalled, then pick the remainder. This computation's result is compared to the variable @idService.
As this computation must be done for each value, an index will not help here...
I do not think, that this can be put more sargable.
UPDATE
In a comment you suggest, that it might help to change the code behind the equal operator. No this will not help.
I tried to think of a senseful meaning of this... Is the number of a service (or - as the variable suggests - its id) somehow hidden in the invoice number?
execution plan and row estimation:
The engine will see, that this must be computed for all rows. It would help to enforce any other filter before you have to do this. But you do not show enough. The line of code we see is just one part of a condition...
Indexes and statistics will surely play their roles too...