执行 C# 代码需要更多时间
本文关键字：时间 代码 执行 | 更新日期: 2023-09-27 18:37:15
我是C#的新手，我正在使用Linq在C#中对datatable进行一些操作。我的方法大约需要 35 秒才能完成处理。有人可以建议如何提高性能吗？
该方法的输入数据表具有 35 个 KPI，每个 KPI 中将包含每个会计年度和每个国家/地区的 13 个月数据。因此，dt 每个会计年度将有 35*13 = 455 条记录。无论如何，将有2个财政年度。因此，每个国家/地区 455*2 = 900 条记录。因此，我们在输入数据表中将记录数定为小于 1000
void NeedtoImprovePerformance(DataTable dt)
{
DataView dv = dt.DefaultView;
dv.Sort = "Fiscal_Year ASC";
dt = dv.ToTable();
var kpilist = from table in dt.AsEnumerable()
orderby table.Field<int>("Sub_Service_Type_Id")
group table by new { kpiName = table["KpiName"] } into groupby
select new
{
value = groupby.Key,
columnvalues = groupby
};
var uniqueCountry = from table in dt.AsEnumerable()
group table by new { country = table["CountryName"] } into groupby
select new
{
value = groupby.Key,
columnValues = groupby
};
foreach (var kpi in kpilist)
{
var KpiValues = from table in dt.AsEnumerable()
where table.Field<string>("KpiName") == kpi.value.kpiName.ToString()
select table;
foreach (var countryName in uniqueCountry)
{
var availablePeriods = from table in dt.AsEnumerable()
where table.Field<string>("KpiName").ToString() == kpi.value.kpiName.ToString() &&
table.Field<string>("CountryName").ToString() == countryName.value.country.ToString()
select table.Field<int>("Period").ToString();
if (availablePeriods.Count() < 13 && availablePeriods.Count() > 0)
{
for (int period = 5; period < 17; period++)
{
DataRow dr = dt.NewRow();
if (!availablePeriods.ToList().Contains(period.ToString()))
{
dr[1] = KpiValues.ToList()[0]["KpiName"].ToString();
dr[2] = countryName.value.country.ToString();
dr[3] = (period).ToString();
var availableFiscalYear = from table in dt.AsEnumerable()
where table.Field<int>("Period").ToString() == period.ToString()
select table.Field<string>("Fiscal_Year").ToString();
if (availableFiscalYear == null || availableFiscalYear.Count() == 0)
{
if (ddlFiscalYear.SelectedItem.Text == ddlFiscalYear.Items[ddlFiscalYear.Items.Count - 1].Text)
{
if (period > 10)
dr[4] = ddlFiscalYear.SelectedValue.ToString();
else
dr[4] = (Convert.ToInt32(ddlFiscalYear.SelectedValue) - 1).ToString();
if (period == Convert.ToInt32(ddlMonth.SelectedValue) || period < Convert.ToInt32(ddlMonth.SelectedValue))
dr[4] = (Convert.ToInt32(ddlFiscalYear.SelectedValue) + 1).ToString();
}
else
{
if (period > Convert.ToInt32(ddlMonth.SelectedValue))
dr[4] = (Convert.ToInt32(ddlFiscalYear.SelectedValue) - 1).ToString();
else
{
if (period == Convert.ToInt32(ddlMonth.SelectedValue))
dr[4] = (Convert.ToInt32(ddlFiscalYear.SelectedValue) + 1).ToString();
else
dr[4] = ddlFiscalYear.SelectedValue.ToString();
}
}
}
else
{
dr[4] = availableFiscalYear.ToList()[0].ToString();
}
dr[5] = "";
dr[6] = KpiValues.ToList()[0]["Frequency"].ToString();
dr[7] = KpiValues.ToList()[0]["Sub_Service_Type_Id"].ToString();
dr[8] = KpiValues.ToList()[0]["Service_Type_Id"].ToString();
dr[9] = KpiValues.ToList()[0]["ServiceName"].ToString();
dr[10] = KpiValues.ToList()[0]["OrderBy"];
dt.Rows.Add(dr);
}
if (ddlMonth.SelectedValue == period.ToString())
{
var selectedPeriod = availablePeriods.Where(k => k == ddlMonth.SelectedValue).ToList();
if (selectedPeriod != null && selectedPeriod.Count == 1)
{
var PeriodFiscalYear = from table in dt.AsEnumerable()
where table.Field<string>("KpiName").ToString() == kpi.value.kpiName.ToString() &&
table.Field<string>("CountryName").ToString() == countryName.value.country.ToString()
&& table.Field<int>("Period") == period
select table.Field<string>("Fiscal_Year");
dr = null;
dr = dt.NewRow();
dr[1] = KpiValues.ToList()[0]["KpiName"].ToString();
dr[2] = countryName.value.country.ToString();
dr[3] = (period).ToString();
var availableFiscalYear = from table in dt.AsEnumerable()
where table.Field<int>("Period").ToString() == period.ToString() &&
table.Field<string>("Fiscal_Year").ToString() != PeriodFiscalYear.ToList()[0]
select table.Field<string>("Fiscal_Year").ToString();
if (availableFiscalYear == null || availableFiscalYear.Count() == 0)
{
if (ddlFiscalYear.SelectedItem.Text == ddlFiscalYear.Items[ddlFiscalYear.Items.Count - 1].Text)
{
if (period > 10)
dr[4] = ddlFiscalYear.SelectedValue.ToString();
else
dr[4] = (Convert.ToInt32(ddlFiscalYear.SelectedValue) - 1).ToString();
if (period == Convert.ToInt32(ddlMonth.SelectedValue) || period < Convert.ToInt32(ddlMonth.SelectedValue))
dr[4] = (Convert.ToInt32(ddlFiscalYear.SelectedValue) + 1).ToString();
}
else
{
if (period > Convert.ToInt32(ddlMonth.SelectedValue))
dr[4] = (Convert.ToInt32(ddlFiscalYear.SelectedValue) - 1).ToString();
else
{
if (period == Convert.ToInt32(ddlMonth.SelectedValue))
dr[4] = (Convert.ToInt32(ddlFiscalYear.SelectedValue) + 1).ToString();
else
dr[4] = ddlFiscalYear.SelectedValue.ToString();
}
}
}
else
{
dr[4] = availableFiscalYear.ToList()[0].ToString();
}
dr[5] = "";
dr[6] = KpiValues.ToList()[0]["Frequency"].ToString();
dr[7] = KpiValues.ToList()[0]["Sub_Service_Type_Id"].ToString();
dr[8] = KpiValues.ToList()[0]["Service_Type_Id"].ToString();
dr[9] = KpiValues.ToList()[0]["ServiceName"].ToString();
dr[10] = KpiValues.ToList()[0]["OrderBy"];
dt.Rows.Add(dr);
}
}
}
}
}
}
dv = dt.DefaultView;
dv.Sort = "KpiName ASC, Fiscal_Year ASC, Sub_Service_Type_Id ASC";
dt = dv.ToTable();
}
谢谢
该代码示例中存在许多性能问题。一些改进：
- 像
var table = dt.AsEnumerable()一样将dt.AsEnumerable()移到两个循环之外并使用它 - 将
.ToList()KpiValues移动到第一个 foreach 并使用结果列表 - 您还多次查询
availablePeriods（2x.Count(),2x.ToList()），您可以通过调用.ToList()一次并在所有位置使用该列表来改善这一点。
一般情况下：尽量少查询。变量KpiValues和availablePeriods不是事物列表，而是查询定义。每次从中获取一些数据时，它都会执行查询以获取数据，而不是重用您可能期望的先前结果。