如何优化实体框架查询

entity-framework mysql performance

我正在使用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

一般承认的答案

是。重写LINQ查询。大多数LINQ to Entities查询可以用许多不同的方式编写,并且将以不同的方式转换为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我无法让你的查询工作,因为我收到一条错误消息,说明在调用SelectMany时Type Inference失败了。

但是我接受了你的建议,从使用连接转到了“olde style”前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!




许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因