选择count Distinct慢MSSQL

Question

您好，我这里有一个简单的查询，但它的速度像地狱一样慢，需要大约40秒才能恢复数据，我尝试了不同的SQL，但似乎仍然不能使其工作，任何建议都将不胜感激

select
  count (Distinct s.ExternalCustomerID) as PlayerCount,
  s.League  as Extra
from Q_Net_Ml_SportsDetailsActivity_monthly s with (nolock)
where  s.MerchantID = 584
  and s.WagerCount > 0
  and s.Year = 2021
group by League

计划：https://www.brentozar.com/pastetheplan/?id=HJ1bL0ivY

我尝试了一下: subquery

SELECT
    COUNT(ExternalCustomerID) AS Playercount,
    League AS extra
FROM (
    SELECT DISTINCT
      ExternalCustomerID,
      League
    FROM Q_Net_Ml_SportsDetailsActivity_monthly s with (nolock, INDEX(NCSI_Q_Net_Ml_SportsDetailsActivity_monthly))
    Where  s.MerchantID = 584
      and s.WagerCount > 0
      and s.Year = 2021
) dt
GROUP BY League

看起来仍然很慢的https://www.brentozar.com/pastetheplan/?id=HkAHjCswY

Answer 1

可以尝试将表'Q_Net_Ml_SportsDetailsActivity_monthly‘中的一些索引放在select语句中使用的列中。然后尝试再次运行查询。