linq查询大量数据的延迟结果

Question

我有一个查询，我有5分钟的时间执行以下查询（总记录40k）

var assetAssociatedEvents = (from mainNodes in projectNodes
     select new OrgManagementEvent
     {
         OrgAssetsAssociatedEvent = new OrgAssetsAssociatedEvent
         {
             CustomerUID = Guid.Parse(CustomerUID),
             OrgUid = Guid.Parse(mainNodes.NodeObjectUID),
             AssociatedAssetUids = (from assets in customerAssets
                                    join assetNodes in assetNodeObjects
                                    on assets.AssetID equals assetNodes.fk_AssetID
                                    where assetNodes.fk_NodeObjectID == mainNodes.NodeObjectID
                                    select assets.AssetUID).Distinct().ToList().ConvertAll(Guid.Parse).ToArray(),
             Timestamp = timeStampDetail
         }
     }).ToList();

如果我做错了什么，你能纠正我吗，以提高表现。

Answer 1

是否需要应用所有这些转换？当您调用ToList（）时，您正在遍历整个数据集，当调用ConverAll时，您将再次执行此操作，然后调用ToArray以达到相同的效果。也许您可以考虑只使用linq部分，然后使用linq查询的结果进行操作

    (from assets in customerAssets join assetNodes in assetNodeObjects on assets.AssetID equals assetNodes.fk_AssetID where assetNodes.fk_NodeObjectID ==  mainNodes.NodeObjectID select assets.AssetUID)

我认为仅凭这一点就可以大大提高表现。

Answer 2

我认为您应该拆分查询。这样的东西应该会提高性能：

var allAssetUids = (from assets in customerAssets
                    join assetNodes in assetNodeObjects
                    on assets.AssetID equals assetNodes.fk_AssetID                                 
                    select assets.AssetUID)
                    .Distinct()
                    .ToList()
                    .ConvertAll(Guid.Parse);

然后做

var assetAssociatedEvents = (from mainNodes in projectNodes
 select new OrgManagementEvent
 {
     OrgAssetsAssociatedEvent = new OrgAssetsAssociatedEvent
     {
         CustomerUID = Guid.Parse(CustomerUID),
         OrgUid = Guid.Parse(mainNodes.NodeObjectUID),    
         Timestamp = timeStampDetail
     }
 }).ToList();

在en中迭代您的事件，如

foreach(var associatedEvent in assetAssociatedEvents){
    AssociatedAssetUids = allAssetUids.Where(p => p == associatedEvent.NodeObjectID).ToArray();
}