sybase查询太慢了,大家看下sql看有没有优化的方法
两张表:
表area_device(2000条数据)
列:devSN(varchar), areaId(varchar).devSN和areaId是联合主键
表 device_info(600万条数据)
列:id(int),devSN(varchar)设备sn,time(DateTime)回合时间,value(varchar)性能值. id是主键
两张表中:devSN是关联列(但是没有加外键)
sql语句:取出一定时间段内一个area下的设备的device_info表中每回合value的和的最大值(时间段内有多回合)
select max(b.s1) as m1, max(b.s2) as m2 from (select sum(convert(numeric(18,0),value)) as s1 From device_info AS aw where time between '2011-07-01 00:00:00' and '2011-08-01 00:00:00' and aw.devSN in( select devSN from area_device where areaId='12') and value!=null group by time) as b
注:表中没有加任何索引,执行速度大约在130s左右
求优化