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
Accepted Answer
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;
Popular Answer
@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!
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
