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

悬赏园豆：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 | 初学一级 | 园豆：100
提问于：2012-02-08 13:58

< >

所有回答(12)

存储过程+索引

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

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

ry, dw 这两个表建立跟查询相关的索引

冰牙 | 园豆：118 (初学一级) | 2012-02-08 16:18

篩選后建立臨時表呢?

sunylf | 园豆：165 (初学一级) | 2012-02-08 17:33

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

1、建立适当的索引

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

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

4、使用StringBuilder拼接字符串

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

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

首先一定要放在一个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

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

顾晓北 | 园豆：10898 (专家六级) | 2012-02-09 09:46

问题主要出在数据库上，

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

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

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

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

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

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

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

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

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

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

首先，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

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

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

GuYoung | 园豆：433 (菜鸟二级) | 2012-02-13 16:54

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。

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

欢迎，请先登录或者注册。