我正在开发无线应用程序，当我从SQL Server 2008查询2000+行时，会发生超时异常。
我在循环程序中使用了很多 linq。当我评论其中一些时，它会更好，但仍然会发生。
Linq 是否会导致性能降低？还有其他原因导致此问题吗？我该如何解决它？
谢谢。
enter code here
var listShipUnit = from ShipUnit su in bdd.ShipUnit
where (su.ShipmentID == shipmentNumber
&& (su.QStatus != "1" || su.QStatus == null)
&& (!su.CaseID.Equals(null)
&& !su.CaseID.ToUpper().Equals("UNKNOWN") &&
!su.CaseID.Equals(string.Empty) &&
!su.CaseID.ToUpper().Equals("NULL")
&& su.CaseID.Length > 0))
group su by su.CaseID into gsu
select new
{
gsu.Key,
gsu,
TotalWeight = gsu.Sum(w => w.Weight),
TotalVolume = gsu.Sum(v => v.Volume),
TotalCount = gsu.Sum(v => v.ShipUnitCount)
};
foreach (var shipUnit in listShipUnit)
{
tempListShipUnitGroup.Add(new ShipUnitGroupSTL(shipUnit.gsu.ToList(), shipUnit.Key, shipUnit.TotalVolume.ToString(), shipUnit.TotalWeight.ToString(), shipUnit.TotalCount.ToString()));
}
List<ShipUnitGroupSTL> revShipUnitGroup = new List<ShipUnitGroupSTL>();
List<string> finishPallet = new List<string>();
List<string> finishCase = new List<string>();
// 2013.12.19 device null exception ---> start
var stlunits = bdd.ScanToLoad;
List<ScanToLoad> stlList = stlunits.ToList();
//var shipunits = bdd.ShipUnit;
//List<ShipUnit> shipunitList = shipunits.ToList();
if (stlList != null && stlList.Count() > 0)
// 2013.12.19 device null exception --- end
{
//// get the recently record from STL talbe
foreach (var shipUnitGroup in tempListShipUnitGroup)
{
string tempCaseID;
string tempPalletID;
var unit = shipUnitGroup.GroupShipUnit;
tempCaseID = shipUnitGroup.GroupKey;
tempPalletID = unit.FirstOrDefault().PalletID;
// // 2013.12.19 device null exception ---> start
// // look up the caseId in the STL table
var stlunit = stlList
.Where(s => s.CaseID.Equals(tempCaseID))
.OrderByDescending(s => s.UpdateDate);
// //var stlunit = bdd.ScanToLoad
// // .Where(s => s.ScanToLoadID.Equals(tempCaseID)
// // || s.CaseID.Equals(tempCaseID))
// // .OrderByDescending(s => s.UpdateDate);
// // 2013.12.19 device null exception ---> end
if (stlunit != null && stlunit.Count() > 0)
{
string stlPallet = null;
stlPallet = stlunit.FirstOrDefault().NewPalletID;
if (!string.IsNullOrEmpty(stlPallet))
{
if (tempPalletID.Equals(stlPallet))
{
finalListShipUnitGroup.Add(shipUnitGroup);
//continue;
}
else
{
var revShipUnit = bdd.ShipUnit
.Where(su => su.ShipmentID == shipmentNumber
&& (su.QStatus != "1" || su.QStatus == null)
&& su.PalletID.Equals(stlPallet));
if (revShipUnit != null && revShipUnit.Count() > 0)
{
unit.FirstOrDefault().PalletID = stlPallet;
finalListShipUnitGroup.Add(shipUnitGroup);
}
}
}
else
{
finalListShipUnitGroup.Add(shipUnitGroup);
//continue;
}
}
else
{
finalListShipUnitGroup.Add(shipUnitGroup);
//continue;
}
finishCase.Add(tempCaseID);
var checkFinshPallet = finishPallet
.Contains(tempPalletID);
if (checkFinshPallet)
{
continue;
}
finishPallet.Add(tempPalletID);
}
foreach (var tempPalletID in finishPallet)
{
// look up the pallet in the STL table, add others new caseId in STL table
var stlunitPal = stlList
.Where(s => s.NewPalletID.Equals(tempPalletID))
.OrderByDescending(s => s.UpdateDate);
if (stlunitPal != null && stlunitPal.Count() > 0)
{
//IEnumerable<MobilePlusServer.ShipUnit> newCaseList;
string stlPalletID;
// add and remove case from the Pallet
foreach (var s in stlunitPal)
{
stlPalletID = s.NewPalletID;
//var addSus = from ShipUnit su in bdd.ShipUnit
// where (su.CaseID.Equals(s.CaseID) && su.RecordID.Equals(s.CaseRecordID))
// && (su.QStatus != "1" || su.QStatus == null)
// group su by su.CaseID into gsu
// select new
// {
// gsu.Key,
// gsu,
// TotalWeight = gsu.Sum(w => w.Weight),
// TotalVolume = gsu.Sum(v => v.Volume),
// TotalCount = gsu.Sum(v => v.ShipUnitCount)
// };
var addSus = from ShipUnit su in bdd.ShipUnit
where (su.CaseID.Equals(s.CaseID) && su.RecordID.Equals(s.CaseRecordID)
&& (su.QStatus != "1" || su.QStatus == null))
select su;
if (addSus != null && addSus.Count() > 0)
{
var addSu = addSus.FirstOrDefault();
// get the remove pallet id
string adShipmentID = addSu.ShipmentID;
string adPalletID = addSu.PalletID;
//string adRecordID = unit.FirstOrDefault().RecordID.ToString();
//string adCaseID = unit.FirstOrDefault().RecordID.ToString();
if (!shipmentNumber.Equals(adShipmentID)
&& !finishCase.Contains(s.CaseID))
{
// set new pallet id
addSu.PalletID = stlPalletID;
finalListShipUnitGroup.Add(new ShipUnitGroupSTL(
addSus.ToList(),
s.CaseID,
addSu.Volume.ToString(),
addSu.Weight.ToString(),
addSu.ShipUnitCount.ToString()));
}
}
}
}
}
}
// 2013.12.19 device null exception ---> start
else
{
finalListShipUnitGroup = tempListShipUnitGroup;
}
您可以使用
SQL 上的索引来获取更快的查询我建议在连接字段上使用索引
在此处和此处和此处查看此文档
有如此复杂的查询，很容易被解释为设计不佳的迹象（无论是在查询中还是在数据库中）。我建议将您的查询分解为较小的查询，然后编写它们。这将引导您获得更清晰的代码和逻辑。然后，您可以使用 SQL Server 探查器查看执行的确切 sql 查询，并查看延迟是否是由于 Linq 到实体和 SQL 之间的转换造成的。如果您确定无法使查询更简单，则可以在SQL Server级别使用索引
希望我有帮助！