I have a table with ~54,000,000 rows. I have the following query:
SELECT
tap1.[id],
tap1.[Mark],
tap1.[Date_Write]
FROM
[crpt].[dbo].[BI] tap1
JOIN
(SELECT mark
FROM [crpt].[dbo].[BI]
GROUP BY mark
HAVING COUNT(mark) = 2) t ON t.mark = tap1.mark
WHERE
date_write > '2023-09-26'
AND date_write < '2023-09-28'
ORDER BY
date_write, mark
It resolves (and with other older dates) in 3 seconds, but if change the dates to newest ones like 2023-10-01/2023-10-03 it hangs and I don't get a result. What can be wrong here?
2条答案
按热度按时间xt0899hw1#
You can try the following indexes. You need both of these indexes to cover your query properly.
You can also try rewriting this query to use window functions, which should be substantially more efficient given the right indexes.
You probably want the following index for this
nuypyhwy2#
Assuming you have the necessary indexes, it might be probably due to cached plan. Try executing it with a recompile (and I think in real code you are using variables rather then constants for parameters):