c# - 将 sql 合并到存储过程中以获得更好的性能
问题描述
我仍然在我们的 38,000 个条目的列表中遇到真正的性能问题，我需要将它们映射到另一个表以进行导出我正在考虑将其移动到存储的 proc 但仍然担心那里的性能会是什么样子。该代码有效，但需要很长时间才能执行并希望将其转换为存储过程。
private List<TradeItemsExport> MapTradeItems(List<TradeItems> tradeItem)
{
List<TradeItemsExport> retList = new List<TradeItemsExport>();
try
{
var StockImport = new StockItemExported();
List<StandardLookUpList> _AnalsisCodes = GetAnayalsisCodesForExportCode();
StandardLookUpList sport = new StandardLookUpList();
StandardLookUpList gender = new StandardLookUpList();
StandardLookUpList colour = new StandardLookUpList();
StandardLookUpList Size = new StandardLookUpList();
StandardLookUpList categorycode = new StandardLookUpList();
StandardLookUpList categorydesc = new StandardLookUpList();
StandardLookUpList subcategorycode = new StandardLookUpList();
StandardLookUpList subcategorydesc = new StandardLookUpList();
StandardLookUpList brandcode = new StandardLookUpList();
StandardLookUpList branddesc = new StandardLookUpList();
using (var db = new liveEntities1())
{
int count = 0;
foreach (var item in tradeItem)
{
count++;
bool hasprocessed = HasTransactionBeenProcessed(item.ItemCode);
if (hasprocessed == false)
{
var codesForThisItem = _AnalsisCodes.Where(w => w.ItemCode == item.ItemCode);
if (codesForThisItem.Any())
{
sport = codesForThisItem.FirstOrDefault(x => x.code == Constants.Sport);
gender = codesForThisItem.FirstOrDefault(x => x.code == Constants.Gender);
colour = codesForThisItem.FirstOrDefault(x => x.code == Constants.Colour);
Size = codesForThisItem.FirstOrDefault(x => x.code == Constants.Size);
categorycode = codesForThisItem.FirstOrDefault(x => x.code == Constants.Category);
categorydesc = codesForThisItem.FirstOrDefault(x => x.code == Constants.Category);
subcategorycode = codesForThisItem.FirstOrDefault(x => x.code == Constants.SubCategory);
subcategorydesc = codesForThisItem.FirstOrDefault(x => x.code == Constants.SubCategory);
brandcode = codesForThisItem.FirstOrDefault(x => x.code == Constants.Brand);
string SportCodeValue, SportDescValue;
if (sport == null)
{
SportCodeValue = "";
SportDescValue = "";
}
else
{
SportCodeValue = sport.LookupValue.ToString();
SportDescValue = sport.description;
}
string GenderCodeValue, GenderCodeDesc;
if (gender == null)
{
GenderCodeValue = "";
GenderCodeDesc = "";
}
else
{
GenderCodeValue = gender.LookupValue.ToString();
GenderCodeDesc = gender.description;
}
string ColourCodeValue, ColourCodeDesc;
if (colour == null)
{
ColourCodeValue = "";
ColourCodeDesc = "";
}
else
{
ColourCodeValue = colour.LookupValue.ToString();
ColourCodeDesc = colour.description;
}
string SizeCodeValue, SizeCodeDesc;
if (Size == null)
{
SizeCodeValue = "";
SizeCodeDesc = "";
}
else
{
SizeCodeValue = Size.LookupValue.ToString();
SizeCodeDesc = Size.description;
}
string CategoryCodeValue, CategoryCodeDesc;
if (categorycode == null)
{
CategoryCodeValue = "";
CategoryCodeDesc = "";
}
else
{
CategoryCodeValue = categorycode.LookupValue.ToString();
CategoryCodeDesc = categorydesc.description;
}
string subcategorycodevalue, subcategorycodedesc;
if (categorycode == null)
{
subcategorycodevalue = "";
subcategorycodedesc = "";
}
else
{
subcategorycodevalue = subcategorycode.LookupValue.ToString();
subcategorycodedesc = subcategorydesc.description;
}
string brandcodecodevalue, brandcodecodedesc;
if (brandcode == null)
{
brandcodecodevalue = "";
brandcodecodedesc = "";
}
else
{
brandcodecodevalue = brandcode.LookupValue.ToString();
brandcodecodedesc = brandcode.description;
}
retList.Add(new TradeItemsExport()
{
ItemCode = item.ItemCode,
BarCode = item.BarCode,
Description = item.Description,
SupplierCode = item.SupplierCode,
SupplierStockCode = item.SupplierStockCode,
Product_Group_Code = "",
Product_Group_Desc = "",
SportCode = SportCodeValue,
SportDesc = SportDescValue,
GenderCode = GenderCodeValue,
GenderDesc = GenderCodeDesc,
ColourCode = ColourCodeValue,
ColourDesc = ColourCodeDesc,
SizeCode = SizeCodeValue,
SizeDesc = SizeCodeDesc,
CategoryCode = CategoryCodeValue,
CategoryDesc = CategoryCodeDesc,
subcategorycode = subcategorycodevalue,
subcategorydesc = subcategorycodedesc,
BrandsCode = brandcodecodevalue,
BrandsDesc = brandcodecodedesc,
Vat = item.Vat,
GrossWeight = item.Weight,
CommodityCode = item.CommodityCode,
price_exVAT = item.price_exVAT,
price_incVAT = item.price_incVAT,
currentprice_exVAT = item.currentprice_exVAT,
currentprice_incVAT = item.currentprice_incVAT,
creation_date = item.creation_date,
Inactive_date = item.Inactive_date,
status = 1
});
Console.Write(String.Format("Exporting stock item {0} with a current record of {1} of {2} \n", item.ItemCode.ToString(), count.ToString(), tradeItem.Count.ToString()));
EFStockItemExported _newStockitemImported = new EFStockItemExported();
_newStockitemImported.StockItemID = item.ItemCode;
_newStockitemImported.IsProcessed = true;
_newStockitemImported.DateImported = DateTime.Now;
db.EFStockItemExporteds.Add(_newStockitemImported);
db.SaveChanges();
}
else
{
Console.Write(string.Format("Stock Items to Process [{0}] check the table and remove entry if wish to re process.", 0));
}
}
}
}
}
catch (Exception ex)
{
}
return retList;
}
我的问题是这需要大约 30 分钟才能计算出非常慢的结果。
这是我正在做的 sql，它是我正在传递的贸易项目的视图。
SELECT
dbo.PLSupplierAccount.SupplierAccountNumber, dbo.PLSupplierAccount.PLSupplierAccountID, dbo.PLSupplierAccount.SupplierAccountName,
dbo.PLSupplierAccount.SYSCurrencyID, dbo.PLSupplierAccount.MainTelephoneAreaCode, dbo.PLSupplierAccount.MainTelephoneCountryCode,
dbo.PLSupplierAccount.MainTelephoneSubscriberNumber, dbo.PLSupplierAccount.MainFaxCountryCode, dbo.PLSupplierAccount.MainFaxSubscriberNumber,
dbo.PLSupplierAccount.MainFaxAreaCode, dbo.PLSupplierContact.ContactName, dbo.PLSupplierContact.Description, dbo.PLSupplierContact.FirstName,
dbo.PLSupplierContact.MiddleName, dbo.PLSupplierContact.LastName, loc.AddressLine1, loc.AddressLine2, loc.AddressLine3, loc.AddressLine4, loc.PostCode,
loc.City, loc.County,
CAST(CASE WHEN loc.Country = 'Ireland' THEN 'IRL'
WHEN loc.Country = 'Great Britain'
THEN 'GBR'
ELSE 'ERR'
END AS nvarchar(3)) AS Country,
dbo.SYSCurrency.SYSCurrencyISOCodeID, dbo.SYSCurrency.SYSExchangeRateTypeID, dbo.SYSCurrency.Name AS CurrencyDescription,
dbo.SYSCurrency.Symbol AS CurrencySymbol
FROM
dbo.PLSupplierAccount
INNER JOIN
dbo.PLSupplierContact ON dbo.PLSupplierAccount.PLSupplierAccountID = dbo.PLSupplierContact.PLSupplierAccountID
INNER JOIN
dbo.PLSupplierLocation AS loc ON dbo.PLSupplierAccount.PLSupplierAccountID = loc.PLSupplierAccountID
AND dbo.PLSupplierContact.PLSupplierLocationID = loc.PLSupplierLocationID
INNER JOIN
dbo.SYSCurrency ON dbo.PLSupplierAccount.SYSCurrencyID = dbo.SYSCurrency.SYSCurrencyID
我的问题是我将如何更改上面的内容以包含一个子查询，该子查询的作用与上面的函数相同。
下面是另一个视图的代码查询查询。
SELECT
dbo.StockItem.ItemID, dbo.StockItem.Code, dbo.StockItem.Name, dbo.StockItemSearchCatVal.SearchValueID, dbo.SearchValue.Name AS Expr1,
dbo.SearchCategory.Name AS Expr2
FROM
dbo.SearchCategory
INNER JOIN
dbo.SearchValue ON dbo.SearchCategory.SearchCategoryID = dbo.SearchValue.SearchCategoryID
INNER JOIN
dbo.StockItemSearchCatVal ON dbo.SearchCategory.SearchCategoryID = dbo.StockItemSearchCatVal.SearchCategoryID
AND dbo.SearchValue.SearchValueID = dbo.StockItemSearchCatVal.SearchValueID
INNER JOIN
dbo.StockItem ON dbo.StockItemSearchCatVal.ItemID = dbo.StockItem.ItemID
我只是觉得将其更改为子查询会获得更多好处，因此我只是将结果返回到.net 我正在使用 filehelpers 库将 MapTradeItems 的结果集输出到 csv 所以 obv 更多的事情我可以做得更好服务器。
Obv 我需要某种临时表来循环遍历结果，但是与每个循环的 .net 相比，sql server 中的速度有多快。
这是我必须复制到 csv 的 poco 类。
[DelimitedRecord(",")]
public class TradeItemsExport
{
[FieldOrder(1)]
public string ItemCode { get; set; }
[FieldOrder(2)]
public string BarCode { get; set; }
[FieldOrder(3)]
public string Description { get; set; }
[FieldOrder(4)]
public string SupplierCode { get; set; }
[FieldOrder(5)]
public string SupplierStockCode { get; set; }
[FieldOrder(6)]
public string Product_Group_Code { get; set; }
[FieldOrder(7)]
public string Product_Group_Desc { get; set; }
[FieldOrder(8)]
public string SportCode { get; set; }
[FieldOrder(9)]
public string SportDesc { get; set; }
[FieldOrder(10)]
public string GenderCode { get; set; }
[FieldOrder(11)]
public string GenderDesc { get; set; }
[FieldOrder(12)]
public string ColourCode { get; set; }
[FieldOrder(13)]
public string ColourDesc { get; set; }
[FieldOrder(14)]
public string SizeCode { get; set; }
[FieldOrder(15)]
public string SizeDesc { get; set; }
[FieldOrder(16)]
public string CategoryCode { get; set; }
[FieldOrder(17)]
public string CategoryDesc { get; set; }
[FieldOrder(18)]
public string subcategorycode { get; set; }
[FieldOrder(19)]
public string subcategorydesc { get; set; }
[FieldOrder(20)]
public string BrandsCode { get; set; }
[FieldOrder(21)]
public string BrandsDesc { get; set; }
[FieldOrder(22)]
public Nullable<short> Vat { get; set; }
[FieldOrder(23)]
public decimal GrossWeight { get; set; }
[FieldOrder(24)]
public string CommodityCode { get; set; }
[FieldOrder(25)]
public decimal price_exVAT { get; set; }
[FieldOrder(26)]
public Nullable<decimal> price_incVAT { get; set; }
[FieldOrder(27)]
public Nullable<decimal> currentprice_exVAT { get; set; }
[FieldOrder(28)]
public Nullable<decimal> currentprice_incVAT { get; set; }
[FieldOrder(29)]
public System.DateTime creation_date { get; set; }
[FieldOrder(30)]
public Nullable<System.DateTime> Inactive_date { get; set; }
[FieldOrder(31)]
public int status { get; set; }
}
解决方案
推荐阅读
- c# - 如何在 EF 上启用身份插入 - 代码首先是 context.Seed()，然后在数据播种后再次禁用
- wordpress - 合并网站、图片双ID问题
- wicket - 如何在 Wicket 中从 tinyMCE 设置宽度
- go - 无法在 Go 应用程序的另一个包中使用函数
- firefox - Mozilla firefox 忽略缓存控制标头
- node.js - 如何处理 Selenium Webdriver Node Js 中的错误。NoSuchElementError
- sql - SQL Server：基于父子关系拆分列
- python - 有没有可以去除异常值的功能？
- c# - 防止用户拥有多个并发会话
- azure - 从 ARM 模板部署逻辑应用自定义连接器时设置身份验证