CODEWITHSUNDEEP
c#asp.netperformancelinqoptimization
Tom Gullen
Tom Gullen

Reputation: 61729

How best to optimise this small bit of c# Linq code

If you take a look at our arcade homepage:

http://www.scirra.com/arcade

In the top right there is a box showing the last people who played this game. In the profiler I'm using, it shows this as taking 900ms to run which is about 80% of the total page load time.

The query is relatively simple:

// Recent players
using (MainContext db = new MainContext())
{
    var q = (from c in db.tblArcadeGamePlays
                join a in db.tblProfiles on c.UserID equals a.UserID
                where c.UserID != 0
                select new
                {
                    c.UserID,
                    c.tblForumAuthor.Username,
                    a.EmailAddress,
                    Date = (from d in db.tblArcadeGamePlays where d.UserID == c.UserID orderby d.Date descending select new { d.Date }).Take(1).Single().Date
                })
    .Distinct()
    .OrderByDescending(c => c.Date)
    .Take(16);

But it's too slow for my needs.

An output cache on this would not be suitable because it would be nice for this box to be in real time. Also, 900ms ontop of normal page load is too slow even for one user every now and then so would like to avoid that if possible.

Does anyone have any ideas on how I can speed this up? My two ideas at the moment are to have:

Both sort of ugly! Any help appreciated.

As requested, linqpad results

Lambda

TblArcadeGamePlays
   .Join (
      TblProfiles, 
      c => c.UserID, 
      a => a.UserID, 
      (c, a) => 
         new  
         {
            c = c, 
            a = a
         }
   )
   .Where (temp0 => (temp0.c.UserID != 0))
   .Select (
      temp0 => 
         new  
         {
            UserID = temp0.c.UserID, 
            Username = temp0.c.User.Username, 
            EmailAddress = temp0.a.EmailAddress, 
            Date = TblArcadeGamePlays
               .Where (d => (d.UserID == temp0.c.UserID))
               .OrderByDescending (d => d.Date)
               .Select (
                  d => 
                     new  
                     {
                        Date = d.Date
                     }
               )
               .Take (1)
               .Single ().Date
         }
   )
   .Distinct ()
   .OrderByDescending (c => c.Date)
   .Take (16)

SQL

-- Region Parameters
DECLARE @p0 Int = 0
-- EndRegion
SELECT TOP (16) [t6].[UserID], [t6].[Username], [t6].[EmailAddress], [t6].[value] AS [Date2]
FROM (
    SELECT DISTINCT [t5].[UserID], [t5].[Username], [t5].[EmailAddress], [t5].[value]
    FROM (
        SELECT [t0].[UserID], [t2].[Username], [t1].[EmailAddress], (
            SELECT [t4].[Date]
            FROM (
                SELECT TOP (1) [t3].[Date]
                FROM [tblArcadeGamePlays] AS [t3]
                WHERE [t3].[UserID] = [t0].[UserID]
                ORDER BY [t3].[Date] DESC
                ) AS [t4]
            ) AS [value]
        FROM [tblArcadeGamePlays] AS [t0]
        INNER JOIN [tblProfile] AS [t1] ON [t0].[UserID] = [t1].[UserID]
        INNER JOIN [tblForumAuthor] AS [t2] ON [t2].[Author_ID] = [t0].[UserID]
        ) AS [t5]
    WHERE [t5].[UserID] <> @p0
    ) AS [t6]
ORDER BY [t6].[value] DESC

Query Plan

enter image description here

Upvotes: 1

Views: 222

Answers (4)

Peter Bromberg
Peter Bromberg

Reputation: 1496

Run the SQL Server Database Tuning Wizard ( in SQL Management Studio / Tools) against your database with these queries and let it create statistics and indexes to tune your database for performance. How many people would recommend this? It works.

Upvotes: 0

David Ly
David Ly

Reputation: 31586

This might not be the issue, but your date query could be simplified to this:

Date = (from d in db.tblArcadeGamePlays 
        where d.UserID == c.UserID 
        orderby d.Date descending 
        select d.Date).First()

Maybe the round-about query was confusing the optimizer. Otherwise I agree with the other answers, check the generated SQL query and check your indices.

Also, are you sure Distinct is necessary and that it does what you think? It will only filter out duplicates that have the same value for every field/column.

Upvotes: 1

Chris Shain
Chris Shain

Reputation: 51319

I'd be willing to bet pretty good money that virtually all of the delay you are seeing comes from the database itself, not the LINQ (making this a database optimization question, not a LINQ optimization question).

I'd use linqpad to have a peek at what query is being generated (see: http://www.thereforesystems.com/view-t-sql-query-generated-by-linq-to-sql-using-linqpad/), and post that here. A query plan from running that query in SQL Management Studio (assuming that you are using SQL Server) would also be helpful.

OK, given the edits, try something like this. It should simplify the query dramatically:

using (MainContext db = new MainContext())
{
    var latestIds = db.tblArcadeGamePlays.OrderByDescending(c => c.Date).Select(c => c.UserID).Distinct().Take(16); // These are the 16 most recent player Ids.
    // join them here to the rest of those player's data
    var playerData = ... // you'll need to fill in here some by filtering the other data you want using latestIds.Contains
}

Upvotes: 5

Jordan
Jordan

Reputation: 2758

Make sure you have indexes on both sides of the join.

Upvotes: 0

Related Questions