How to optimize Entity Framework Queries

Question

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.

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?

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

EDIT: Supplying additional information

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

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;

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

Answer 1

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.

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.

Try:

    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;

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;

Answer 2

Since 95% of the time is in the nested loops, eliminating them should solve the problem.

There are a few things you can look at:

• Are the nested loops necessary. If you wrote a query directly in SQL could you get the same result without using nested loops. If the answer to this is that it could be written without nested loops, what is it in the model or the linq query that is causing it.

• Is it an option for you to place some of the logic in views, thereby reducing the complexity of the linq query, and may be removing the need for the nested loops.

Normally I use the SQL server profiler to look at what SQL linq produces, I find this easier especially if you have two screens.

If you still have problems, post your linq query.

Answer 3

@Craig I couldn't get your query to work as I get an error message saying Type Inference failed in the call to SelectMany.

However I took your advice and went from using the join to an "olde style" pre ANSI type query:

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

Which produces fairly decent sql!