Bills must be consecutive by date and number. I'm doing this query to check that I don't have out-of-order bills, but the performance is awful (I have millions of bills, so it takes minutes to check a month) :
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 by NUMBER, DATE and DATE + NUMBER, but the compound index can't really be used in that query.
I have also tried removing the subquery with no noticeable 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
Do you know how this kind of queries (using the >< operands on two different fields at the same time) can be improved ?.
I have added the execution plan. The problem obviously is on checking the OUT_OF_ORDER bills, it just uses the plain DATE index. As far as I know you can't define a compound index that allows you to search with >< operands on two different fields.
