北纬30度水域 2016-01-08 06:50 采纳率: 50%
浏览 1777
已结题

informix 数据库查询问题,请求帮忙优化

 CREATE PROCEDURE informix.pro_counteveryday
()
BEGIN
  /* Procedure body */
  define ls_cuid varchar(255);
  define ls_ipv6 varchar(255);
  define ld_count_date datetime year to second;
  define start_date datetime year to second;
  define end_date datetime year to second;
  let start_date = extend(sysdate-interval(1) day to day,year to day);
  let end_date = extend(sysdate day to day,year to day);

  delete from rm_alarm_trans_inserttemp where 1=1;
  --EXECUTE IMMEDIATE 'truncate table rm_alarm_trans_inserttemp';
   --      union all
 foreach select related_city_cuid as regionid,extend(a.revalarm_time,year to day) as startdate ,b.dev_ipv6
 into ls_cuid,ld_count_date,ls_ipv6 from history_alarm as a,alarm_trans_resource as b
        where length(a.related_port_cuid)>0 and length(b.port_cuid)>0 
        and (a.revalarm_time<end_date and a.revalarm_time>=start_date  ) 
        and  a.related_port_cuid=b.port_cuid 
        --and length(b.dev_ipv6)>0 
        and a.alarmobject_type in('物理端口' ,'CTP端口') 
        insert into rm_alarm_trans_inserttemp(regionid,startdate,dev_ipv6) values(ls_cuid,ld_count_date,ls_ipv6);
 end foreach;     

  foreach       select related_city_cuid as regionid,extend(a.revalarm_time,year to day) as startdate,b.dev_ipv6
 into ls_cuid,ld_count_date,ls_ipv6 from current_alarm as a ,alarm_trans_resource as b    
        where (a.revalarm_time<end_date and a.revalarm_time>=start_date ) 
        and a.related_port_cuid=b.port_cuid 
        --and length(b.dev_ipv6)>0 
        and a.alarmobject_type in('物理端口' ,'CTP端口') and length(b.dev_ipv6)>0 
        --is not null 
        if(length(ls_ipv6)>0){
  insert into rm_alarm_trans_inserttemp(regionid,startdate,dev_ipv6) values(ls_cuid,ld_count_date,ls_ipv6);}
 end foreach;  

  foreach      select related_city_cuid as regionid,extend(a.revalarm_time,year to day) as startdate,b.dev_ipv6
  into ls_cuid,ld_count_date,ls_ipv6    from current_alarm as a,alarm_trans_resource as b
        where (a.revalarm_time<end_date and a.revalarm_time>=start_date) 
        and a.related_ne_cuid = b.related_ne_cuid 
        --and length(b.dev_ipv6)>0  
        and a.alarmobject_type='网元'
        if(length(ls_ipv6)>0){
        insert into rm_alarm_trans_inserttemp(regionid,startdate,dev_ipv6) values(ls_cuid,ld_count_date,ls_ipv6);
        }

 end foreach;  
  foreach    select related_city_cuid as regionid,extend(a.revalarm_time,year to day) as startdate ,b.dev_ipv6
    into ls_cuid,ld_count_date,ls_ipv6    from history_alarm as a,alarm_trans_resource as b
        where (a.revalarm_time<end_date and a.revalarm_time>=start_date ) 
        and  a.related_ne_cuid = b.related_ne_cuid 
        --and length(b.dev_ipv6)>0  
        and a.alarmobject_type='网元' and b.dev_ipv6 is not null 
    insert into rm_alarm_trans_inserttemp(regionid,startdate,dev_ipv6) values(ls_cuid,ld_count_date,ls_ipv6);
 end foreach; 
 --       union all

       /* truncate table rm_alarm_trans_souday;

         insert into rm_alarm_trans_souday 
         select b.regionid,b.regionname,b.cityname,nvl(a.alarmnum,0) as alarmnum,nvl(a.startdate,current) as startdate
         from (select cuid as regionid,label_cn as regionname,label_cn_x as cityname from v_district where label_cn = label_cn_x ) as b
         left join (select regionid,startdate,count(regionid) as alarmnum from rm_alarm_trans_inserttemp group by regionid,startdate) as a
         on a.regionid=b.regionid;*/
END;
END PROCEDURE;                                                                                                                                                                              

这个数据量不太大,才一千万以上,当加上 字段的is not null的时候,查询效率非常慢。试过length(字段)>0效果也是不理想。有没有更好的查询?

  • 写回答

1条回答 默认 最新

  • devmiao 2016-01-08 18:59
    关注
    评论

报告相同问题?

悬赏问题

  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀