实体框架:在添加表之后，什么会导致查询变慢

本文关键字：查询 什么 框架 添加 之后 实体 | 更新日期: 2023-09-27 18:19:13

我有一个连接到SQLite数据库的实体框架5项目。我使用了模型第一种方法，我能够在大约3秒内从Table_A查询30,000条记录。

现在我所做的是另一个Table_B，它有0到1个引用来自Table_A的父记录。在Table_A上运行相同的查询需要3分钟以上。表b中没有记录

还值得注意的是，EDMX为Table_A和Table_B添加了导航属性。但是，它只向Table_B添加了外键列。什么会导致实体框架慢了这么多?当我将更改恢复到旧模型时，它运行得很快。

作为参考，该查询是一个标准的linq to sql查询。

var matches = Table_A.Where(it => it.UserName == "Waldo" || it.TimeStamp < oneMonthAgo);

实体框架:在添加表之后，什么会导致查询变慢

我刚刚运行了ToTraceString()来查找生成的SQL查询，这家伙在他的回答中建议:

事实证明实体框架试图"聪明"地预测我会使用子记录中的数据。这真的很酷!只是减慢了我的查询，所以我可能会找到一个更快的解决方案。

请注意，这个查询在LINQ语法中是相同的。这只是在向EDMX图中添加另一个Table后生成的底层SQL。

下面是FAST查询:(为清晰起见缩写)

SELECT *
FROM   [Table_A] AS [Extent1]
INNER JOIN  (SELECT 
[Extent2].[OID] AS [K1], 
[Extent2].[C_Column1] AS [K2], 
Max([Extent2].[Id]) AS [A1]
FROM [Table_A] AS [Extent2]
GROUP BY [Extent2].[OID], [Extent2].[C_Column1] ) AS [GroupBy1] ON [Extent1].[Id] = 
[GroupBy1].[A1]
INNER JOIN [OtherExistingTable] AS [Extent3] ON [Extent1].[C_Column1] = [Extent3].[Id]

在添加了Table_B之后，生成了新的查询，这使得事情变得慢了很多。

SELECT *
FROM    [Table_A] AS [Extent1]
LEFT OUTER JOIN [Table_B] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Table_B_ForeignKey_To_Table_A]
INNER JOIN  (SELECT 
    [Join2].[K1] AS [K1], 
    [Join2].[K2] AS [K2], 
    Max([Join2].[A1]) AS [A1]
    FROM ( SELECT 
        [Extent3].[OID] AS [K1], 
        [Extent3].[C_Column1] AS [K2], 
        [Extent3].[Id] AS [A1]          
        FROM  [Table_A] AS [Extent3]            
        LEFT OUTER JOIN [Table_B] AS [Extent4] ON [Extent3].[Id] = [Extent4].[Table_B_ForeignKey_To_Table_A]
    )  AS [Join2]
    GROUP BY [K1], [K2] ) AS [GroupBy1] ON [Extent1].[Id] = [GroupBy1].[A1]
INNER JOIN [FeatureServices] AS [Extent5] ON [Extent1].[C_Column1] = [Extent5].[Id]
相关文章：