Linq statement runs really slow

Question

I have the following linq statement which runs quite slowly (I'm seeing 3 second times)

var results = 
    from pi in ProductItems.Include("Parent")
    join nt in PicklistDetails 
        on pi.Diameter.PicklistCode + "-" + pi.Schedule.PicklistCode 
        equals nt.PicklistCode
    where pi.Active 
    select new
    {
        Active = pi.Active,
        ID = pi.ID,
        IsCategory = pi.IsCategory,
        Name = pi.Name,
        Diameter = pi.Diameter.Value1,
        Thickness = nt.Value1,
        ThicknessCode = pi.Diameter.PicklistCode + "-" + pi.Schedule.PicklistCode
    };    
results.Dump();

I can write an equivalent statement directly in SQL and it runs much faster. Can someone tell me if there is a more efficient way to write the linq query?

UPDATE: Thanks for all the replies so far here is some more information. The generated sql from the above linq query comes out like so:

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Active] AS [Active], 
[Extent1].[IsCategory] AS [IsCategory], 
[Extent1].[Name] AS [Name], 
[Extent7].[Value1] AS [Value1], 
[Extent2].[Value1] AS [Value11], 
[Extent7].[PicklistCode] + N'-' + [Extent8].[PicklistCode] AS [C1]
FROM    [dbo].[ProductItem] AS [Extent1]
INNER JOIN [dbo].[PicklistDetails] AS [Extent2] ON  EXISTS (SELECT 
    1 AS [C1]
    FROM      ( SELECT 1 AS X ) AS [SingleRowTable1]
    LEFT OUTER JOIN  (SELECT 
        [Extent3].[ID] AS [ID], 
        [Extent3].[PicklistCode] AS [PicklistCode]
        FROM [dbo].[PicklistDetails] AS [Extent3]
        WHERE [Extent1].[DiameterID] = [Extent3].[ID] ) AS [Project1] ON 1 = 1
    LEFT OUTER JOIN  (SELECT 
        [Extent4].[ID] AS [ID], 
        [Extent4].[PicklistCode] AS [PicklistCode]
        FROM [dbo].[PicklistDetails] AS [Extent4]
        WHERE [Extent1].[ScheduleID] = [Extent4].[ID] ) AS [Project2] ON 1 = 1
    LEFT OUTER JOIN  (SELECT 
        [Extent5].[ID] AS [ID], 
        [Extent5].[PicklistCode] AS [PicklistCode]
        FROM [dbo].[PicklistDetails] AS [Extent5]
        WHERE [Extent1].[DiameterID] = [Extent5].[ID] ) AS [Project3] ON 1 = 1
    LEFT OUTER JOIN  (SELECT 
        [Extent6].[ID] AS [ID], 
        [Extent6].[PicklistCode] AS [PicklistCode]
        FROM [dbo].[PicklistDetails] AS [Extent6]
        WHERE [Extent1].[ScheduleID] = [Extent6].[ID] ) AS [Project4] ON 1 = 1
    WHERE (([Project1].[PicklistCode] + N'-' + [Project2].[PicklistCode]) = [Extent2].[PicklistCode]) OR (([Project3].[PicklistCode] + N'-' + [Project4].[PicklistCode] IS NULL) AND ([Extent2].[PicklistCode] IS NULL))
)
LEFT OUTER JOIN [dbo].[PicklistDetails] AS [Extent7] ON [Extent1].[DiameterID] = [Extent7].[ID]
LEFT OUTER JOIN [dbo].[PicklistDetails] AS [Extent8] ON [Extent1].[ScheduleID] = [Extent8].[ID]
WHERE [Extent1].[Active] = 1

I can get the same result in sql much faster using this query:

select pi.Active, pi.id, pi.IsCategory, pi.Name, diameter.Value1 as diameter, nt.Value1 as thickness, diameter.PicklistCode + '-' + schedule.PicklistCode as thicknesscode
from ProductItem pi
    inner join PicklistDetails diameter on diameter.id = pi.DiameterID
    inner join PicklistDetails schedule on schedule.id = pi.ScheduleID
    inner join PicklistDetails nt on nt.PicklistCode = diameter.PicklistCode + '-' + schedule.PicklistCode
where pi.Active = 1

I am aware that I can run this sql query directly but I would like to see if I can get the linq query to run faster. As I mentioned the linq query runs for about 3 secs and the sql query runs with sub 1 sec times.

I checked the execution plan and the two major items are as follows 41% Sort ([ProductItem].DiameterID Ascending, [ProductItem].ScheduleID Ascending, [PicklistDetails].PicklistCode Ascending) 49% Row Count Spool (Lazy Spool) I'm not entirely sure what this one is.

Answer 1

I would suspect the culprit here is your computed join value, particularly when pulling values from two separate tables (Diameter and Schedule). SQL can't take advantage of its indexes well with that structure. Check the execution plan for that join based on the generated SQL. Ultimately, my best suggestion would be to remove the query computation by denormalizing the computed picklist code and put it in the productitem table, but don't know your data model well enough to know if that makes sense.

Answer 2

Linq to entities can be really slow if you don't know to use it properly. Here are some tips that will really improve your performance:

1.) Avoid to put all the DB Objects into One Single Entity Model

2.) Disable change tracking for entity if not needed

3.) Use Pre-Generating Views to reduce response time for first request

4.) Avoid fetching all the fields if not required

5.) Choose appropriate Collection for data manipulation

6.) Use Compiled Query wherever needed

7.) Retrieve only required number of records

8.) Avoid using Contains

Of these point 2 and 5 are really important. After that you might also consider 8, 7 and rest. You can always check what your linq query is sending to database by using ToTraceString(). It will give you equivalent sql query, you can analyse and improve performance. You can refer below link for further details:

[link] http://www.dotnet-tricks.com/Tutorial/entityframework/J8bO140912-Tips-to-improve-Entity-Framework-Performance.html