SQL Server query does not work with newest data

wb1gzix0  于 12个月前  发布在  SQL Server
关注(0)|答案(2)|浏览(147)

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?

xt0899hw

xt0899hw1#

You can try the following indexes. You need both of these indexes to cover your query properly.

BI (Mark)
BI (Date_Write) INCLUDE (Mark, ID)

You can also try rewriting this query to use window functions, which should be substantially more efficient given the right indexes.

SELECT  
    tap1.id,
    tap1.Mark,
    tap1.Date_Write
FROM (
    SELECT
      tap1.*,
      COUNT(*) OVER (PARTITION BY Mark) AS countPerMark
    FROM dbo.BI tap1
) tap1
WHERE
    tap1.Date_Write > '2023-09-26' 
    AND tap1.Date_Write < '2023-09-28'
    AND tap1.countPerMark = 2
ORDER BY
    date_write, mark;

You probably want the following index for this

BI (Mark) INCLUDE (Date_Write, ID)
nuypyhwy

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):

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> '20231001' and date_write< '20231003'
ORDER by date_write,mark
OPTION (RECOMPILE);

相关问题