none
Linq to sql - aggregation not being executed on database server RRS feed

  • Question

  • Hi,

    I've got a linq to sql query in c# that works - but results in very poor performance when used within an ASP.NET application that outputs a data summary to a webpage. This query attempts to group logs from a single log4net table.  There are no joins.

    _repository.Logs                           .GroupBy(l => new
                                                  {
                                                      Level = l.Level,
                                                      Period = ((DateTime.Now - l.Created).TotalMinutes <= 60)
                                                                   ? "Within last hour"
                                                                   : (((DateTime.Now - l.Created).TotalMinutes > 60) &&
                                                                      ((DateTime.Now - l.Created).TotalMinutes <= 360)
                                                                     )
                                                                         ? "Within last 6 hours"
                                                                         : (((DateTime.Now - l.Created).TotalMinutes > 360) &&
                                                                            ((DateTime.Now - l.Created).TotalMinutes <= 1440)
                                                                           )
                                                                               ? "Within last 24 hours"
                                                                               : (((DateTime.Now - l.Created).TotalMinutes >
                                                                                   1440) &&
                                                                                  ((DateTime.Now - l.Created).TotalMinutes <=
                                                                                   10080)
                                                                                 )
                                                                                     ? "Within last week"
                                                                                     : (((DateTime.Now - l.Created).
                                                                                             TotalMinutes > 10080) &&
                                                                                        ((DateTime.Now - l.Created).
                                                                                             TotalMinutes <= 302400)
                                                                                       )
                                                                                           ? "Within last month"
                                                                                           : (((DateTime.Now - l.Created).
                                                                                                   TotalMinutes > 302400) &&
                                                                                              ((DateTime.Now - l.Created).
                                                                                                   TotalMinutes <= 907200)
                                                                                             )
                                                                                                 ? "Within last quarter"
                                                                                                 : (((DateTime.Now -
                                                                                                      l.Created).
                                                                                                         TotalMinutes >
                                                                                                     907200) &&
                                                                                                    ((DateTime.Now -
                                                                                                      l.Created).
                                                                                                         TotalMinutes <=
                                                                                                     3628800)
                                                                                                   )
                                                                                                       ? "Current year"
                                                                                                       : ((DateTime.Now -
                                                                                                           l.Created).
                                                                                                              TotalMinutes >
                                                                                                          3628800)
                                                                                                             ? "Before current year"
                                                                                                             : String.Empty,
                                                      PeriodType = ((DateTime.Now - l.Created).TotalMinutes <= 60)
                                                                       ? 1
                                                                       : (((DateTime.Now - l.Created).TotalMinutes > 60) &&
                                                                          ((DateTime.Now - l.Created).TotalMinutes <= 360)
                                                                         )
                                                                             ? 2
                                                                             : (((DateTime.Now - l.Created).TotalMinutes >
                                                                                 360) &&
                                                                                ((DateTime.Now - l.Created).TotalMinutes <=
                                                                                 1440)
                                                                               )
                                                                                   ? 3
                                                                                   : (((DateTime.Now - l.Created).
                                                                                           TotalMinutes > 1440) &&
                                                                                      ((DateTime.Now - l.Created).
                                                                                           TotalMinutes <= 10080)
                                                                                     )
                                                                                         ? 4
                                                                                         : (((DateTime.Now - l.Created).
                                                                                                 TotalMinutes > 10080) &&
                                                                                            ((DateTime.Now - l.Created).
                                                                                                 TotalMinutes <= 302400)
                                                                                           )
                                                                                               ? 5
                                                                                               : (((DateTime.Now - l.Created)
                                                                                                       .TotalMinutes >
                                                                                                   302400) &&
                                                                                                  ((DateTime.Now - l.Created)
                                                                                                       .TotalMinutes <=
                                                                                                   907200)
                                                                                                 )
                                                                                                     ? 6
                                                                                                     : (((DateTime.Now -
                                                                                                          l.Created).
                                                                                                             TotalMinutes >
                                                                                                         907200) &&
                                                                                                        ((DateTime.Now -
                                                                                                          l.Created).
                                                                                                             TotalMinutes <=
                                                                                                         3628800)
                                                                                                       )
                                                                                                           ? 7
                                                                                                           : ((DateTime.Now -
                                                                                                               l.Created).
                                                                                                                  TotalMinutes >
                                                                                                              3628800)
                                                                                                                 ? 8
                                                                                                                 : 0
                                                  }
                                ).ToList();


    What I find when I run the SQL profiler is that the query actually being run is this:

    SELECT 
    [Extent1].[LogID] AS [LogID], 
    [Extent1].[Created] AS [Created], 
    [Extent1].[RoleInstance] AS [RoleInstance], 
    [Extent1].[DeploymentId] AS [DeploymentId], 
    [Extent1].[Machine] AS [Machine], 
    [Extent1].[Thread] AS [Thread], 
    [Extent1].[Level] AS [Level], 
    [Extent1].[Logger] AS [Logger], 
    [Extent1].[Message] AS [Message], 
    [Extent1].[Exception] AS [Exception]
    FROM [dbo].[Logs] AS [Extent1]

    Essentially, it looks like all the grouping etc is being done on the client side. As there is quite a slow link between the web server and database server - this results in a significant amount of time before the web page actually displays results.  Also, the larger the log database gets, the slower the response.

    Strangely enough - when I run a similar query in LinqPad - I get an entirely different plan - something like this (slight different query):

    exec sp_executesql N'SELECT COUNT(*) AS [Count], [t1].[Level], [t1].[value] AS [Period], [t1].[value2] AS [PeriodType]
    FROM (
        SELECT [t0].[Level], 
            (CASE 
                WHEN ((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p0))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p0), [t0].[Created]), @p0)) * 10000))) / 600000000) <= @p1 THEN CONVERT(NVarChar(20),@p2)
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p3))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p3), [t0].[Created]), @p3)) * 10000))) / 600000000) > @p4) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p5))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p5), [t0].[Created]), @p5)) * 10000))) / 600000000) <= @p6) THEN CONVERT(NVarChar(20),@p7)
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p8))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p8), [t0].[Created]), @p8)) * 10000))) / 600000000) > @p9) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p10))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p10), [t0].[Created]), @p10)) * 10000))) / 600000000) <= @p11) THEN @p12
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p13))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p13), [t0].[Created]), @p13)) * 10000))) / 600000000) > @p14) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p15))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p15), [t0].[Created]), @p15)) * 10000))) / 600000000) <= @p16) THEN CONVERT(NVarChar(20),@p17)
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p18))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p18), [t0].[Created]), @p18)) * 10000))) / 600000000) > @p19) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p20))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p20), [t0].[Created]), @p20)) * 10000))) / 600000000) <= @p21) THEN CONVERT(NVarChar(20),@p22)
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p23))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p23), [t0].[Created]), @p23)) * 10000))) / 600000000) > @p24) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p25))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p25), [t0].[Created]), @p25)) * 10000))) / 600000000) <= @p26) THEN CONVERT(NVarChar(20),@p27)
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p28))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p28), [t0].[Created]), @p28)) * 10000))) / 600000000) > @p29) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p30))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p30), [t0].[Created]), @p30)) * 10000))) / 600000000) <= @p31) THEN CONVERT(NVarChar(20),@p32)
                WHEN ((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p33))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p33), [t0].[Created]), @p33)) * 10000))) / 600000000) > @p34 THEN CONVERT(NVarChar(20),@p35)
                ELSE CONVERT(NVarChar(20),@p36)
             END) AS [value], 
            (CASE 
                WHEN ((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p37))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p37), [t0].[Created]), @p37)) * 10000))) / 600000000) <= @p38 THEN @p39
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p40))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p40), [t0].[Created]), @p40)) * 10000))) / 600000000) > @p41) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p42))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p42), [t0].[Created]), @p42)) * 10000))) / 600000000) <= @p43) THEN @p44
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p45))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p45), [t0].[Created]), @p45)) * 10000))) / 600000000) > @p46) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p47))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p47), [t0].[Created]), @p47)) * 10000))) / 600000000) <= @p48) THEN @p49
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p50))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p50), [t0].[Created]), @p50)) * 10000))) / 600000000) > @p51) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p52))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p52), [t0].[Created]), @p52)) * 10000))) / 600000000) <= @p53) THEN @p54
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p55))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p55), [t0].[Created]), @p55)) * 10000))) / 600000000) > @p56) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p57))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p57), [t0].[Created]), @p57)) * 10000))) / 600000000) <= @p58) THEN @p59
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p60))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p60), [t0].[Created]), @p60)) * 10000))) / 600000000) > @p61) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p62))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p62), [t0].[Created]), @p62)) * 10000))) / 600000000) <= @p63) THEN @p64
                WHEN (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p65))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p65), [t0].[Created]), @p65)) * 10000))) / 600000000) > @p66) AND (((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p67))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p67), [t0].[Created]), @p67)) * 10000))) / 600000000) <= @p68) THEN @p69
                WHEN ((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t0].[Created], @p70))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t0].[Created], @p70), [t0].[Created]), @p70)) * 10000))) / 600000000) > @p71 THEN @p72
                ELSE @p73
             END) AS [value2]
        FROM [Logs] AS [t0]
        ) AS [t1]
    GROUP BY [t1].[Level], [t1].[value], [t1].[value2]',N'@p0 datetime,@p1 float,@p2 nvarchar(4000),@p3 datetime,@p4 float,@p5 datetime,@p6 float,@p7 nvarchar(4000),@p8 datetime,@p9 float,@p10 datetime,@p11 float,@p12 nvarchar(4000),@p13 datetime,@p14 float,@p15 datetime,@p16 float,@p17 nvarchar(4000),@p18 datetime,@p19 float,@p20 datetime,@p21 float,@p22 nvarchar(4000),@p23 datetime,@p24 float,@p25 datetime,@p26 float,@p27 nvarchar(4000),@p28 datetime,@p29 float,@p30 datetime,@p31 float,@p32 
    nvarchar(4000),@p33 datetime,@p34 float,@p35 nvarchar(4000),@p36 nvarchar(4000),@p37 datetime,@p38 float,@p39 int,@p40 datetime,@p41 float,@p42 datetime,@p43 float,@p44 int,@p45 datetime,@p46 float,@p47 datetime,@p48 float,@p49 int,@p50 datetime,@p51 float,@p52 datetime,@p53 float,@p54 int,@p55 datetime,@p56 float,@p57 datetime,@p58 float,@p59 int,@p60 datetime,@p61 float,@p62 datetime,@p63 float,@p64 int,@p65 datetime,@p66 float,@p67 datetime,@p68 float,@p69 int,@p70 datetime,@p71 float,@p72 int,@p73 int',@p0='2012-07-11 23:46:56.457',@p1=60,@p2=N'Within last hour',@p3='2012-07-11 23:46:56.457',@p4=60,@p5='2012-07-11 23:46:56.457',@p6=360,@p7=N'Within last 6 hours',@p8='2012-07-11 23:46:56.457',@p9=360,@p10='2012-07-11 23:46:56.457',@p11=1440,@p12=N'Within last 24 hours',@p13='2012-07-11 23:46:56.457',@p14=1440,@p15='2012-07-11 23:46:56.457',@p16=10080,@p17=N'Within last week',@p18='2012-07-11 23:46:56.457',@p19=10080,@p20='2012-07-11 23:46:56.457',@p21=302400,@p22=N'Within last month',@p23='2012-07-11 23:46:56.457',@p24=302400,@p25='2012-07-11 23:46:56.457',@p26=907200,@p27=N'Within last quarter',@p28='2012-07-11 23:46:56.457',@p29=907200,@p30='2012-07-11 23:46:56.457',@p31=3628800,@p32=N'Current year',@p33='2012-07-11 23:46:56.457',@p34=3628800,@p35=N'Before current year',@p36=N'',@p37='2012-07-11 23:46:56.460',@p38=60,@p39=1,@p40='2012-07-11 23:46:56.460',@p41=60,@p42='2012-07-11 23:46:56.460',@p43=360,@p44=2,@p45='2012-07-11 23:46:56.460',@p46=360,@p47='2012-07-11 23:46:56.460',@p48=1440,@p49=3,@p50='2012-07-11 23:46:56.460',@p51=1440,@p52='2012-07-11 23:46:56.460',@p53=10080,@p54=4,@p55='2012-07-11 23:46:56.460',@p56=10080,@p57='2012-07-11 23:46:56.460',@p58=302400,@p59=5,@p60='2012-07-11 23:46:56.460',@p61=302400,@p62='2012-07-11 23:46:56.460',@p63=907200,@p64=6,@p65='2012-07-11 23:46:56.460',@p66=907200,@p67='2012-07-11 23:46:56.460',@p68=3628800,@p69=7,@p70='2012-07-11 23:46:56.460',@p71=3628800,@p72=8,@p73=0

    Now, if this were the result of the query i'm writing in c#, the time taken to output to the webpage would be significantly less.

    When debugging the c# code, I've tried several options - revolving around adding .ToList() statements at various point in the linq query. I've even tried changing the LazyLoadingEnabled in the EF4 designer.  Nothing seems to make the correct plan be used when I look at the SQL profiler.  I've also tried the DataLoadOptions - but they don't seem to make any different either as it looks like the options would be more useful where table joins are used.

    What am I doing wrong?  How can I get the group by and any other aggregation to happen on the database server so that far fewer records are transferred to the webserver?  Is this due to lazy loading in Linq?  How can I correct this?

    thanks



    Wednesday, July 11, 2012 10:55 PM

Answers

  • Hi

    You should create a stroed procedure inside of SQL Server and call it through Linq, that would be improve the performance of your application.

    Best Regards,


    Please mark this as answer if it helps with this issue!

    • Marked as answer by ossent Tuesday, July 17, 2012 9:18 AM
    Tuesday, July 17, 2012 7:08 AM

All replies

  • Hi

    You should create a stroed procedure inside of SQL Server and call it through Linq, that would be improve the performance of your application.

    Best Regards,


    Please mark this as answer if it helps with this issue!

    • Marked as answer by ossent Tuesday, July 17, 2012 9:18 AM
    Tuesday, July 17, 2012 7:08 AM
  • I suppose that's one way to do it. I'd wanted to flexibility of including queries via linq in code - rather than on the server side. However, doesn't look like in this case it's possible.

    thanks

    Tuesday, July 17, 2012 9:19 AM
  • In case this helps somebody, when running Linq like that above - ensure the original type is the same type as the Linq to Entities object that is defined in the Entity Framework designer and is not of the form:

    select new CustomClass { FirstProperty = l.Property1, SecondProperty = l.Property2 }


     The problem I had some there was that:

    _repository.Logs

    was actually not of the same type as the class in the Entity Framework designer.  This meant that effectively Linq was running a select * from the whole table in order to populate the custom class.

    By removing this custom class and just using a generic select new {} with appropriate properties - Linq then started deferring the execution of the query and including all the groupby statements that were defined later on code.

    The resultant sql executed began with:

    exec sp_executesql N'SELECT
    1 AS [C1],

    exec sp_executesql N'SELECT 
    1 AS [C1], 
    whic is exactly what I wanted.



    • Edited by ossent Tuesday, July 17, 2012 2:36 PM
    Tuesday, July 17, 2012 2:35 PM