数据库里数据量增大后，应用程序查询速度变慢

Question

贴出你的sql语句，大家好分析呀，
我的数据库中的900万行数据，查询都很快呀，
如果你用了很多的or、like、union、minus、group by 
可能会影响速度

Answer 1

数据库里数据量增大后，应用程序查询速度变慢...
原因：1.数据库设计有问题，有太多的数据冗余
      2.没有建立相应的INDEX在要检索的列。
      3.SQL 语句没有优化
      4.用了很多的or、like、union、minus、group by 
....

Answer 2

ALTER                    PROCEDURE picconsumeline
        @begin_date          datetime   ,-- input parameter
        @end_date            datetime  ,  -- both user input
        @compute_name        varchar(30) 
       as
 set nocount on
     declare
        @r_lineid             char(4)    , --define variable
        @r_linecentid         char(2)    , 
        @r_linename           char(16)   ,
        @r_beginstation       varchar(10),
        @r_endstation         varchar(10),
        @r_icid               numeric(5,0),
        @r_icname             varchar(20),
        @r_cardnum            numeric(12,0),
        @r_moneynum           numeric(12,0),
        @r_icnum              numeric(12,0),
        @r_icmoney            numeric(12,2),
        @r_deptid             char(9),
        @r_deptname           varchar(30)
 delete from icconsumeline_temp where computename = @compute_name
       
    declare line_curs cursor for 
         select distinct line_id          ,
                linecent_id      
          from  uv_linecent
         order by line_id
    open line_curs
    fetch line_curs into @r_lineid,@r_linecentid
          while (@@fetch_status = 0)
             begin 
                declare ic_curs cursor for
                    select ic_id  from ictype_dict
                    where ic_id <32 --user ic is (0-32)
                open ic_curs
                fetch ic_curs into @r_icid
                     while (@@fetch_status = 0) 
                         begin
                             select @r_icnum   = sum(ic_num),
                                    @r_icmoney = sum(ic_money),
                                    @r_cardnum = sum(card_num),
                                    @r_moneynum= sum(money_num)
                             from   bustwice_dict
                             where  ic_id = @r_icid 
                              and  convert(char(10),use_date,112)>=convert(char(10),@begin_date,112)
                               and  convert(char(10),use_date,112)<=convert(char(10),@end_date,112) 
                               and  line_id = @r_lineid
                               and  linecent_id = @r_linecentid --需要起点终点，必须有线路分号
                               
        if @r_icnum is null begin select @r_icnum = 0 end 
        if @r_icmoney is null begin select @r_icmoney = 0 end 
        if @r_cardnum is null begin select @r_cardnum = 0 end
        if @r_moneynum is null begin select @r_moneynum = 0 end
        if @r_icnum > 0 and @r_icmoney >= 0 begin
        select @r_beginstation = a.begin_station,
               @r_endstation   = a.end_station ,
               @r_linename     = b.line_name   ,
               @r_deptid       = b.dept_id
               from  uv_linecent a,
                     uv_line b
         where a.line_id = b.line_id
         and   a.line_id = @r_lineid
         and   a.linecent_id = @r_linecentid
             select @r_icname = ic_name
              from   ictype_dict 
             where  ic_id = @r_icid
            select @r_deptname = dept_name
             from  department_dict
            where dept_id = @r_deptid
                                  insert into icconsumeline_temp
                                  values ( @r_lineid  ,
                                           @r_linename ,
                                           @r_beginstation,
                                           @r_endstation,
                                           @r_icid  ,
                                           @r_icname    ,
                                           @r_cardnum   ,
                                           @r_moneynum  ,
                                           @r_icnum   ,
                                           @r_icmoney  ,
                                           @r_deptid ,
                                           @r_deptname ,
                                            @compute_name) 
                                end --if close
                             -- @r_lineid = ' '
                                 --@r_linename = " "
                               --  @r_beginstation = " "
                               --  @r_endstation = " "
                             select    @r_icname = 0 ,
                                       @r_icmoney = 0  
                  fetch ic_curs into @r_icid
                  end --ic_curs close
                  close ic_curs
                  deallocate ic_curs
        fetch  line_curs into  @r_lineid,@r_linecentid
        end --line_curs close
        close line_curs
        deallocate line_curs
 set nocount off

Answer 3

支持Leimin的分析，
原因是多样的，
不知哪个套用你。
^_^