Why would LINQ to SQL perform better than a straight SQL query?

Question

Strange performance outcome, I have a LINQ to SQL query which uses several let statements to get various info it looks like this

    public IQueryable<SystemNews> GetSystemNews()
{
    using (var t = new TransactionScope(TransactionScopeOption.Required,
              new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
    {
        var results = from s in _datacontext.SystemNews
                      let member = GetMemberInfo(s.MemberID)
                      let determination = GetDetermination(s.DeterminationID.Value)
                      let daimoku = GetDaimoku(s.DaimokuID.Value)
                      let entry = GetEntry(s.EntryID.Value)
                      let encouragment = GetEncouragement(s.EncouragementID.Value)
                      select new SystemNews
                      {
                          NewsDate = s.NewsDate,
                          MemberID = s.MemberID,
                          PhotoID = s.PhotoID.Value,
                          DeterminationID = s.DeterminationID.Value,
                          DaimokuID = s.DaimokuID.Value,
                          EntryID = s.EntryID.Value,
                          EncouragementID = s.EncouragementID.Value,
                          Member = new LazyList<Members>(member),
                          Determination = new LazyList<Determinations>(determination),
                          Daimoku = new LazyList<MemberDaimoku>(daimoku),
                          Entry = new LazyList<MemberEntries>(entry),
                          Encouragement = new LazyList<MemberEncouragements>(encouragment),
                          IsDeterminationComplete = s.IsDeterminationComplete.Value
                      };
        return results;
    }
}

I created the same thing (basically at least the various info that is obtained in this) into a SQL View, and the LINQ to SQL returned results in under 90 miliseconds where as the view returned the same data actually less info in over 700 milliseconds. Can anyone explain this?

Answer 1

Have you used the SQL profiler to check the differences between the SQL being generated by LINQ, and the SQL you are using to compare?

Perhaps the LINQ to SQL query is better optimised?

Answer 2

You're probably seeing a caching effect; it's not real.

One of the tenets of benchmarking is that you can skew your benchmark either way by running the benchmark once, getting the results in cache, then run it again and report amazing results, or, do the reverse...

Answer 3

In general, LINQ-to-SQL will be "about the same", and I'd agree that caching is porbably an issue here. Really you need to look at the generated SQL; the easiest way being:

_datacontext.Log = Console.Out;

Then try running the TSQL directly in Query Analyzer. SPROCs have some advantages in terms of being able to optimise access, use table variables, etc; however, LINQ has the advantage of composability - i.e. you can apply your paging, sorting, projections, etc in the single query. You can at a push do that with an SPROC, but it is very hard work.

But really, the key is in looking at the TSQL; it might be that you haven't loaded all the data, for example (lazy loading).

Answer 4

At a cursory glance it looks like your Linq to SQL query is making use of lazy loading. If I'm right about that then it makes sense that running your query wont actually run all parts of the query at that time. This means that if you compare it to a SQL View which is set to bring back all of these results in one swoop then that would definitely be more work and thus take extra time.

Two other points:

1. Make sure you are timing the actual query execution and not the assignment of the query expression. Remember Linq queries are deferred so when you return your query from the method above it is actually returning the expression not the results. In general three popular ways to make a Linq query execute are by running the ToArray or ToList extension methods or by simply iterating over the results using foreach.

2. If you are in fact doing lazy loading and timing the actual execution of the query and still ending up with better results in Linq to SQL it could be due to differences in having wide flattened results returned by your SQL View in comparison to narrow pieces being queried multiple times with Linq to SQL.

I've never done any benchmarks on this to see where the tipping point is but you can imagine the case where you have two tables Table1, and Table2 both with 100 columns per table and 1,000 rows of data in Table1 which also has a one to many relationship to Table2 and generally yields 10 matches in Table2 for every record in Table1. Now if you write a view to pull all of these results back in a single query you will expect around 10,000 rows of data all with about 200 columns wide. By issuing two separate queries however either via Linq to SQL or any other mechanism you can instead get your initial 1,000 row 100 column wide results from Table1 and then pick the remaining Table2 items potentially 10,000 rows by another 100 columns for a total of 1,100,000 cells (which is much less than the 2,000,000 cells from the SQL View). The benefits are even more exaggerated if we assume there is a large degree of overlap between the dependent rows of a many to many relationship. In the most extreme case there is 100% overlap meaning we would only expect a total of 100,010 cells of data to be pulled back which is far less than the 2,000,000 returned by a flattened view.