我使用ef core，它生成如下查询：

SELECT [x].[Id],[x].[Started] AS [StartAt],(SELECT Count(*)
FROM [Message] AS [m]
WHERE [x].[Id] = [m].[ThreadId]) AS [MessageCount]
FROM [Thread] AS [x]
WHERE ( ( [x].[Started] >= '2019-11-10 00:00:00.000'/* @__startAt_0 */ )
AND ( [x].[Ended] <= '2019-11-30 00:00:00.000'/* @__endAt_1 */ ) )
AND EXISTS (SELECT 1
FROM [ThreadGroup] AS [b]
WHERE [b].[GroupId] IN ( Cast(1 AS BIGINT), Cast(2 AS BIGINT), Cast(3 AS BIGINT), Cast(4 AS BIGINT),
Cast(5 AS BIGINT), Cast(6 AS BIGINT), Cast(7 AS BIGINT), Cast(8 AS BIGINT),
Cast(9 AS BIGINT), Cast(10 AS BIGINT), Cast(11 AS BIGINT), Cast(12 AS BIGINT),
Cast(13 AS BIGINT), Cast(14 AS BIGINT), Cast(15 AS BIGINT), Cast(16 AS BIGINT),
Cast(18 AS BIGINT), Cast(19 AS BIGINT), Cast(20 AS BIGINT), Cast(21 AS BIGINT),
Cast(22 AS BIGINT), Cast(23 AS BIGINT), Cast(24 AS BIGINT), Cast(25 AS BIGINT),
Cast(26 AS BIGINT), Cast(27 AS BIGINT), Cast(28 AS BIGINT), Cast(30 AS BIGINT),
Cast(31 AS BIGINT), Cast(32 AS BIGINT), Cast(34 AS BIGINT), Cast(49 AS BIGINT) )
AND ( [x].[Id] = [b].[ThreadId] ))
ORDER BY [StartAt]
OFFSET 0 /* @__p_3 */ ROWS FETCH NEXT 20 /* @__p_4 */ ROWS ONLY

但是它的执行速度并不能让我满意，我正在寻找一种方法来加速一个类似的请求，我决定通过存储过程来加速它，但结果它没有帮助，因为问题出在“select”本身，我试图重写它：

SELECT Thread.Id, c.MessageCount  FROM Thread
        JOIN (select ThreadId from ThreadGroup where GroupId IN ( Cast(1 AS BIGINT), Cast(2 AS BIGINT),
Cast(3 AS BIGINT), Cast(4 AS BIGINT),
          Cast(5 AS BIGINT), Cast(6 AS BIGINT), Cast(7 AS BIGINT), Cast(8 AS BIGINT),
          Cast(9 AS BIGINT), Cast(10 AS BIGINT), Cast(11 AS BIGINT), Cast(12 AS BIGINT),
          Cast(13 AS BIGINT), Cast(14 AS BIGINT), Cast(15 AS BIGINT), Cast(16 AS BIGINT),
          Cast(18 AS BIGINT), Cast(19 AS BIGINT), Cast(20 AS BIGINT), Cast(21 AS BIGINT),
          Cast(22 AS BIGINT), Cast(23 AS BIGINT), Cast(24 AS BIGINT), Cast(25 AS BIGINT),
          Cast(26 AS BIGINT), Cast(27 AS BIGINT), Cast(28 AS BIGINT), Cast(30 AS BIGINT),
          Cast(31 AS BIGINT), Cast(32 AS BIGINT), Cast(34 AS BIGINT), Cast(49 AS BIGINT) )) as ThreadGroup
        ON Thread.Id = threadGroup.ThreadId
        JOIN (Select Message.ThreadId, Count(Message.ThreadId) as MessageCount from Message GROUP BY ThreadId) Mess ON Mess.ThreadId = Thread.Id
      WHERE  Thread.[Started] >= '2019-11-10 00:00:00.000' AND  Thread.[Ended] <= '2019-11-30 00:00:00.000'
      Order By Thread.Started
      OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

但我不能加快速度，而是放慢了速度。也许有人知道如何加速类似的请求？我的ef核心查询：

var threads = _context.Threads
                .Include(x => x.ThreadGroups)
                .Include(x => x.Messages)
                .AsNoTracking()
                .Where(x => x.Started >= startAt && x.Ended <= endAt && x.ThreadGroups.Where(b => groupIds.Contains(b.GroupId)).Any())
                .GroupBy(x => x.Started);