locked
Entity framework 6 - Reusing Select queries RRS feed

  • Question

  • I am using .net and Entity Framework 6

    I have a table "Activities" in Sql Server which has a ExerciseType column

    I am trying to count the total number of rows for each exercise type.

              //dbCtx is of type System.Data.Entity.DbContext 
              int nbType1 = dbCtx.Activities.Count(a => a.Exercise == "TYPE1");
              int nbType2 = dbCtx.Activities.Count(a => a.Exercise == "TYPE2");
              int nbType3 = dbCtx.Activities.Count(a => a.Exercise == "TYPE3");
              int nbType4 = dbCtx.Activities.Count(a => a.Exercise == "TYPE4");

    those are the queries corresponding to the previous code :

    SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Activities] AS [Extent1] WHERE (N'TYPE1' = [Extent1].[ExerciseType]) ) AS [GroupBy1]

    SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Activities] AS [Extent1] WHERE (N'TYPE2' = [Extent1].[ExerciseType]) ) AS [GroupBy1]

    SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Activities] AS [Extent1] WHERE (N'TYPE3' = [Extent1].[ExerciseType]) ) AS [GroupBy1]

    SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Activities] AS [Extent1] WHERE (N'TYPE4' = [Extent1].[ExerciseType]) ) AS [GroupBy1]

    The queries are slow.

    As the query is almost identical for each call to EF. Is there a possibility to improve performance by caching the query ?

    Monday, November 3, 2014 11:11 AM

Answers

  • >As the query is almost identical for each call to EF. Is there a possibility to improve performance by caching the query ?

    Yes.  It's also possible to degrade performance.  If you use a literal string in the LINQ query, EF will use a literal string in the SQL query.  If you use a variable in the LINQ query EF will use a parameter in the SQL query. 

    The parameterized SQL Query will create a shared plan that be cached and reused on the server for any parameter value.  This reduces the compilation and optimization cost, but only permits a single query plan.  So if there is a lot of skew in the distribution of the rows, you may get better plans with the hard-coded values.  You can read up on this here:Parameters and Execution Plan Reuse and Plan Caching and Recompilation in SQL Server 2012

    Also you could rewrite this as a single GroupBy query. The trick is to group by a literal value, and to use Sum() to count the rows. Like this:

                    var q = from a in db.Activities
                            group a by 1 into allActivities
                            select new
                                {
                                    CountOfType1 = allActivities.Sum(c => c.Exersise == "TYPE1" ? 1 : 0),
                                    CountOfType2 = allActivities.Sum(c => c.Exersise == "TYPE2" ? 1 : 0),
                                    CountOfType3 = allActivities.Sum(c => c.Exersise == "TYPE3" ? 1 : 0),
                                    CountOfType4 = allActivities.Sum(c => c.Exersise == "TYPE4" ? 1 : 0)
                                };

    Which translates to

    SELECT
        [GroupBy1].[K1] AS [C1],
        [GroupBy1].[A1] AS [C2],
        [GroupBy1].[A2] AS [C3],
        [GroupBy1].[A3] AS [C4],
        [GroupBy1].[A4] AS [C5]
        FROM ( SELECT
            [Extent1].[K1] AS [K1],
            SUM([Extent1].[A1]) AS [A1],
            SUM([Extent1].[A2]) AS [A2],
            SUM([Extent1].[A3]) AS [A3],
            SUM([Extent1].[A4]) AS [A4]
            FROM ( SELECT
                1 AS [K1],
                CASE WHEN (N'TYPE1' = [Extent1].[Exersise]) THEN 1 ELSE 0 END AS [A1],
                CASE WHEN (N'TYPE2' = [Extent1].[Exersise]) THEN 1 ELSE 0 END AS [A2],
                CASE WHEN (N'TYPE3' = [Extent1].[Exersise]) THEN 1 ELSE 0 END AS [A3],
                CASE WHEN (N'TYPE4' = [Extent1].[Exersise]) THEN 1 ELSE 0 END AS [A4]
                FROM [dbo].[Activities] AS [Extent1]
            )  AS [Extent1]
            GROUP BY [K1]
        )  AS [GroupBy1]
    
    

    Which will calculate all the counts in a single scan.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Monday, November 3, 2014 2:49 PM

All replies

  • >As the query is almost identical for each call to EF. Is there a possibility to improve performance by caching the query ?

    Yes.  It's also possible to degrade performance.  If you use a literal string in the LINQ query, EF will use a literal string in the SQL query.  If you use a variable in the LINQ query EF will use a parameter in the SQL query. 

    The parameterized SQL Query will create a shared plan that be cached and reused on the server for any parameter value.  This reduces the compilation and optimization cost, but only permits a single query plan.  So if there is a lot of skew in the distribution of the rows, you may get better plans with the hard-coded values.  You can read up on this here:Parameters and Execution Plan Reuse and Plan Caching and Recompilation in SQL Server 2012

    Also you could rewrite this as a single GroupBy query. The trick is to group by a literal value, and to use Sum() to count the rows. Like this:

                    var q = from a in db.Activities
                            group a by 1 into allActivities
                            select new
                                {
                                    CountOfType1 = allActivities.Sum(c => c.Exersise == "TYPE1" ? 1 : 0),
                                    CountOfType2 = allActivities.Sum(c => c.Exersise == "TYPE2" ? 1 : 0),
                                    CountOfType3 = allActivities.Sum(c => c.Exersise == "TYPE3" ? 1 : 0),
                                    CountOfType4 = allActivities.Sum(c => c.Exersise == "TYPE4" ? 1 : 0)
                                };

    Which translates to

    SELECT
        [GroupBy1].[K1] AS [C1],
        [GroupBy1].[A1] AS [C2],
        [GroupBy1].[A2] AS [C3],
        [GroupBy1].[A3] AS [C4],
        [GroupBy1].[A4] AS [C5]
        FROM ( SELECT
            [Extent1].[K1] AS [K1],
            SUM([Extent1].[A1]) AS [A1],
            SUM([Extent1].[A2]) AS [A2],
            SUM([Extent1].[A3]) AS [A3],
            SUM([Extent1].[A4]) AS [A4]
            FROM ( SELECT
                1 AS [K1],
                CASE WHEN (N'TYPE1' = [Extent1].[Exersise]) THEN 1 ELSE 0 END AS [A1],
                CASE WHEN (N'TYPE2' = [Extent1].[Exersise]) THEN 1 ELSE 0 END AS [A2],
                CASE WHEN (N'TYPE3' = [Extent1].[Exersise]) THEN 1 ELSE 0 END AS [A3],
                CASE WHEN (N'TYPE4' = [Extent1].[Exersise]) THEN 1 ELSE 0 END AS [A4]
                FROM [dbo].[Activities] AS [Extent1]
            )  AS [Extent1]
            GROUP BY [K1]
        )  AS [GroupBy1]
    
    

    Which will calculate all the counts in a single scan.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Monday, November 3, 2014 2:49 PM
  • One usually doesn't hit the the database doing that kind of a query. Then there is the issue of object materlization that must be done before  it even tries to take the count into consideration for an object. You need to do the count in memory possibly on a disconnected collection -- query the objects into a collection and disconnect from the DB. It might help a little in the speed.

    The only other thing you could do is use a complied linq query where the execution plan is cached in memory, but I don't think that works (compiled Linq queries) in EF 6 anymore or it's hell to even make it work, unlike the previous versions of EF6.

    Monday, November 3, 2014 2:50 PM