我的代码在执行时花费的时间太长
本文关键字：时间 代码 执行 我的 | 更新日期: 2023-09-27 18:29:57
这是我的代码
private void button1_Click(object sender, EventArgs e)
{
double prate = 0;
double srate = 0;
double rate = 0;
double qty = 0;
double val = 0; bool b = false;
List<string> item = new List<string>();
List<string> brand = new List<string>();
List<string> station = new List<string>();
List<string> dealer = new List<string>();
int m1=0, m2=0;
for (int k = 0; k < treeView1.Nodes.Count; k++)
{
for (int l = 0; l < treeView1.Nodes[k].Nodes.Count; l++)
{
if (treeView1.Nodes[k].Nodes[l].Checked == true)
{
brand.Add(treeView1.Nodes[k].Tag.ToString());
item.Add(treeView1.Nodes[k].Nodes[l].Tag.ToString());
m1++;
}
}
}
for (int i = 0; i < treeView2.Nodes.Count; i++)
{
for (int j = 0; j < treeView2.Nodes[i].Nodes.Count; j++)
{
if (treeView2.Nodes[i].Nodes[j].Checked == true)
{
station.Add(treeView2.Nodes[i].Tag.ToString());
dealer.Add(treeView2.Nodes[i].Nodes[j].Tag.ToString());
m2++;
}
}
}
string[] brands; string[] items; string[] stations; string[] dealers;
brands = brand.ToArray();
items = item.ToArray();
stations = station.ToArray();
dealers = dealer.ToArray();
SqlConnection con = new SqlConnection(Global.constr);
con.Open();
SqlCommand del = new SqlCommand("delete PLReport", con);
del.ExecuteNonQuery();
del.Dispose();
for (int i = 0; i < m1; i++)
{
prate = 0;
SqlCommand cmdr = new SqlCommand("select top 1 purchasePrice from PurchaseDetails where ItemCode='" + item[i] + "' order by doc_date Desc", con);
SqlDataReader drr = cmdr.ExecuteReader();
while (drr.Read())
{
prate = Convert.ToDouble(drr[0]);
}
drr.Close();
cmdr.Dispose();
for (int j = 0; j < m2; j++)
{
SqlCommand cmd33 = new SqlCommand("select slno from SalesDetails where ItemCode='" + items[i] + "' and dealer='" + dealers[j] + "' and date between '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' and '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "'", con);
SqlDataReader r33 = cmd33.ExecuteReader();
if (!r33.HasRows)
continue;
r33.Close();
cmd33.Dispose();
SqlCommand cmd = new SqlCommand("select sum(Qty),sum(rate) from SalesDetails where ItemCode='" + items[i] + "' and dealer='" + dealers[j] + "' and date between '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' and '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' group by ItemCode,Dealer", con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//MessageBox.Show("fgfg");
srate = 0;
b = double.TryParse(dr[1].ToString(), out srate);
rate = 0;
if (srate != 0)
rate = srate - prate;
qty = 0;
b = double.TryParse(dr[0].ToString(), out qty);
val = qty * rate;
if (checkBox3.Checked)//zero valued entry
{
SqlCommand cmd2 = new SqlCommand("insert PLReport values('" + stations[j] + "','" + dealers[j] + "','" + brands[i] + "','" + items[i] + "','" + val + "')", con);
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}
else
{
if (val != 0)
{
SqlCommand cmd2 = new SqlCommand("insert PLReport values('" + stations[j] + "','" + dealers[j] + "','" + brands[i] + "','" + items[i] + "','" + val + "')", con);
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}
}
}
dr.Close();
cmd.Dispose();
}
}
frmPLReport obj = new frmPLReport();
obj.ttle = "Item Wise Profit & Loss report from " + dateTimePicker1.Value.ToString("dd/MM/yyyy") + " to " + dateTimePicker2.Value.ToString("dd/MM/yyyy");
obj.option = 0;
obj.ShowDialog();
con.Close();
}
一个"i"循环大约需要4秒才能执行，我有大约8000条记录（项目）1000+（经销商）400个条件。
这部分需要很长时间才能执行：
SqlCommand cmd = new SqlCommand("select sum(Qty),sum(rate) from SalesDetails where ItemCode='" + items[i] + "' and dealer='" + dealers[j] + "' and date between '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' and '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' group by ItemCode,Dealer", con);
SqlDataReader dr = cmd.ExecuteReader();
如何解决这个问题时间太长了。在我的表结构中不允许索引。任何其他解决方案都会有所帮助。
首先，我不明白对DB的查询是花费了太长时间，还是函数的其余部分，或者两者兼而有之。
如果你不知道，你应该从测试开始。你可以使用一个简单的DateTime.Now，看看每个部分花了多少时间。
如果你想用更好的方法测试它，你可以像V4Vendetta所说的那样使用StopWatch。你可以在这里找到一个例子，基本上合成文本是这样的：
// Create new stopwatch
Stopwatch stopwatch = new Stopwatch();
// Begin timing
stopwatch.Start();
// YOUR CODE HERE
// Stop timing
stopwatch.Stop();
// Write result
Console.WriteLine("Time elapsed: {0}",
stopwatch.Elapsed);
由于数据库访问时间太长，我建议您不要调用太多sql查询。我看到在for循环中，您正在调用：
"从SalesDetails中选择slno，其中…"
获取所有的表并使用linq可能会更好。这样可以节省大量的DB调用。其余的DB查询也是如此，我认为我们最多可以使用3-5个DB调用。