真爱无限的知识驿站

思路：一次不能联表查太多数据

旧代码：

--receiptIds   id集合，每次查1000左右

var mappingList = new List<FinanceSettlementMapping>();

mappingList.AddRange(SettlementMappingRepository.Entities.Include(o => o.ReceiptsTo).Include(d => d.FinanceSettlement).Where(d => receiptIds.Contains(d.ReceiptIdFrom)).ToList());

mappingList.AddRange(SettlementMappingRepository.Entities.Include(o => o.ReceiptsTo).Include(d => d.FinanceSettlement).Where(d => receiptIds.Contains(d.ReceiptIdTo)).ToList());

--FinanceSettlementMapping -500w数据，9个字段

--ReceiptsTo，外键表，FinanceReceipts --800w数据，64个字段

--FinanceSettlement  --200w数据，15个字段

//FinanceSettlementMapping join FinanceSettlement join FinanceReceipts  表数据越大，连接越慢，可能会超时

新代码：

外键表FinanceReceipts数据单独查询

var mappingList = new List<FinanceSettlementMapping>();

var settlementmapFromList = SettlementMappingRepository.Entities.Include(d => d.FinanceSettlement).Where(d => receiptIds.Contains(d.ReceiptIdFrom)).ToList();

if (settlementmapFromList.Count > 0)

{

var toreceiptIds = settlementmapFromList.Select(o => o.ReceiptIdTo).ToArray();

var toreceipts = ReceiptsRepository.Entities.Where(o => toreceiptIds.Contains(o.ReceiptId)).ToList();//查询数据到内存

foreach (var map in settlementmapFromList)//循环内存数据赋值

{

var tempreceipt = toreceipts.Where(o => o.ReceiptId == map.ReceiptIdTo).FirstOrDefault();

map.ReceiptsTo = tempreceipt;

}

}

mappingList.AddRange(settlementmapFromList);

var settlementmapToList = SettlementMappingRepository.Entities.Include(d => d.FinanceSettlement).Where(d => receiptIds.Contains(d.ReceiptIdTo)).ToList();

if (settlementmapToList.Count > 0)

{

var toreceiptIds = settlementmapToList.Select(o => o.ReceiptIdTo).ToArray();

var toreceipts = ReceiptsRepository.Entities.Where(o => toreceiptIds.Contains(o.ReceiptId)).ToList();//查询数据到内存

foreach (var map in settlementmapToList)//循环内存数据赋值

{

var tempreceipt = toreceipts.Where(o => o.ReceiptId == map.ReceiptIdTo).FirstOrDefault();

map.ReceiptsTo = tempreceipt;

}

}

mappingList.AddRange(settlementmapToList);

分析：

    1000条数据，最差的线性查询时间复杂度 （1+1000）*1000/2 ,本次查询的是Id,是数字类型，如果系统用的是二叉树或其他快速查询方法，就更快，再者，查询到之后，把toreceipts 值赋给map.ReceiptsTo，只是修改了map.ReceiptsTo的指针地址，没有赋值操作，当然也是很快的，实测1000数据0.006s。

   解决了大数据量表的Join查询慢的问题，能加快程序总体运行速度，因为，我们网站应用 ，大部分时间是耗在数据库操作上，所以能减少数据库查询时间，就能减少完成一个操作的整体运行时间。