CODEWITHSUNDEEP
entity-framework-5
Donald Jansen
Donald Jansen

Reputation: 1985

Entity Framework generated SQL is slow

The way Entity framework generates the SQL queries are slowing down the site or putting a lot of pressure on the cpu.

The code we currently have is

        var items = (from item in _DatabaseContext.ViewDashboardCounts
            where item.EmployeeID == employeeId && item.BranchID == branchId
            select item);

The query generated from this is 

SELECT  
   [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[C1] AS [C1], [Extent1].[C2] AS [C2],  
   [Extent1].[C3] AS [C3], [Extent1].[C4] AS [C4], [Extent1].[C5] AS [C5],  
   [Extent1].[C6] AS [C6], [Extent1].[C7] AS [C7], [Extent1].[C8] AS [C8],  
   [Extent1].[C9] AS [C9], [Extent1].[BranchID] AS [BranchID] 
FROM 
   (SELECT  
       [vDashboardCounts].[EmployeeID] AS [EmployeeID],  
       [vDashboardCounts].[BranchID] AS [BranchID], [vDashboardCounts].[C1] AS [C1],  
       [vDashboardCounts].[C2] AS [C2], [vDashboardCounts].[C3] AS [C3],  
       [vDashboardCounts].[C4] AS [C4], [vDashboardCounts].[C5] AS [C5],  
       [vDashboardCounts].[C6] AS [C6], [vDashboardCounts].[C7] AS [C7],  
       [vDashboardCounts].[C8] AS [C8], [vDashboardCounts].[C9] AS [C9] 
    FROM 
       [dbo].[vDashboardCounts] AS [vDashboardCounts]
) 
AS [Extent1] 
WHERE ([Extent1].[EmployeeID] = @p__linq__0) AND ([Extent1].[BranchID] = @p__linq__1)

However this runs for 2 seconds, which is very slow, however this is still faster than our previous query, but if I move the "where" to inside query ([dbo].[vDashboardCounts] AS [vDashboardCounts]) then the execution becomes instant

This is our first code

var items = (from item in _DatabaseContext.ViewDashboardCounts
            where item.EmployeeID == employeeId && item.BranchID == branchId
            select new List<int?>
            {
                item.C1,
                item.C2,
                item.C3,
                item.C4,
                item.C5,
                item.C6,
                count,
                item.C8,
                item.C9
            });

It took around 11 seconds to execute but I changed this code to

        var items = (from item in _DatabaseContext.ViewDashboardCounts
            where item.EmployeeID == employeeId && item.BranchID == branchId
            select item);
        return items.ToList().Select(item => new List<int?>
            {
                item.C1,
                item.C2,
                item.C3,
                item.C4,
                item.C5,
                item.C6,
                count,
                item.C8,
                item.C9
            }).FirstOrDefault();

What can I do to speed up the execution of the first piece of code because the view vDashboardCounts Doesn't even take a second to execute all the results

Upvotes: 1

Views: 64

Answers (1)

Vladimirs
Vladimirs

Reputation: 8599

You query looks pretty basic I don't think that you should tinker around that. First of all you can try to use AsNoTracking:

    _DatabaseContext.ViewDashboardCounts.AsNoTracking()
.Where(item => item.EmployeeID == employeeId && item.BranchID == branchId).ToList()

Then check if you have indexes for EmployeeID and/or BranchID. If still not happy with result may be worth to partition your database.

Upvotes: 1

Related Questions