如何读取大型IEnumerable<>快

本文关键字：IEnumerable 大型 何读取 读取 | 更新日期: 2023-09-27 18:09:10

我有下面的代码从数据库返回22,000,000条记录非常快:

var records = from row in dataContext.LogicalMapTable
                          select
                              new
                              {
                                  row.FwId,
                                  row.LpDefId,
                                  row.FlDefMapID
                              };

上面数据库调用之后的代码需要60秒运行:

var cache = new Dictionary<string, int>();
foreach (var record in records)
{
   var tempHashCode = record.FirmwareVersionID + "." + record.LogicalParameterDefinitionID;
   cache.Add(tempHashCode, record.FirmwareLogicalDefinitionMapID);
}
return cache;

是否有更好的方法来提高性能?

如何读取大型IEnumerable<>快

代码的第二部分并不慢。它只会导致LINQ查询求值，您可以通过之前的查询看到这一点，例如

var records = (from row in dataContext.LogicalMapTable
                          select
                              new
                              {
                                  row.FwId,
                                  row.LpDefId,
                                  row.FlDefMapID
                              }).ToList();

所以是你的LINQ查询很慢，这里是你如何修复它的方法。

你可能不需要在内存中缓存22M条记录。你可以试试:

  • 分页(取，跳过)
  • 更改查询以包含特定的id或其他列。例如:select * ...之前，select * ... where id in (1,2,3) ...之后
  • 做大部分的分析工作在数据库，它是快速的，不占用你的应用程序内存
  • 偏好快速带来小批量数据的查询。您可以同时运行其中的几个来更新UI的不同位

正如其他人在评论中提到的那样，像这样读取整个列表是非常低效的。

根据你发布的代码，我假设列表加载到你的"缓存"后，你查找FirmwareLogicalDefinitionMapID使用FirmwareVersionID +"的关键。"+ LogicalParameterDefinitionID;

我的建议是提高整体性能和内存使用是实现一个实际的缓存模式，像这样:

public static class CacheHelper
{
    public static readonly object _SyncLock = new object();
    public static readonly _MemoryCache = MemoryCache.Default;
    public static int GetFirmwareLogicalDefinitionMapID(int firmwareVersionID, int logicalParameterDefinitionID)
    {
        int result = -1;
        // Build up the cache key
        string cacheKey = string.Format("{0}.{1}", firmwareVersionID, logicalParameterDefinitionID);
        // Check if the object is in the cache already
        if(_MemoryCache.Countains(cacheKey))
        {
            // It is, so read it and type cast it
            object cacheObject = _MemoryCache[cacheKey];
            if(cacheObject is int)
            {
                result = (int)cacheObject;
            }
        }
        else
        {
            // The object is not in cache, aquire a sync lock for thread safety         
            lock(_SyncLock)
            {
                // Double check that the object hasnt been put into the cache by another thread.
                if(!_MemoryCache.Countains(cacheKey))
                {
                    // Still not there, now Query the database
                    result = (from i in dataContext.LogicalMapTable
                              where i.FwId == firmwareVersionID && i.LpDefId == logicalParameterDefinitionID
                              select i.FlDefMapID).FirstOrDefault();
                     // Add the results to the cache so that the next operation that asks for this object can read it from ram
                    _MemoryCache.Add(new CacheItem(cacheKey, result), new CacheItemPolicy() { SlidingExpiration = new TimeSpan(0, 5, 0) });
                }
                else
                {
                    // we lost a concurrency race to read the object from source, its in the cache now so read it from there.
                    object cacheObject = _MemoryCache[cacheKey];
                    if(cacheObject is int)
                    {
                        result = (int)cacheObject;
                    }
                }
            }
        }
        // return the results
        return result;
    }
}

你还应该阅读。net MemoryCache: http://www.codeproject.com/Articles/290935/Using-MemoryCache-in-Net-4-0

希望这对你有帮助!

相关文章：