I cannot optimize the checkout for out-of-order billing
The invoice must be consistent in date and number. I am making this request to verify that I have no out of line accounts, but the performance is terrible (I have millions of accounts, so it takes a few minutes to check):
SELECT NUMBER, DATE
FROM BILLS
WHERE DATE BETWEEN @FROM AND @TO AND
EXISTS (SELECT *
FROM BILLS OUT_OF_ORDER
WHERE OUT_OF_ORDER.NUMBER < BILLS.NUMBER
AND OUT_OF_ORDER.DATE > BILLS.DATE)
I already have indexes on NUMBER, DATE and DATE + NUMBER, but the composite index cannot really be used in this query.
I've also tried removing the subquery with no discernible difference.
SELECT DISTINCT BILLS.NUMBER, BILLS.DATE
FROM BILLS
INNER JOIN BILLS OUT_OF_ORDER ON OUT_OF_ORDER.NUMBER < BILLS.NUMBER AND
OUT_OF_ORDER.DATE > BILLS.DATE
WHERE DATE BETWEEN @FROM AND @TO
You know how you can improve this kind of query (using> and lt operands on two different fields at the same time).
I've added an execution plan. Obviously the problem is with the validation of OUT_OF_ORDER accounts, it just uses a simple DATE index. As far as I know, you cannot define a composite index that allows you to search using> <operands on two different fields.
source to share
@Damien_The_Unbeliever made me remember that I don't have whitespace (or I shouldn't, which is checked on a second request). Therefore, I can simply check one previous account and not all of the previous accounts.
Checking the previous account uses the primary key because I am directly looking for the NUMBER - 1 account.
Now this request only takes a few seconds instead of a few minutes.
SELECT BILLS.NUMBER, BILLS.DATE
FROM BILLS
INNER JOIN BILLS OUT_OF_ORDER ON OUT_OF_ORDER.NUMBER = BILLS.NUMBER - 1 AND
OUT_OF_ORDER.DATE > BILLS.DATE
WHERE DATE BETWEEN @FROM AND @TO
Thanks everyone.
source to share