none
LINQ performance puzzle, LINQpad vs application RRS feed

  • Question

  • Hi,

    I am encountering a very strange behavior with my application when working with LINQ to SQL.

    Given the following LINQ statement:

    from A in TableA.Where(A => A.Cond1 == Cond || A.Cond2 == Cond) 
    join B in TableB.Where(B => B.TimeStamp >= From && B.TimeStamp <= To) on A.ID equals B.fkA
    group B by A.fkOther into grouped
    select new {
    ID = grouped.Key,
    FirstInGroup = grouped.FirstOrDefault(B => B.TimeStamp == grouped.Min(bla => bla.TimeStamp)),
    LastInGroup  = grouped.FirstOrDefault(B => B.TimeStamp == grouped.Max(bla => bla.TimeStamp))
    }

    I have sets defined in table A which group entities from Table B. I am trying to get the first and last entity from table B within the sets, filtered by some conditions.

    Table A has around 400 sets and Table B has around 10million entries.

    This LINQ statement runs perfectly in LINQpad (200ms). When running the same statement, with same conditions, on the same database, within my application the statement takes around 20seconds.

    I have compared the SQL statements created by the provider and they are the same.

    I have profiled the exec sp_executesql with the SQL Profiler and the executed SQL statements are the same. There is a difference though, the duration of the RPC completed event takes the 20seconds from my application but only 200ms from LINQpad. The Read count of the RPC completed event is around 20million from my application and only 6000 from LINQpad. So the SQL server is obviously doing something different, when I run the code from my application.

    I even have included the assembly from my application into LINQpad and ran the method, which performs the statement, within LINQpad (to be sure there are no side effects from my application) and the results are the same... the method compiled in my assembly runs 20seconds.

    I also have tried to update my assembly from .NET 3.5 to 4.0, because I thought there was a bug in the LINQtoSQL implementation in .NET 3.5, still the same results.

    I also have tried to run the LINQ statement in a completely clean and new .NET 4.0 console application, but the results are the same.

    I found a way to resolve the performance issue, but I do not know why.
    I changed the condition on table A from 

    TableA.Where(A => A.Cond1 == Cond || A.Cond2 == Cond)

    to 

    TableA.Where(A => A.Cond1 != null ? A.Cond1 == Cond : A.Cond2 == Cond)

    which caused LINQ to generate a CASE WHEN instead of a OR statement.

    I think I can not sleep anymore, if I do not find out, WHY the same statement works within LINQpad, but in neither of my applications.

    Does anyone have an idea what causes the differences?

    Best regards,
    Martin

    Tuesday, September 18, 2012 3:25 PM

Answers