选择计数Distinct Slow 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

我试过这个：子查询

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语句中使用的列中放置一些索引。然后尝试再次运行查询。