How to optimize Entity platform queries

Question

I use Linq-To-Entities to execute a query that returns only 947 rows but takes 18 seconds to run. I did "ToTraceString" to get the basic sql output and run the same thing directly in the database and get the same time.

I used a setup advisor and created a couple of indexes, albeit with a little impact.

In terms of query execution, there are several nested loops that take up 95% of the time, but are they already working on indexes?

Does anyone have any ideas on how to force some optimization in an EF request?

EDIT: providing additional information

The basic ER chart for the three tables is as follows:

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

The linq that I run is designed to return all events for a specific person (using 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 into SQL in different ways. Since you are not showing LINQ, SQL or query plan, that’s all I can say.

You are smart though, to try to execute SQL directly. Compiling queries may also take some time, but you solved this by setting that SQL accounts for the entire 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 nested loops, eliminating them should solve the problem.

There are a few things you can pay attention to:

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

• Is it possible to place some logic in the views, thereby reducing the complexity of the linq query and can eliminate the need for nested loops.

I usually use the SQL server profiler to see what SQL linq produces, I find it easier, especially if you have two screens.

If you still have problems send your linq request.

Answer 3

@Craig I could not get your request to work, as I get the error "Type input error" when calling SelectMany.

However, I took your advice and switched from using a connection to an ANSI type request of the olde style type:

  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 gives a pretty decent sql!