Good afternoon.
I am trying to develop a solution within the ERP of the company where I work, in order to identify the possibilities of added FATURES whose TOTAL is equal to or approximate to a value deposited in account.
If I do the select with a smaller TOTAL, I find the possibilities very quickly. When doing it with a real situation, where the value paid is much higher than the one tested, select is running for hours without result.
Could anyone suggest another approach that is more efficient at runtime?
with
SomaFaturas (LastHANDLE, SEQ, SOMA) as
(select HANDLE as LastHANDLE, ''+CAST(HANDLE AS nvarchar(MAX)) AS SEQ,
CAST(VALOR AS real) AS SOMA
from k_testefaturas
UNION ALL
SELECT f.HANDLE AS LASTHANDLE, S.SEQ+'-'+CAST(HANDLE AS nvarchar(MAX)) AS SEQ, F.VALOR+S.SOMA AS SOMA
FROM k_testefaturas F
INNER JOIN SomaFaturas S
ON F.HANDLE > S.LASTHANDLE and F.VALOR+S.SOMA<74540.81+0.10)
SELECT SEQ, CAST(SOMA AS numeric(18,2)) AS SOMA FROM SomaFaturas
WHERE SOMA BETWEEN 74540.81-0.10 AND 74540.81+0.10
ORDER BY SEQ
The table k_testefatura, contains 76 invoices of several values. It is properly indexed in order to improve performance.
Hereisanexampleofthesameselect,butwithalowerTOTALtobefound:
