需要帮助构建性能最佳的SQL查询

我正在制作一个监听器，这样我就可以将多个游戏的游戏数据发布到我的服务器上。我会用它来保持客户端的同步，无论他们在什么设备上玩！这部分我已经完成了，使用Entity和Linq进行插入，并在它们被取消同步时加载playerdata。

根据发送的数据，每个到服务器的网络帖子大约需要2-10毫秒。（我希望这很好！）。这主要是FirstOrDefault选择，修改一些数据并保存更改。

但服务器也必须向客户端提供记分牌。"今天/所有时间"answers"仅限所有人/朋友"。

我尝试了一些LinqSQL查询。但当我看到输出时，我真的很害怕它会如何成型（成千上万的用户和数百万的得分记录）。

这里有一个例子：

using (BertEntities BGE = new BertEntities())
{
    var query =
       (from score in BGE.ScoreTable
        join game in BGE.GameSession on score.GameSessionID equals game.ID
        join user in BGE.User on game.UserID equals user.ID
        where
                game.PublicHighScore == true &&
                game.Banned == false &&
                game.GameID == GameID &&
                score.LevelID == level
        orderby score.Score descending
        select new ScoreResult
        {
            ID = user.ID,
            Name = user.FacebookName,
            Score = score.Score,
            Streak = score.Streak,
            Time = score.Time
        }).Skip(skip).Take(count);

    return JsonConvert.SerializeObject(query.ToList());
}

这样执行

SELECT TOP (100)
    [Project1].[Score] AS [Score],
    [Project1].[ID] AS [ID],
    [Project1].[FacebookName] AS [FacebookName],
    [Project1].[Streak] AS [Streak],
    [Project1].[Time] AS [Time]
    FROM ( SELECT [Project1].[Score] AS [Score], [Project1].[Streak] AS [Streak], [Project1].[Time] AS [Time], [Project1].[ID] AS [ID], [Project1].[FacebookName] AS [FacebookName], row_number() OVER (ORDER BY [Project1].[Score] ASC) AS [row_number]
        FROM ( SELECT
        [Filter1].[Score] AS [Score],
        [Filter1].[Streak] AS [Streak],
        [Filter1].[Time] AS [Time],
        [Extent3].[ID] AS [ID],
        [Extent3].[FacebookName] AS [FacebookName]
        FROM   (SELECT [Extent1].[LevelID] AS [LevelID], [Extent1].[Score] AS [Score], [Extent1].[Streak] AS [Streak], [Extent1].[Time] AS [Time], [Extent2].[UserID] AS [UserID], [Extent2].[GameID] AS [GameID]
            FROM  [dbo].[ScoreTable] AS [Extent1]
            INNER JOIN [dbo].[GameSession] AS [Extent2] ON [Extent1].[GameSessionID] = [Extent2].[ID]
            WHERE (1 = [Extent2].[PublicHighScore]) AND (0 = [Extent2].[Banned]) ) AS [Filter1]
        INNER JOIN [dbo].[User] AS [Extent3] ON [Filter1].[UserID] = [Extent3].[ID]
        WHERE ([Filter1].[GameID] = @p__linq__0) AND ([Filter1].[LevelID] = @p__linq__1)
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[Score] DESC

我不是SQL专家，但这对我来说很难

这是数据库布局：www.invokergame.com/db.png

我正在考虑将我需要的所有数据存储在ScoreTable表中，这样我就可以避免进行联接。但这会导致大量重复的数据，我敢肯定这是错误的方法。

也许我应该忘记Entity Linq，用ADO.NET SQL编写它（我需要帮助）？

对不起，文字墙，我期待着一些明智的话语！

编辑：

好吧，汤姆，这是你假装的吗？将我需要的所有数据拖放到一个数据集中。然后，当用户创建新的highscore或禁用PublicHighScore时，使用LINQ更新或删除并从此列表中选择项目？

这将如何表现？

public class ScoreCacheItem
{
    public Int64 ScoreTableID { get; set; }
    public Guid GameID { get; set; }
    public int LevelID { get; set; }
    public Int64 UserID { get; set; }
    public string Name { get; set; }
    public int Score { get; set; }
    public int Streak { get; set; }
    public double Time { get; set; }
    public DateTime Today { get; set; }
    public int Today_Score { get; set; }
    public int Today_Streak { get; set; }
    public double Today_Time { get; set; }
}

public sealed class ScoreCacheSystem
{
    private List<ScoreCacheItem> FScoreCache = new List<ScoreCacheItem>();
    private object FLock = new object();
    private List<ScoreCacheItem> LoadAllScores()
    {
        using (BertEntities BGE = new BertEntities())
        {
            var query =
               (from score in BGE.ScoreTable
                join game in BGE.GameSession on score.GameSessionID equals game.ID
                join user in BGE.User on game.UserID equals user.ID
                where
                        game.PublicHighScore == true &&
                        game.Banned == false 
                orderby score.Score descending
                select new ScoreCacheItem
                {
                    ScoreTableID = score.ID,
                    GameID = game.GameID,
                    LevelID = score.LevelID,
                    UserID = user.ID,
                    Name = user.FacebookName,
                    Score = score.Score,
                    Streak = score.Streak,
                    Time = score.Time,
                    Today = score.Today,
                    Today_Score = score.Today_Score,
                    Today_Streak = score.Today_Streak,
                    Today_Time = score.Today_Time
                });
            return query.ToList();
        }
    }
    public List<ScoreCacheItem> HighScores {
        get
        {
            lock (FLock)
            {
                if (FScoreCache == null)
                {
                    FScoreCache = LoadAllScores();
                }
                return FScoreCache;
            }
        }
    }
}