我使用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);
1条答案
按热度按时间myss37ts1#
您不需要将所有这些常量转换为大整数,日期常量也不需要时间组件。所以,我想你想要:
对于此查询,您需要索引:
message(threadid)
ThreadGroup(threadid, groupid)thread(started, ended, id, startedat)
这也许会有帮助。但我想你被困在一个完整的扫描表上thread()
最后用这个数据模型进行排序。所有的不平等都让这一切变得缓慢。它可能有助于增加什么似乎是多余的条件,以解决问题
where
,x.started < '2019-11-30'
,假设started
<ended
. 这将允许索引基于started
.