sql - EF Core with Angular - 在 SQL Profiler 中快速查询，在实践中慢

Question

我有一个不太大的数据库 - 6 个表，每个表最多 400 条记录，4 个连接。我的问题是在实践中查询很慢。过去需要 30 秒，它随机下降到 2 秒，然后又回来，等等。当读取几乎整个数据库时，它似乎停留在 2 秒。有人说我的电脑内存不足。所以我引入了每页 10 条记录的分页，它在前端仍然超过 2 秒，尽管 SQL Profiler 说它在 10-20 毫秒内完成。不包含 10 条记录保持 2 秒。

林克：

        var events = await eventRepository
            .GetAllWith()
            .Skip(page * 10)
            .Take(10)
            .Include(e => e.Sport)
            .Include(e => e.League)
            .Include(e => e.Markets)
                .ThenInclude(m => m.Results)
            .ToListAsync();

这是运行的查询：

exec sp_executesql N'SELECT [t].[Id], [t].[AwayTeam], [t].[Date], [t].[HomeTeam], [t].[LeagueId], [t].[Outcome], [t].[SportId], [s].[Id], [s].[Name], [l].[Id], [l].[Name], [l].[SportId], [t0].[Id], [t0].[EventId], [t0].[id0], [t0].[MarketId], [t0].[name], [t0].[odds], [t0].[sourceName]
FROM (
    SELECT [e].[Id], [e].[AwayTeam], [e].[Date], [e].[HomeTeam], [e].[LeagueId], [e].[Outcome], [e].[SportId]
    FROM [Event] AS [e]
    ORDER BY (SELECT 1)
    OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
) AS [t]
INNER JOIN [Sport] AS [s] ON [t].[SportId] = [s].[Id]
INNER JOIN [League] AS [l] ON [t].[LeagueId] = [l].[Id]
LEFT JOIN (
    SELECT [m].[Id], [m].[EventId], [r].[id] AS [id0], [r].[MarketId], [r].[name], [r].[odds], [r].[sourceName]
    FROM [Market] AS [m]
    LEFT JOIN [ResultEntity] AS [r] ON [m].[Id] = [r].[MarketId]
) AS [t0] ON [t].[Id] = [t0].[EventId]
ORDER BY [t].[Id], [s].[Id], [l].[Id], [t0].[Id], [t0].[id0]',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10

我试图将 nvarchars 更改为 varchars，消除可为空的相等性检查，但这并没有解决我的问题。我该如何解决？

标签： sqlsql-serverangularlinqef-core-3.1