Optimizing SQL query in Entity Framework for performance improvements

Question

I have a query in Entity Framework which looks like this:

var dbModels = context.BusinessRuleExceptions
                    .Where(b => b.ResponseDateTime >= businessRuleSearchParameters.FromDate
                                && b.ResponseDateTime <= businessRuleSearchParameters.ToDate
                    )
                    .GroupBy(x => new {x.BusinessRuleName, x.GenNextId})
                    .Select(p => p.OrderByDescending(pk => pk.ResponseDateTime).Take(1))
                    .SelectMany(e => e).ToList();

When I capture this query in SQL Server Profiler, it is converted into:

SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[OriginalCorrelationId] AS [OriginalCorrelationId], 
    [Limit1].[ServiceName] AS [ServiceName], 
    [Limit1].[BusinessRuleName] AS [BusinessRuleName], 
    [Limit1].[ResponseMessage] AS [ResponseMessage], 
    [Limit1].[ResponseDateTime] AS [ResponseDateTime], 
    [Limit1].[GenNextId] AS [GenNextId], 
    [Limit1].[SourceSystem] AS [SourceSystem]
FROM   
    (SELECT 
        [Distinct1].[BusinessRuleName] AS [BusinessRuleName], 
        [Distinct1].[GenNextId] AS [GenNextId]
    FROM 
        (SELECT DISTINCT 
            [Extent1].[BusinessRuleName] AS [BusinessRuleName], 
            [Extent1].[GenNextId] AS [GenNextId]
        FROM 
            [dbo].[BusinessRuleException] AS [Extent1]
        WHERE 
            ([Extent1].[ResponseDateTime] >= GetDate()-30) 
            AND ([Extent1].[ResponseDateTime] <= GetDate())
        )  AS [Distinct1] ) AS [Project2]
    OUTER APPLY  (SELECT TOP (1) [Project3].[Id] AS [Id],
     [Project3].[OriginalCorrelationId] AS [OriginalCorrelationId], 
     [Project3].[ServiceName] AS [ServiceName],
      [Project3].[BusinessRuleName] AS [BusinessRuleName], 
      [Project3].[ResponseMessage] AS [ResponseMessage], 
      [Project3].[ResponseDateTime] AS [ResponseDateTime], 
      [Project3].[GenNextId] AS [GenNextId], 
      [Project3].[SourceSystem] AS [SourceSystem]
        FROM ( SELECT 
            [Extent2].[Id] AS [Id], 
            [Extent2].[OriginalCorrelationId] AS [OriginalCorrelationId], 
            [Extent2].[ServiceName] AS [ServiceName], 
            [Extent2].[BusinessRuleName] AS [BusinessRuleName], 
            [Extent2].[ResponseMessage] AS [ResponseMessage], 
            [Extent2].[ResponseDateTime] AS [ResponseDateTime], 
            [Extent2].[GenNextId] AS [GenNextId], 
            [Extent2].[SourceSystem] AS [SourceSystem]
            FROM [dbo].[BusinessRuleException] AS [Extent2]
            WHERE ([Extent2].[ResponseDateTime] >= GetDate()-30) AND ([Extent2].[ResponseDateTime] <= GetDate() ) 
            AND ([Project2].[BusinessRuleName] = [Extent2].[BusinessRuleName]) 

            AND (([Project2].[GenNextId] = [Extent2].[GenNextId]) 
            OR (([Project2].[GenNextId] IS NULL) AND ([Extent2].[GenNextId] IS NULL)))
        )  AS [Project3]
        ORDER BY [Project3].[ResponseDateTime] DESC ) AS [Limit1]

This converted query is very slow and the command execution time is for hours. I have written the required query in SQL directly as below which is a fast performing query:

WITH ranked_messages AS  
(
    SELECT 
        p.*, 
        ROW_NUMBER() OVER (PARTITION BY BusinessRuleName, GenNextId ORDER BY ResponseDateTime DESC) AS rn
    FROM 
        BusinessRuleException AS p
    WHERE 
        ResponseDateTime >= @FromDate
        AND ResponseDateTime <= @ToDate
)
SELECT *
FROM ranked_messages 
WHERE rn = 1

I am not sure how to convert or optimize my EF LINQ query to perform faster

You could always package up your fast executing query into a stored procedure, and then call that stored procedure from your EF code — marc_s
– marc_s, Commented Oct 8, 2018 at 4:35
Thanks. I was avoiding that as I was more keen on improving the query performance. Tried the answer below and it works. — HappyDeveloper
– HappyDeveloper, Commented Oct 9, 2018 at 4:12

HappyDeveloper · Accepted Answer · 2018-10-09 03:48:01Z

1

This one performs at the same speed of given sql query:

 var dbModels =
                        from bre in context.BusinessRuleExceptions
                        where bre.ResponseDateTime >= businessRuleSearchParameters.FromDate
                              && bre.ResponseDateTime <= businessRuleSearchParameters.ToDate
                        group bre by new { bre.BusinessRuleName, bre.GenNextId }
                        into g
                        let maxResponseDateTime = g.Max(x => x.ResponseDateTime)
                        let res = context.BusinessRuleExceptions.FirstOrDefault(p => p.ResponseDateTime == maxResponseDateTime &&
                            p.BusinessRuleName == g.Key.BusinessRuleName &&
                            p.GenNextId == g.Key.GenNextId)
                        select res;

answered Oct 9, 2018 at 3:48

HappyDeveloper

1731 gold badge1 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimizing SQL query in Entity Framework for performance improvements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related