sql语句优化

Question

报表跑太慢所以来请教下大家下面那段sql语句可以怎么样进行优化select a.order_no,to_char(b.create_time,'yyyy-MM-dd')as create_date,to_char(b.create_time,'HH24:mi:ss')
as create_time, a.cs_id,CONCAT(b.inv_header,b.inv_no) 
as inv_no,b.inv_amt,b.status_flg,a.order_code,a.pos_no 
from order_head a,invoice b 
where a.com_no=1001 
and a.com_no=b.com_no  
and a.store_no=1084 
and a.pos_no in(10491 ) 
and a.order_code  in('DO') 
and a.status_flg in ('P','D','N','W') 
and to_char(a.order_date,'yyyy-MM-dd')='2012-03-01'  
and  a.primarykey =b.order_primarykey 
and b.status_flg=1  
union 
select a.order_no,to_char(b.create_time,'yyyy-MM-dd')as createdate,to_char(b.create_time,'HH24:mi:ss')
as createTime, a.cs_id,CONCAT(b.inv_header,b.inv_no)
as inv_no,b.inv_amt,b.status_flg,a.order_code,a.pos_no 
from order_head a,invoice b 
where a.com_no=1001 
and a.com_no=b.com_no  
and a.store_no=1084 
and a.pos_no in(10491 ) 
and a.order_code  ='RNC' 
and a.status_flg in ('P','D','N','W') 
and to_char(a.order_date,'yyyy-MM-dd')='2012-03-01'  
and a.primarykey =b.cancel_primarykey 
union  
select a.order_no,to_char(a.order_date,'yyyy-MM-dd')as createdate,to_char(b.create_time,'HH24:mi:ss')
as createTime,   a.cs_id,CONCAT(b.inv_header,b.inv_no) 
as inv_no,b.inv_amt,b.status_flg,a.order_code,a.pos_no  
from order_head a,invoice b 
where a.com_no=1001 
and a.com_no=b.com_no  
and a.store_no=1084 
and a.pos_no in(10491 )  
and a.order_code  ='RNC' 
and a.status_flg in ('P','D','N','W') 
and to_char(b.create_time,'yyyy-MM-dd')='2012-03-01'     
and  a.primarykey =b.cancel_primarykey 
and to_char(a.order_date,'yyyy-MM-dd')!=to_char(b.inv_time,'yyyy-MM-dd') 
and b.status_flg=0 
union   
select a.order_no,to_char(b.create_time,'yyyy-MM-dd')as createdate,to_char(b.create_time,'HH24:mi:ss')
as createTime,  a.cs_id,CONCAT(b.inv_header,b.inv_no) 
as inv_no,b.inv_amt,'1','DO',a.pos_no  
from order_head a,invoice b 
where a.com_no=1001 
and a.com_no=b.com_no  
and a.store_no=1084 
and a.pos_no in(10491 )  
and a.order_code  ='RNC' 
and a.status_flg in ('P','D','N','W') 
and to_char(b.create_time,'yyyy-MM-dd')='2012-03-01'     
and  a.primarykey =b.cancel_primarykey 
and to_char(a.order_date,'yyyy-MM-dd')!=to_char(b.inv_time,'yyyy-MM-dd') 
and b.status_flg=0   
order by inv_no 

Answer 1

用临时表逐条更新，不要用UNION。

Answer 2

and a.pos_no in(10491 ) 
and a.order_code  in('DO') -->and a.pos_no = 10491
and a.order_code = 'DO'

Answer 3

in后面只有一个值，那就直接使用等于。无法貌似不是MSSQL的，建议把帖子转到相应版块问一下

Answer 4

是 oracle 的  第一次来还不太熟悉见谅啦   看来这里大家还是很热情的啊

Answer 5

select a.order_no,to_char(b.create_time,'yyyy-MM-dd')as create_date,to_char(b.create_time,'HH24:mi:ss')
as create_time, a.cs_id,CONCAT(b.inv_header,b.inv_no) 
as inv_no,b.inv_amt,b.status_flg,a.order_code,a.pos_no 
from order_head a,invoice b 
where a.com_no=1001 
and a.com_no=b.com_no  
and a.store_no = 1084 
and a.pos_no in =10491 
and a.status_flg in ('P','D','N','W') 
and ((to_char(a.order_date,'yyyy-MM-dd')='2012-03-01' and ((a.primarykey =b.order_primarykey and a.order_code ='DO' and b.status_flg=1 ) or (a.primarykey =b.cancel_primarykey and a.order_code  ='RNC')))
or (b.status_flg=0 and to_char(a.order_date,'yyyy-MM-dd')!=to_char(b.inv_time,'yyyy-MM-dd') and a.primarykey =b.cancel_primarykey and to_char(b.create_time,'yyyy-MM-dd')='2012-03-01'))
union
select a.order_no,to_char(b.create_time,'yyyy-MM-dd')as createdate,to_char(b.create_time,'HH24:mi:ss')
as createTime,  a.cs_id,CONCAT(b.inv_header,b.inv_no) 
as inv_no,b.inv_amt,'1','DO',a.pos_no  
from order_head a,invoice b 
where a.com_no=1001 
and a.com_no=b.com_no  
and a.store_no=1084 
and a.pos_no =10491  
and a.order_code  ='RNC' 
and a.status_flg in ('P','D','N','W') 
and to_char(b.create_time,'yyyy-MM-dd')='2012-03-01'     
and  a.primarykey =b.cancel_primarykey 
and to_char(a.order_date,'yyyy-MM-dd')!=to_char(b.inv_time,'yyyy-MM-dd') 
and b.status_flg=0   
order by inv_no

Answer 6

优化可以从下面几点入手：
1、建立合理的索引；
2、尤其注意where语句后面的顺序，从左到右分别为主键、聚簇索引、非聚簇索引；
3、where语句后面的等号左边尽量不要用函数；
4、in要改成or,如：and a.status_flg in ('P','D','N','W')改为and （a.status_flg='P' or a.status_flg='D' or a.status_flg='N' or a.status_flg='W');
5、尽量少用like，改用instr('asfa','a')>0的形式；
7、SQLserver中尽量避免使用null（因为是逐行扫瞄的）；
8、exists语句速度快，要尽量学会使用它；

Answer 7

9、union all要比union快，因为union要去掉重复的记录，union all则不用；

Answer 8

哥们，有一条好像不对，oracle是从右往左的

Answer 9

sql server中是从左往右的，Oracle中真还不知道是倒过来的，不管怎样语句拿查询分析器分析一下就知道哪个效率高了

Answer 10

select a.order_no,to_char(b.create_time,'yyyy-MM-dd')as createdate,to_char(b.create_time,'HH24:mi:ss')
as createTime,  a.cs_id,CONCAT(b.inv_header,b.inv_no) 
as inv_no,b.inv_amt,'1','DO',a.pos_no  
from order_head a inner join invoice b on a.primarykey =b.order_primarykey and a.com_no=b.com_no
where a.com_no=1001 --这里的顺序从左到右依次放主键、唯一索引、聚簇索引、非聚簇索引;楼主如果不用inner join的话,第一个应该放a.primarykey =b.cancel_primarykey 
and a.store_no=1084 
and a.pos_no=10491
and to_char(a.order_date,'yyyy-MM-dd')='2012-03-01'    
and (a.status_flg='P' or a.status_flg='D' or a.status_flg='N' or a.status_flg='W')
and (a.order_code='DO' and b.status_flg=1  
or a.order_code ='RNC' 
and to_char(a.order_date,'yyyy-MM-dd')!=to_char(b.inv_time,'yyyy-MM-dd') 
and b.status_flg=0)
union all --如果你这几种情况不会产生重复记录的话，建议改为union all
select a.order_no,to_char(b.create_time,'yyyy-MM-dd')as createdate,to_char(b.create_time,'HH24:mi:ss')
as createTime, a.cs_id,CONCAT(b.inv_header,b.inv_no)
as inv_no,b.inv_amt,b.status_flg,a.order_code,a.pos_no 
from order_head a inner join invoice b on a.primarykey =b.order_primarykey and a.com_no=b.com_no
where a.com_no=1001 
and a.store_no=1084 
and a.pos_no=10491
and a.order_code  ='RNC' 
and (a.status_flg='P' or a.status_flg='D' or a.status_flg='N' or a.status_flg='W')
and (to_char(a.order_date,'yyyy-MM-dd')='2012-03-01' or b.status_flg=0 and to_char(a.order_date,'yyyy-MM-dd')!=to_char(b.inv_time,'yyyy-MM-dd'))

Answer 11

看着代码谈sql优化只能解决一小部分问题, sql优化要首先根据 查询数据量，执行计划，硬件配置等来判断
要遵循一定的步骤，例如你的例子可以这样考虑:1.你的需要过滤掉重复记录吗？如果不需要就用union all
2.你的代码是由多个部分union起来的，每个子部分的性能都差吗？
  你可以单独执行子部分，观察哪一段用时最长，然后单独处理
3.针对你认为有性能问题的子部分，查看执行计划，观察时间花费在哪里？是不是应该花费这么大代价？
4.然后才能用到具体的优化手段，比如上面帖子中提到的诸多……

Answer 12

楼主可以试试我改过之后的如下：select a.order_no,to_char(b.create_time,'yyyy-MM-dd')as create_date,to_char(b.create_time,'HH24:mi:ss')
as create_time, a.cs_id,CONCAT(b.inv_header,b.inv_no) 
as inv_no,b.inv_amt,b.status_flg,a.order_code,a.pos_no 
from order_head a inner join invoice b 
                          on a.primarykey =b.order_primarykey
                         and a.com_no=b.com_no 
where to_char(a.order_date,'yyyy-MM-dd')='2012-03-01'
      and b.status_flg=1  
      and a.status_flg in ('P','D','N','W')    
      and a.order_code = 'DO'   
      and a.pos_no = 10491 
      and a.store_no = 1084
      and a.com_no=1001
  
union all
select a.order_no,to_char(b.create_time,'yyyy-MM-dd')as createdate,to_char(b.create_time,'HH24:mi:ss')
as createTime, a.cs_id,CONCAT(b.inv_header,b.inv_no)
as inv_no,b.inv_amt,b.status_flg,a.order_code,a.pos_no 
from order_head a inner join invoice b
                          on a.primarykey =b.cancel_primarykey
                         and a.com_no=b.com_no 
where to_char(a.order_date,'yyyy-MM-dd')='2012-03-01'
      and a.status_flg in ('P','D','N','W')    
      and a.order_code ='RNC' 
      and a.com_no=1001 
      and a.store_no=1084
      and a.pos_no = 10491   union all 
select a.order_no,to_char(a.order_date,'yyyy-MM-dd')as createdate,to_char(b.create_time,'HH24:mi:ss')
as createTime,   a.cs_id,CONCAT(b.inv_header,b.inv_no) 
as inv_no,b.inv_amt,b.status_flg,a.order_code,a.pos_no  
from order_head a inner join invoice b 
                          on to_char(a.order_date,'yyyy-MM-dd')!=to_char(b.inv_time,'yyyy-MM-dd') 
 and a.com_no=b.com_no
 and a.primarykey =b.cancel_primarykey
where to_char(b.create_time,'yyyy-MM-dd')='2012-03-01'
      and a.status_flg in ('P','D','N','W') 
      and b.status_flg=0
      and a.com_no=1001 
      and a.store_no=1084 
      and a.pos_no = 10491 
      and a.order_code  ='RNC' 
       
union all  
select a.order_no,to_char(b.create_time,'yyyy-MM-dd')as createdate,to_char(b.create_time,'HH24:mi:ss')
as createTime,  a.cs_id,CONCAT(b.inv_header,b.inv_no) 
as inv_no,b.inv_amt,'1','DO',a.pos_no  
from order_head a inner join invoice b 
          on to_char(a.order_date,'yyyy-MM-dd')!=to_char(b.inv_time,'yyyy-MM-dd')
                 and a.com_no=b.com_no  
 and a.primarykey =b.cancel_primarykey
where to_char(b.create_time,'yyyy-MM-dd')='2012-03-01'
and a.status_flg in ('P','D','N','W')
and a.store_no=1084 
and a.pos_no = 10491
and a.order_code  ='RNC'
and b.status_flg=0
and a.com_no=1001
order by inv_no