SQL Sever 2008和实体框架-提高性能

Question

背景：

我有一个Windows服务，它从SQL表中提取记录(用EF代码第一方法创建)。记录是非常频繁地添加(~10-20秒)由两个客户端，然后剥离出数据库和我的服务处理。对于冗余，有两个客户端监视相同的系统，可能会创建重复的记录。我正在寻找一种方法来提高程序的性能，它正在处理新的记录。

问题：

步骤1:删除重复条目：

// get duplicate entries
var duplicateEntities = context.OpcTagValueLogs.GroupBy(x => new { x.OpcTagId, x.SourceTimeStamp }).SelectMany(x => x.OrderBy(y => y.Id).Skip(1));
foreach (var duplicateEntry in duplicateEntries)
{
   // remove duplicate entries
   context.OpcTagValueLogs.Remove(duplicateEntry );
}

步骤2:获取剩余的日志条目

var logs = context.OpcTagValueLogs.Include(x => x.OpcTag).Include(x => x.OpcTag.RuleSets).ToList();

步骤3:检查相关规则并执行事件以处理新值

我正在尽可能地优化我的程序，因为现在正在处理数据的windows服务比正在创建的记录运行得更快。如果唱片创制率增加，我担心服务会跟不上。

这些是我在这个表上运行的唯一查询(除了创建记录)。表的结构是：

• INT Id (主键，群集: Id)
• INT OpcTagId (IX_OpcTagId)
• 日期时间TimeStamp
• NVARCHAR(最大值)值
• INT SourceTimeStamp (IX_SourceTimeStamp)
• NVARCHAR(最大) ClientCode
• NVARCHAR(最大) PriorValue

有什么方法可以修改我的索引来提高查询的性能吗？

编辑:在删除副本之后，日志就是这样处理的：

foreach (var log in logs.ToList()) // because items will be removed from the list during the loop, it is important to update the list on
                {                                  // each iteration, hence the .ToList()

                    if (log.PriorValue == log.Value) // check to see if the prior value equals to new value
                    {                                // I am only interested in changing values, so delete the log entry
                        // remove the entity
                        _context.OpcTagValueLogs.Remove(log);
                        logs.Remove(log);
                        _context.SaveChanges();
                    }
                    else
                    {
                        // check rules and perform actions
                        var ruleSets = log.OpcTag.RuleSets.ToList();
                        foreach (var ruleSet in ruleSets)
                        {
                            if (ruleSet.CheckRule(log.PriorValue, log.Value))
                            {
                                // perform action
                                // convert source timestamp to datetime
                                DateTime srcTS = new DateTime(1970, 1, 1).AddSeconds(log.SourceTimeStamp);
                                var action = ActionFactory.CreateAction(ruleSet.Action, log.PriorValue, log.Value, log.OpcTag, srcTS);
                                action.Execute();
                            }
                        }

                        // remove entity from database
                        _context.OpcTagValueLogs.Remove(log);
                        _context.SaveChanges();
                        logs.Remove(log); // remove the entity from the local list as well                            
                    }
                }

编辑2:当前方法

var ruleSets = _context.RuleSets.ToList(); // Get entire rulesets once into memory
                var logsLocal = logs.ToList(); // bring all the logs into local memory
                var maxIndex = logsLocal.Max(x => x.Id); // the last index of the local logs
                foreach (var log in logsLocal)
                {
                    if (log.PriorValue != log.Value)
                    {
                        foreach (var ruleSet in ruleSets.Where(x => x.OpcTagId == log.OpcTagId))
                        {
                            if (ruleSet.CheckRule(log.PriorValue, log.Value))
                            {
                                // perform action
                                var action = ActionFactory.CreateAction(ruleSet.Action, log.PriorValue, log.Value, log.OpcTag, srcTS);
                                action.Execute();
                            }
                        }
                    }
                }

                _context.OpcTagValueLogs.Where(x=>x.Id <= maxIndex).Delete(); // batch delete only the logs that were processed on this program loop

编辑3:动作对象由基于ActionFactory值的静态ruleSet.Action类生成。

public static Action CreateAction(ActionId pActionId, string pPrior, string pNew, OpcTag pOpcTag, DateTime pSourceTimestamp)
    {
        Action evt = null;
        switch (pActionId)
        {
            case ActionId.A1000: evt = new A1000(pActionId, pPrior, pNew, pOpcTag, pSourceTimestamp);
                break;
            case ActionId.A1001: evt = new A1001(pActionId, pPrior, pNew, pOpcTag, pSourceTimestamp);
                break;
            case ActionId.A1002: evt = new A1002(pActionId, pPrior, pNew, pOpcTag, pSourceTimestamp);
                break;
            case ActionId.A1003: evt = new A1003(pActionId, pPrior, pNew, pOpcTag, pSourceTimestamp);
                break;
            case ActionId.A1004: evt = new A1004(pActionId, pPrior, pNew, pOpcTag, pSourceTimestamp);
                break;
        }
        return evt;
    }

这些操作中的每一个都代表不同的机器事件，每个操作可能是几百行代码(这就是为什么省略了它)。

Answer 1

首先，您的循环可能导致N+1问题。您正在循环循环并从循环中的DB查询项。您想要的是减少I/O (对DB的调用)，因为这是一个昂贵的操作。如果您的服务器足够强大，那么预先将对象存储在内存中是一个更好的选择。您甚至可以开始使用缓存技术，但现在可能有点先进。

下面是我提出的帮助解决N+1问题的代码(未经测试)：

// check rules and perform actions
// Get entire rulesets once into memory
var ruleSets = OpcTagValueLogs.OpcTag.RuleSets.ToList(); 

foreach (var log in logs.ToList()) // because items will be removed from the list during the loop, it is important to update the list on
{                                  // each iteration, hence the .ToList()

    if (log.PriorValue != log.Value) // check to see if the prior value equals to new value
    {                                // I am only interested in changing values, so delete the log entry
        foreach (var ruleSet in ruleSets.Where(x => x.OpcTags.Logs.OpcTagValueLogs.OpcTagId == log.OpcTagId))
        {
            if (ruleSet.CheckRule(log.PriorValue, log.Value))
            {
                // perform action
                // convert source timestamp to datetime
                DateTime srcTS = new DateTime(1970, 1, 1).AddSeconds(log.SourceTimeStamp);
                var action = ActionFactory.CreateAction(ruleSet.Action, log.PriorValue, log.Value, log.OpcTag, srcTS);
                action.Execute();
            }
        }                      
    }

    // The below was common to both the if and else condition, hence it is moved at the end of the conditional
    // remove the entity
    _context.OpcTagValueLogs.Remove(log);
    logs.Remove(log);
}

// Call save changes once (less I/O)
_context.SaveChanges();

我不知道类的定义，所以您必须相应地修改代码，特别是对于foreach (var ruleSet in ruleSets.Where(x => x.OpcTags.Logs.OpcTagValueLogs.OpcTagId == log.OpcTagId))行。

我还重构了一些常见的代码，因为它没有意义，但如果您认为它是错误的，您可以纠正它。

这是斯坦提到的框架。这是一个很好的框架，以帮助您优化使用EF。

此外，确定正在发生的事情的最好方法是在服务运行时运行Server事件探查器以查找错误的查询。

Answer 2

当数据库可以在一个事务中处理时，我认为做所有这些检查和多次访问数据库都浪费了很多时间。

几个选择。我知道您正在使用EF，但是在您的环境中存在使用Stored Procedure的选项吗？如果是这样的话，您可以使用一个MERGE语句，这样您就只能访问数据库一次。

另一种选择是在您的DbContext上创建一个扩展方法，它的作用类似于一个UPSERT (read：MERGE)。我刚遇到了这个类，它为你设置它，当你想看看你是否能让EF做上半身。

https://gist.github.com/ondravondra/4001192