如何优化Entity Framework查询

Question

我正在使用Linq-To-Entities进行查询，返回的结果只有947行，但运行时间达到了18秒。我已经使用"ToTraceString"获取底层SQL，并在数据库中直接运行相同的查询，时间也一样长。

我已经使用调优顾问并创建了一些索引，但影响很小。

查看查询执行计划，发现有几个嵌套循环占用了95%以上的时间，但这些循环已经在索引上运行了？

是否有任何想法来强制优化EF查询？？

编辑：提供附加信息

三张表的基本ER图如下：

People >----People_Event_Link ----< Events
P_ID        P_ID                    E_ID
            E_ID

我正在运行的LINQ查询旨在获取特定人员（使用P_ID）的所有事件：

        var query = from ev in genesisContext.Events
                    join pe in genesisContext.People_Event_Link
                    on ev equals pe.Event
                    where pe.P_ID == key
                    select ev;
        return query;

以下是生成的SQL语句（深呼吸！）：

SELECT 
1 AS [C1], 
[Extent1].[E_ID] AS [E_ID], 
[Extent1].[E_START_DATE] AS [E_START_DATE], 
[Extent1].[E_END_DATE] AS [E_END_DATE], 
[Extent1].[E_COMMENTS] AS [E_COMMENTS], 
[Extent1].[E_DATE_ADDED] AS [E_DATE_ADDED], 
[Extent1].[E_RECORDED_BY] AS [E_RECORDED_BY], 
[Extent1].[E_DATE_UPDATED] AS [E_DATE_UPDATED], 
[Extent1].[E_UPDATED_BY] AS [E_UPDATED_BY], 
[Extent1].[ET_ID] AS [ET_ID], 
[Extent1].[L_ID] AS [L_ID]
FROM  [dbo].[Events] AS [Extent1]
INNER JOIN [dbo].[People_Event_Link] AS [Extent2] ON  EXISTS (SELECT 
    1 AS [C1]
    FROM    ( SELECT 1 AS X ) AS [SingleRowTable1]
    LEFT OUTER JOIN  (SELECT 
        [Extent3].[E_ID] AS [E_ID]
        FROM [dbo].[Events] AS [Extent3]
        WHERE [Extent2].[E_ID] = [Extent3].[E_ID] ) AS [Project1] ON 1 = 1
    LEFT OUTER JOIN  (SELECT 
        [Extent4].[E_ID] AS [E_ID]
        FROM [dbo].[Events] AS [Extent4]
        WHERE [Extent2].[E_ID] = [Extent4].[E_ID] ) AS [Project2] ON 1 = 1
    WHERE ([Extent1].[E_ID] = [Project1].[E_ID]) OR (([Extent1].[E_ID] IS NULL) AND ([Project2].[E_ID] IS NULL))
)
WHERE [Extent2].[P_ID] = 291

- Calanus

---

我刚刚自己编写了SQL查询语句，运行时间不到一秒钟，哎呀呀...SELECT * FROM Events AS E INNER JOIN People_Event_Link AS PE ON E.E_ID=PE.E_ID INNER JOIN PEOPLE AS P ON P.P_ID=PE.P_ID WHERE P.P_ID = 291 - Calanus

Answer 1

是的。重新编写LINQ查询。大多数LINQ到实体查询可以用许多不同的方式编写，并将被翻译成不同的SQL语句。由于您既未显示LINQ，也未显示SQL或查询计划，因此这就是我能说的全部。

但是，您很聪明，尝试直接执行SQL。查询编译也可能需要时间，但通过确定SQL占用了所有测量时间，您已经排除了这种情况。

请尝试：

    var query = from pe in genesisContext.People_Event_Link
                where pe.P_ID == key
                from ev in pe.Event // presuming one to many
                select ev;

或者如果pe.Event是一对一的：

    var query = from pe in genesisContext.People_Event_Link
                where pe.P_ID == key
                select pe.Event;

    return query;

- Craig Stuntz

---

我已经查看了linq查询，但说实话，要更改的内容并不是很多（请参见上文）！ - Calanus

在LINQ to Entities中，几乎从不正确使用join。Event上的属性名称是什么，用于People_Event_Link（换句话说，pe.Event的另一端）？我会在我的答案中猜一个；如果我错了，请纠正我。 - Craig Stuntz

Answer 2

@Craig 我无法使您的查询正常工作，因为出现了一个错误消息，提示在 SelectMany 的调用中 Type Inference 失败。

但是我采纳了您的建议，从使用 join 转向使用“老式”预 ANSI 类型查询：

        var query = from pe in genesisContext.People_Event_Link
                    from ev in genesisContext.Events
                    where pe.P_ID == key && pe.Event == ev
                    select ev;

这会生成相当不错的SQL语句！

- Calanus

---

“Type Inference failed in the call to SelectMany” 意味着我猜错了属性名称。但是你有一个大体的想法——尝试不同的 LINQ 可以获得不同的 SQL。 - Craig Stuntz

Answer 3

由于95%的时间都在嵌套循环中，消除它们应该可以解决问题。

有几件事情你可以考虑：

• 嵌套循环是否必要。如果你直接在SQL中编写查询，是否可以不使用嵌套循环获得相同的结果。如果答案是可以不使用嵌套循环编写，那么模型或linq查询中的什么导致了这种情况。

• 是否可以将一些逻辑放在视图中，从而降低linq查询的复杂性，并可能消除嵌套循环的需要。

通常我使用SQL服务器分析器来查看linq生成的SQL，我发现这样更容易，特别是如果你有两个屏幕。

如果你仍然有问题，请发布你的linq查询。

- Shiraz Bhaiji