首页新闻找找看学习计划

数据库优化,一个10分钟才能查出数据的程序,如何优化.

1
悬赏园豆:80 [待解决问题]
 
 // 点击统计按钮的时候调用的是下面这个方法
 1 private void GetData()
2 {
3 CurrentYear=Session["currentYear"].ToString().Trim();
4
5 if (SelectYear==CurrentYear)
6 rptAccess=new ReportAccess(true);
7 else
8 rptAccess=new ReportAccess(false);
9
10 localcode=rptAccess.GetLocalid(Session["currentLocalid"].ToString(),(bool)Session["IsCenter"],SelectLocalid);
11

14 if(isfl!="")
15 CjrZsCmdText="select count( distinct idcode) from ry,dw where dw is not null and ry.jyzk=1 and dw.PKID=ry.dw and dw.years=ry.years and dw.ISFl="+isfl+" and ry.localid like "+"'"+localcode+"'"+"and ry.years="+SelectYear+"";
16 else
17 CjrZsCmdText="select count( distinct idcode) from ry,dw where dw is not null and ry.jyzk=1 and dw.PKID=ry.dw and dw.years=ry.years and ry.localid like "+"'"+localcode+"'"+"and ry.years="+SelectYear+"";
18 this.TBoxCjrZs.Text=rptAccess.CmdEScalar(CjrZsCmdText).ToString();

20 if(isfl!="")
21 CjrWomanCmdText="select count( distinct idcode) from ry, dw where dw is not null and ry.jyzk=1 and dw.PKID=ry.dw and dw.years=ry.years and dw.ISFl="+isfl+" and ry.localid like "+"'"+localcode+"'"+" and ry.years="+SelectYear+" and ry.sex='2'";
22 else
23 CjrWomanCmdText="select count( distinct idcode) from ry, dw where dw is not null and ry.jyzk=1 and dw.PKID=ry.dw and dw.years=ry.years and ry.localid like "+"'"+localcode+"'"+" and ry.years="+SelectYear+" and ry.sex='2'";
24 this.TBoxCjrWomen.Text=rptAccess.CmdEScalar(CjrWomanCmdText).ToString();
         ......
      }
  // 在这个方法内,有调用了30次CmdEScalar()方法.每次调用传入的SQL不一样.下面是CmdEScalar()方法.

 

public Decimal CmdEScalar(string CmdEScalarText)
{
string CmdText=CmdEScalarText;
Decimal rtn=0;
object myrtn;
SqlConnection myConnection = new SqlConnection(myConnString);
try
{
SqlCommand myCommand = new SqlCommand();
myCommand.CommandTimeout=2000;
myCommand.Connection=myConnection;
myConnection.Open();
myCommand.CommandText=CmdText;
myrtn=myCommand.ExecuteScalar();
if (myrtn==System.DBNull.Value)
rtn=0;
else
rtn=System.Convert.ToDecimal(myrtn);
return rtn;
}
catch(Exception err)
{
throw new Exception(err.Message);
}
finally
{
myConnection.Close();
myConnection.Dispose();
}
}

这个程序用了报表,***Cmdtext是报表中每个统计项的SQL.

数据量的话,上面程序所涉及到的表,每个表基本都在50W条以上.

请问如何优化,让统计时间缩短.

CodeSummer的主页 CodeSummer | 初学一级 | 园豆:100
提问于:2012-02-08 13:58
< >
分享
所有回答(12)
0

存储过程+索引

ERS | 园豆:728 (小虾三级) | 2012-02-08 15:45
0

把所有的select 写成一个语句放在存储过程里返回查询结果集

ry, dw 这两个表建立跟查询相关的索引
冰牙 | 园豆:118 (初学一级) | 2012-02-08 16:18
0

篩選后建立臨時表呢?

sunylf | 园豆:165 (初学一级) | 2012-02-08 17:33
0
count( distinct idcode)
还可以这么用。。长见识了
waninlezu | 园豆:661 (小虾三级) | 2012-02-08 21:38

distinct 后 再 count(0) 是否会加快?

支持(0) 反对(0) waninlezu | 园豆:661 (小虾三级) | 2012-02-08 21:40

@waninlezu: 这样结果就有可能不对了 ,会有重复数据

支持(0) 反对(0) 陈齐 | 园豆:311 (菜鸟二级) | 2012-02-08 23:06
0

1、建立适当的索引

2、ry.localid  like  "+"'"+localcode+"'"+"   既然没有使用到模糊查询最好不用使用like 查询

3、使用join …on的方式来做表关联

4、使用StringBuilder拼接字符串

5、将所有select 放到一个拼成一个语句或使用存储过程 调用一次数据库返回结果集

陈齐 | 园豆:311 (菜鸟二级) | 2012-02-08 23:17
0

首先一定要放在一个SQL中进行查询,避免对SQL SERVER的重复请求:

1.用StringBuilder拼接SQL,但是每个IF语句后不执行ExcuteScaler,继续进入下一个IF语句;

2.在方法末尾Add输出参数;

3.调用ExcuteScaler;

4.获取输出参数;

示例代码:

StringBuilder strBuilder=new String Builder();
if (condition expression)
strBuilder.Append("...");
else
strBuilder.Append("...");

strBuilder.Append(Environment.NewLine);//为了调试方便,添加换行符

if(condition expression)
strBuilder.Append("...");
...
...
...

cmd=GetCMD(strBuilder.toString());
cmd.AddParameter(Orientation.Input, "@ResultA", DbType.Int32);
...
...
...
cmd.ExcuteScaler();

int resultA=cmd.GetParameter("@Result");
...
...
...
Domi.Z | 园豆:125 (初学一级) | 2012-02-09 09:44

加输出参数那块还有些疑惑,能不能再稍微详细一些,谢谢

支持(0) 反对(0) CodeSummer | 园豆:100 (初学一级) | 2012-02-09 12:53

@CodeSummer:

IDbDataParameter parameter = Command.CreateParameter(); 
parameter.ParameterName = "TotalCount"; 
parameter.DbType = dbType.Int32;
parameter.Direction = ParameterDirection.Output; Command.Parameters.Add(parameter);

Command就是cmd,它的CommandText可以是这样:SELECT @TotalCount=COUNT(*) FROM dbo.T1
执行后这样取: 

IDataParameter param = Command.Parameters["TotalCount"].Value;
return param.Value;
支持(0) 反对(0) Domi.Z | 园豆:125 (初学一级) | 2012-02-09 22:32
0

还是要设计你的UI吧?别让用户等着,提示他需要时间长一点,然后让他先浏览其他地方,等查询完了就放到缓存里面,用户再浏览的时候,直接从缓存里面取。

顾晓北 | 园豆:9534 (大侠五级) | 2012-02-09 09:46
0

问题主要出在数据库上,

总共四条语句,之行的时候都会执行两条,其中两条的区别就是后面的一条语句多了一个sex条件。这两个其实可以合并查询,通过对sex group得到。

程序查询的时候最好采用参数化,通过SqlParamter处理,或者是采用存储过程,这样可以减少sql语句编译的时间(在此处估计影响的时间不大)

在多表查询的时候采用join替代你的where。

不知道你的sql是sql2000还是sql 2005以上或者其他,如果是sql2000,则在每个表上建立一个涵盖索引,涵盖到所需要的字段,如果是sql2005, 则可以建包含索引,针对不需要排序的字段放到包含的字段中。

dail | 园豆:630 (小虾三级) | 2012-02-09 11:59
0

是否大量使用seesion,不做负载吗?另建议报表统计用存储过程。

lonely_rain | 园豆:752 (小虾三级) | 2012-02-10 11:17
0

连接不停的open/close很费时间的,

我建议把sql语句放在一起执行。可以试试union all之类的。返回一个datatable,只要两列,count列和type列

死白的man | 园豆:2135 (老鸟四级) | 2012-02-10 16:46
0

首先,30次CmdEScalar,实际调用15次(只会成才满足其中的一半),也就是每次调用耗时10min/15=40sec,所以真的需要做15次查询么?能不能把符合条件的数据加载到datatable(甚至直接用datareader)来?比如你代码里面都写了

ry.jyzk=1 and dw.PKID=ry.dw and dw.years=ry.years and dw.ISFl="+isfl+"  and ry.localid  like  "+"'"+localcode+"'"+" and ry.years="+SelectYear+" 

这说明,可以先查询这个到内存中(得到的可能是15条记录),再在内存中筛选

其次,为以下字段建立索引:ry.jyzk,dw.PKID,ry.dw, dw.years,ry.years, dw.ISFl, ry.localid

再次,使用一个connection对象

小彬 | 园豆:947 (小虾三级) | 2012-02-11 11:29
0

可以考虑使用内存数据库,如sqlite

把重复使用的查询结果缓存到内存数据库,减少主数据库的查询量

GuYoung | 园豆:433 (菜鸟二级) | 2012-02-13 16:54
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册