如何最优化C＃中的LINQ code这个小位 [英] 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

在右上方有显示谁玩过这个游戏的最后一个人框。在我使用的分析器，它表明这是采取900毫秒运行哪个是总页面加载时间约80％。

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.

查询相对简单：

// 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.

在这方面的一个输出缓存将不适合，因为这将是很好的这个盒子是实时的。此外，ontop正常页面加载的900毫秒的速度太慢，即使一个用户飘飞所以想避免这种可能的话。

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:

• 包含最后删除玩家的加入
需要一个新的数据库表

• 保存在某个地方一个领域，认为框的HTML，出现这种情况是重建那场
每一个新游戏

• 两个
组合

• A new database table that holds the last players removing the need for the join
• A field stored somewhere that holds the HTML of that box, every new play that happens it rebuilds that field
• Combination of the both

这两种类型的难看！任何帮助AP preciated。

Both sort of ugly! Any help appreciated.

按照要求，linqpad结果

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

查询计划

推荐答案

我愿意打赌，几乎所有你看到的延迟来自于数据库本身pretty好钱，而不是LINQ（使这是一个数据库的优化问题，而不是一个LINQ优化问题）。

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).

我会使用linqpad有在正在生成查询什么偷看（见：的http://www.thereforesystems.com/view-t-sql-query-generated-by-linq-to-sql-using-linqpad/),并张贴在这里。查询计划运行在SQL Management Studio中的查询（假设您正在使用SQL Server）也将是有益的。

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
}

这篇关于如何最优化C＃中的LINQ code这个小位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！