如何優化實體框架查詢

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合法嗎? 是的,了解原因