页面有3个input text:厂别(fact_no),部门(sec_no ),日期(txtDate)
根据这3个条件,从相关表按照以下方式查询这些出数据.不良項目 返修數(雙) 前三周(%) 前兩周(%) 上周(%) 本周(%) 上月(%) 本月(%)
鞋頭不貼楦 1970 12.24 18.38 19.45 16.81 12.42 16.11
沿條鬆動 1709 3.35 8.46 11 14.59 5.83 11.87主要原代码:DataTable result = new DataTable();
string sql = string.Empty;
OracleDataAccess oda = new OracleDataAccess();
ArrayList al = new ArrayList();
al = getDate(txtDate.Value);//上上上周,上上周,上周,本周
string fact_no = ddlFactNo.SelectedValue.ToString();
string sec_no = ddlSecNo.SelectedValue.ToString();
//******** 按照報表格式構造DataTable
result.Columns.Add(new DataColumn("item", typeof(String)));//序號
result.Columns.Add(new DataColumn("bad_item", typeof(string)));//不良項目
result.Columns.Add(new DataColumn("bad_qty", typeof(double)));//返修數(雙)
result.Columns.Add(new DataColumn("three_week", typeof(String)));//上上上周返修率
result.Columns.Add(new DataColumn("two_week", typeof(String)));//上上周返修率
result.Columns.Add(new DataColumn("last_week", typeof(String)));//上周返修率
result.Columns.Add(new DataColumn("this_week", typeof(String)));//本周返修率
result.Columns.Add(new DataColumn("last_month", typeof(String)));//上月返修率
result.Columns.Add(new DataColumn("this_month", typeof(String)));//本月返修率
//******** end
//****************** 取上月和本月 統計的不良項
string last_month = getMonth(-1);
string this_month = getMonth(0);
sql = "select distinct bad_no,(select BAD_DESC from A_BAD_REASON_M where fact_no=a.FACT_NO and bad_no=a.BAD_NO) as bad_name from a_sec_qc_data a where fact_no='" + ddlFactNo.SelectedValue + "' and sec_no='" + ddlSecNo.SelectedValue + "' and substr(rec_date,1,6) in ('" + last_month + "','" + this_month + "')";
DataTable tmp = new DataTable();
tmp = oda.getDataTable(sql);
string bad_no = string.Empty;
long tmp_qty = 0L;
int good_qty = 0;
int bad_qty=0;
double r = 0;
if (tmp.Rows.Count > 0)
{
for (int i = 0; i < tmp.Rows.Count; i++)
{
DataRow newdr = result.NewRow();
newdr["item"] = i.ToString();
newdr["bad_item"] = tmp.Rows[i]["bad_name"].ToString();
bad_no = tmp.Rows[i]["bad_no"].ToString();
//返修數(雙)
sql = "select NVL(sum(qty),0) as qty from a_sec_qc_data where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and bad_no='" + bad_no + "' and rec_date>='" + al[0].ToString() + "' and rec_date<='" + al[1].ToString() + "'";
tmp_qty = oda.getField(sql, "qty"); //?
newdr["bad_qty"] = Convert.ToDouble(tmp_qty); //this week
sql = "select NVL(sum(qty),0) as qty from a_sec_qc_data where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and bad_no='" + bad_no + "' and rec_date>='" + al[0].ToString() + "' and rec_date<='" + al[1].ToString() + "'";
bad_qty = oda.getField(sql, "qty");
sql = "select NVL(sum(qty),0) as qty from a_data_5 where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and rec_date>='" + al[0].ToString() + "' and rec_date<='" + al[1].ToString() + "'";
good_qty = oda.getField(sql, "qty");
if (good_qty >0)
{
r = Convert.ToDouble(bad_qty) / Convert.ToDouble(good_qty) * 100;
if (r > 1)
newdr["this_week"] = r.ToString("###,###,###.##");
else
newdr["this_week"] = "0" + r.ToString("###,###,###.##");
}
else
{
newdr["this_week"] = "0";
}
//end this week //last week
sql = "select NVL(sum(qty),0) as qty from a_sec_qc_data where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and bad_no='" + bad_no + "' and rec_date>='" + al[2].ToString() + "' and rec_date<='" + al[3].ToString() + "'";
bad_qty = oda.getField(sql, "qty");
sql = "select NVL(sum(qty),0) as qty from a_data_5 where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and rec_date>='" + al[2].ToString() + "' and rec_date<='" + al[3].ToString() + "'";
good_qty = oda.getField(sql, "qty");
if (good_qty > 0)
{
r = Convert.ToDouble(bad_qty) / Convert.ToDouble(good_qty) * 100;
if (r > 1)
newdr["last_week"] = r.ToString("###,###,###.##");
else
newdr["last_week"] = "0" + r.ToString("###,###,###.##");
}
else
{
newdr["last_week"] = "0";
} //end last week //two week
sql = "select NVL(sum(qty),0) as qty from a_sec_qc_data where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and bad_no='" + bad_no + "' and rec_date>='" + al[4].ToString() + "' and rec_date<='" + al[5].ToString() + "'";
bad_qty = oda.getField(sql, "qty");
sql = "select NVL(sum(qty),0) as qty from a_data_5 where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and rec_date>='" + al[4].ToString() + "' and rec_date<='" + al[5].ToString() + "'";
good_qty = oda.getField(sql, "qty"); if (good_qty>0)
{
r = Convert.ToDouble(bad_qty) / Convert.ToDouble(good_qty) * 100;
if (r > 1)
newdr["two_week"] = r.ToString("###,###,###.##");
else
newdr["two_week"] = "0" + r.ToString("###,###,###.##");
}
else
{
newdr["two_week"] = "0";
} //end two week //three week
和 one,two大致相同, 省略了
//end three week //this month
sql = "select NVL(sum(qty),0) as qty from a_sec_qc_data where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and bad_no='" + bad_no + "' and substr(rec_date,1,6)='" + this_month + "'";
bad_qty = oda.getField(sql, "qty");
sql = "select NVL(sum(qty),0) as qty from a_data_5 where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and substr(rec_date,1,6)='" + this_month + "'";
good_qty = oda.getField(sql, "qty"); if (good_qty >0)
{
r = Convert.ToDouble(bad_qty) / Convert.ToDouble(good_qty) * 100;
if (r > 1)
newdr["this_month"] = r.ToString("###,###,###.##");
else
newdr["this_month"] = "0" + r.ToString("###,###,###.##");
}
else
{
newdr["this_month"] = "0";
} //end this month //last month
last month省略
//end last_month
result.Rows.Add(newdr);
}
}
//****************** end这样写执行效率很低,需要25~35秒才能查询出数据.
这是之前一个程序员写的,现在已经离职了.现在我要优化这个程序.大家帮忙看看如何优化这段代码,让它执行效率更高写.
如果通过存储过程来实现,这个存储过程又该怎么写呢.
根据这3个条件,从相关表按照以下方式查询这些出数据.不良項目 返修數(雙) 前三周(%) 前兩周(%) 上周(%) 本周(%) 上月(%) 本月(%)
鞋頭不貼楦 1970 12.24 18.38 19.45 16.81 12.42 16.11
沿條鬆動 1709 3.35 8.46 11 14.59 5.83 11.87主要原代码:DataTable result = new DataTable();
string sql = string.Empty;
OracleDataAccess oda = new OracleDataAccess();
ArrayList al = new ArrayList();
al = getDate(txtDate.Value);//上上上周,上上周,上周,本周
string fact_no = ddlFactNo.SelectedValue.ToString();
string sec_no = ddlSecNo.SelectedValue.ToString();
//******** 按照報表格式構造DataTable
result.Columns.Add(new DataColumn("item", typeof(String)));//序號
result.Columns.Add(new DataColumn("bad_item", typeof(string)));//不良項目
result.Columns.Add(new DataColumn("bad_qty", typeof(double)));//返修數(雙)
result.Columns.Add(new DataColumn("three_week", typeof(String)));//上上上周返修率
result.Columns.Add(new DataColumn("two_week", typeof(String)));//上上周返修率
result.Columns.Add(new DataColumn("last_week", typeof(String)));//上周返修率
result.Columns.Add(new DataColumn("this_week", typeof(String)));//本周返修率
result.Columns.Add(new DataColumn("last_month", typeof(String)));//上月返修率
result.Columns.Add(new DataColumn("this_month", typeof(String)));//本月返修率
//******** end
//****************** 取上月和本月 統計的不良項
string last_month = getMonth(-1);
string this_month = getMonth(0);
sql = "select distinct bad_no,(select BAD_DESC from A_BAD_REASON_M where fact_no=a.FACT_NO and bad_no=a.BAD_NO) as bad_name from a_sec_qc_data a where fact_no='" + ddlFactNo.SelectedValue + "' and sec_no='" + ddlSecNo.SelectedValue + "' and substr(rec_date,1,6) in ('" + last_month + "','" + this_month + "')";
DataTable tmp = new DataTable();
tmp = oda.getDataTable(sql);
string bad_no = string.Empty;
long tmp_qty = 0L;
int good_qty = 0;
int bad_qty=0;
double r = 0;
if (tmp.Rows.Count > 0)
{
for (int i = 0; i < tmp.Rows.Count; i++)
{
DataRow newdr = result.NewRow();
newdr["item"] = i.ToString();
newdr["bad_item"] = tmp.Rows[i]["bad_name"].ToString();
bad_no = tmp.Rows[i]["bad_no"].ToString();
//返修數(雙)
sql = "select NVL(sum(qty),0) as qty from a_sec_qc_data where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and bad_no='" + bad_no + "' and rec_date>='" + al[0].ToString() + "' and rec_date<='" + al[1].ToString() + "'";
tmp_qty = oda.getField(sql, "qty"); //?
newdr["bad_qty"] = Convert.ToDouble(tmp_qty); //this week
sql = "select NVL(sum(qty),0) as qty from a_sec_qc_data where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and bad_no='" + bad_no + "' and rec_date>='" + al[0].ToString() + "' and rec_date<='" + al[1].ToString() + "'";
bad_qty = oda.getField(sql, "qty");
sql = "select NVL(sum(qty),0) as qty from a_data_5 where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and rec_date>='" + al[0].ToString() + "' and rec_date<='" + al[1].ToString() + "'";
good_qty = oda.getField(sql, "qty");
if (good_qty >0)
{
r = Convert.ToDouble(bad_qty) / Convert.ToDouble(good_qty) * 100;
if (r > 1)
newdr["this_week"] = r.ToString("###,###,###.##");
else
newdr["this_week"] = "0" + r.ToString("###,###,###.##");
}
else
{
newdr["this_week"] = "0";
}
//end this week //last week
sql = "select NVL(sum(qty),0) as qty from a_sec_qc_data where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and bad_no='" + bad_no + "' and rec_date>='" + al[2].ToString() + "' and rec_date<='" + al[3].ToString() + "'";
bad_qty = oda.getField(sql, "qty");
sql = "select NVL(sum(qty),0) as qty from a_data_5 where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and rec_date>='" + al[2].ToString() + "' and rec_date<='" + al[3].ToString() + "'";
good_qty = oda.getField(sql, "qty");
if (good_qty > 0)
{
r = Convert.ToDouble(bad_qty) / Convert.ToDouble(good_qty) * 100;
if (r > 1)
newdr["last_week"] = r.ToString("###,###,###.##");
else
newdr["last_week"] = "0" + r.ToString("###,###,###.##");
}
else
{
newdr["last_week"] = "0";
} //end last week //two week
sql = "select NVL(sum(qty),0) as qty from a_sec_qc_data where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and bad_no='" + bad_no + "' and rec_date>='" + al[4].ToString() + "' and rec_date<='" + al[5].ToString() + "'";
bad_qty = oda.getField(sql, "qty");
sql = "select NVL(sum(qty),0) as qty from a_data_5 where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and rec_date>='" + al[4].ToString() + "' and rec_date<='" + al[5].ToString() + "'";
good_qty = oda.getField(sql, "qty"); if (good_qty>0)
{
r = Convert.ToDouble(bad_qty) / Convert.ToDouble(good_qty) * 100;
if (r > 1)
newdr["two_week"] = r.ToString("###,###,###.##");
else
newdr["two_week"] = "0" + r.ToString("###,###,###.##");
}
else
{
newdr["two_week"] = "0";
} //end two week //three week
和 one,two大致相同, 省略了
//end three week //this month
sql = "select NVL(sum(qty),0) as qty from a_sec_qc_data where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and bad_no='" + bad_no + "' and substr(rec_date,1,6)='" + this_month + "'";
bad_qty = oda.getField(sql, "qty");
sql = "select NVL(sum(qty),0) as qty from a_data_5 where fact_no='" + fact_no + "' and sec_no='" + sec_no + "' and substr(rec_date,1,6)='" + this_month + "'";
good_qty = oda.getField(sql, "qty"); if (good_qty >0)
{
r = Convert.ToDouble(bad_qty) / Convert.ToDouble(good_qty) * 100;
if (r > 1)
newdr["this_month"] = r.ToString("###,###,###.##");
else
newdr["this_month"] = "0" + r.ToString("###,###,###.##");
}
else
{
newdr["this_month"] = "0";
} //end this month //last month
last month省略
//end last_month
result.Rows.Add(newdr);
}
}
//****************** end这样写执行效率很低,需要25~35秒才能查询出数据.
这是之前一个程序员写的,现在已经离职了.现在我要优化这个程序.大家帮忙看看如何优化这段代码,让它执行效率更高写.
如果通过存储过程来实现,这个存储过程又该怎么写呢.
解决方案 »
- 不用视图只用SQL语句如何实现对两个表联查
- 谁有.NET所有的类库集合教程!!!就是找不到。。。郁闷
- sql(请求失败或服务器未及时响应.....), '"+count+"'语法
- 不懂父类子类。
- 紧急求助:如何在浏览器里播放声音啊
- 高手请帮我看看
- 从数据库中读取一列数据到组合框的代码返回操作数据库错误
- XtraGrid中设置一列的编辑方式为LookUpEditor后,运行时该列不能马上显示其内容
- 复制excel 单元格 效率太低
- C#窗体中图片移动:怎么让图片从上到下,从左到右(从左上角到右下角)循环移动
- winForm中打开另一个winForm的问题
- C#中如何实现IntPtr与Uint16[]、UInt32[]的互相拷贝
改用存储过程其实也没什么 主要还是看你的sql语句如何写才高效