Linq to sql - aggregation not being executed on database server

Question

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.

.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

Is there a ToList() before the GroupBy? Otherwise I don't see how `DateTime.Now - l.Created` could run with EF. Linq-to-sql, on the other hand, will translate this to sql, which probably explains why you see a different query in linqpad (wich uses L2S if you don't explicitly connect to your EF context). — Gert Arnold, Jul 13 '12 at 19:04
http://stackoverflow.com/a/346850/8155 The number one sql anti-pattern is to mix presentation logic with data access. — Amy B, Jul 13 '12 at 19:11
Bear in mind that you're using LINQ-to-SQL in LINQPad and Entity Framework in Visual Studio. You can get LINQPad to use your EF4 model by clicking 'Add Connection' and choosing the EF context from your application. — Joe Albahari, Jul 14 '12 at 00:22

score 4 · Answer 1 · answered Jul 13 '12 at 17:25

Without access to your full query, in the very least I think you can make the calculations on your dates and your ternary operator cleaner and more efficient.

By using the let command you should only be doing the calculation once in SQL instead of every single comparison.

Also, by reversing how you are searching for your time period from the oldest time period to the earliest time period, you only need to check if it is greater. You can apply the same type of thinking to your period type.

I can't say for sure if this will translate to server side, but it should be a step in the right direction:

var results = (from u in YOURTABLE
    let totalMinutes = (DateTime.Now - u.Created).TotalMinutes
    orderby u.Created
    select new
    {
       u,
       Period = totalMinutes > 3628800 ? "Before current year" :
                (totalMinutes > 907200 ? "Current year" :
                (totalMinutes > 302400 ? "Within last quarter" :
                (totalMinutes > 10080 ? "Within last month" :
                (totalMinutes > 1440 ? "Within last week" :
                (totalMinutes > 360 ? "Within Last 24 hours" :
                (totalMinutes > 60 ? "Within last 6 hours" :
                "Within last hour"
                ))))))
    }).GroupBy (l =>
    new
    {
    Level = l.u.Level,
    l.Period
    }
    );

Linq to sql - aggregation not being executed on database server

1 Answers1