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
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.
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;
@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!