如何优化实体框架查询 [英] How to optimize Entity Framework Queries

问题描述

我正在使用Linq-To-Entities进行查询，该查询仅返回947行，但运行需要18秒.我做了一个"ToTraceString"来获取底层的sql，并直接在数据库上运行相同的东西并获得相同的时间.

I am using Linq-To-Entities to do a query which is returning only 947 rows but taking 18 seconds to run. I have done a "ToTraceString" to get the underlying sql out and ran the same thing directly on the database and get the same timing.

尽管影响不大，但我已经使用了调优顾问并创建了两个索引.

I have used the tuning advisor and created a couple of indexes although with little impact.

看看查询执行计划，有几个嵌套循环占用了95％的时间，但是它们已经在索引上起作用了?

Looking at the query execution plan there are a couple of nested loops which are taking up 95% of the time but these are already working on the indexes?

有人对如何将一些优化强加到EF查询中有任何想法吗?

Does anyone have any ideas on how to force some optimisation into the EF query??

提供其他信息

针对这三个表的基本ER图如下:

A basic ER diagram with for the three tables is as follows:

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

我正在运行的linq旨在(使用P_ID)取回特定人员的所有事件:

The linq that I am running is designed to get all Events back for a particular Person (using the 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(深呼吸！):

Here is the generated SQL (deep breath!):

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或查询计划，所以我只能说这一切.

Yes. Rewrite the LINQ query. Most LINQ to Entities queries can be written in many different ways, and will be translated to SQL differently. Since you show neither the LINQ nor the SQL nor the query plan, that's about all I can say.

但是，您很聪明，可以尝试直接执行SQL.查询编译也可能会花费一些时间，但是您已经通过确定SQL占所有测量时间来排除了这一点.

You are smart, though, to try executing the SQL directly. Query compilation can also take time, but you've ruled that out by determining that the SQL accounts for all of the measured time.

尝试:

    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是一对一:

or if pe.Event is one to one:

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

    return query;

这篇关于如何优化实体框架查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！