这段代码怎么优化一下,查询速度实在是太慢了??
代码如下,是做的一个报表统计查询,用了三个datagrid控件绑定,不过查询效率很低,大家看怎么来优化一下好
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
this.lbl_start_datem.Text=Session[ "start_datem "].ToString();
this.lbl_end_datem.Text=Session[ "end_datem "].ToString();
string str1,str2;
str1=Session[ "start_datem "].ToString();
str2=Session[ "end_datem "].ToString();
SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings[ "DBConnection_tw "]);
string sql= "select case when m.loc_code is null then s.stano else m.loc_code end as loc_code,s.station,s.c3, "+
"isnull(m.ps,0) as ps,isnull(m.weig,0) as weig,isnull(m.charge,0) as charge from "+
"(select loc_code,count(*) as ps,sum(weig) as weig,sum(charge) as charge from manifest where datem between ' "+str1+ " ' and ' "+str2+ " ' group by loc_code) "+
"m full outer join stat s on m.loc_code=s.stano ";
string sql1= "select d.c3,sum(d.ps) as ps,sum(d.weig) as weig,sum(d.charge) as charge from( "+sql+ ") d group by c3 ";
string sql2= "select '統計(總) ' as 統計, sum(d1.ps) as ps,sum(d1.weig) as weig,sum(d1.charge) as charge from ( "+sql1+ ") d1 ";
SqlDataAdapter myCommand =new SqlDataAdapter(sql,conn);
SqlDataAdapter myCommand1 =new SqlDataAdapter(sql1,conn);
SqlDataAdapter myCommand2 =new SqlDataAdapter(sql2,conn);
DataSet ds=new DataSet();
DataSet ds1=new DataSet();
DataSet ds2=new DataSet();
myCommand.Fill(ds);
myCommand1.Fill(ds1);
myCommand2.Fill(ds2);
dg.DataSource=ds;
dg1.DataSource=ds1;
dg2.DataSource=ds2;
dg.DataBind();
dg1.DataBind();
dg2.DataBind();
}
}
------解决方案--------------------改写成存储过程吧。这样性能会提高不少
------解决方案--------------------改善查询性能:
1.使用存储过程
2.建立合适的索引
3.加上合理的where条件~
------解决方案--------------------写成存储过程吧
------解决方案--------------------不说你
改善查询性能:
1.使用存储过程
2.建立合适的索引
3.加上合理的where条件~
-----------------------
就说你的SQL说起,构造复杂的SQL语句,使用StringBuilder的性能就比+的高
再者,web开发,请注意SQL防注入,使用参数的SQL语句或存储过程,或写防注入的函数.
然后是,干吗使用3个Command,你用分号分开的SQL或存储过程,分开执行,返回一整个DATASET,不更好
还有没有必要每次统计的,请放到另外一个表,等用户重新统计,再统计,这样可以节约非常多的资源
之后还有很多,不说了