如何加速ef core生成的创建存储过程的请求

4urapxun  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(402)

我使用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);
myss37ts

myss37ts1#

您不需要将所有这些常量转换为大整数,日期常量也不需要时间组件。所以,我想你想要:

SELECT [x].[Id], [x].[Started] AS [StartAt],
       (SELECT Count(*)
        FROM [Message] [m]
        WHERE [x].[Id] = [m].[ThreadId]
       ) AS [MessageCount]
FROM [Thread] AS [x]
WHERE [x].[Started] >= '2019-11-10'AND
      [x].[Ended] < '2019-11-30' AND
      EXISTS (SELECT 1
              FROM ThreadGroup tg
              WHERE tg.[GroupId] IN ( . . . ) AND
                    [x].[Id] = tg.[ThreadId]
             )
ORDER BY [StartAt]

对于此查询,您需要索引: message(threadid) ThreadGroup(threadid, groupid) thread(started, ended, id, startedat) 这也许会有帮助。但我想你被困在一个完整的扫描表上 thread() 最后用这个数据模型进行排序。所有的不平等都让这一切变得缓慢。
它可能有助于增加什么似乎是多余的条件,以解决问题 where , x.started < '2019-11-30' ,假设 started < ended . 这将允许索引基于 started .

相关问题