首页 新闻 会员 周边 捐助

帮忙优化下下面的语句吧。

0
悬赏园豆:100 [已解决问题] 解决于 2014-04-01 16:46
 1 ALTER PROC [dbo].[SP_MA_GetTaskList_Query]
 2 (
 3     @Parm_UserCode NVARCHAR(60),
 4     @Parm_BillType NVARCHAR(30),
 5     @Parm_BillTypeCode NVARCHAR(255)
 6 )
 7 AS
 8 BEGIN
 9     declare @tmp_BillTypeCode  NVARCHAR(255)
10     select  @tmp_BillTypeCode = isnull(@Parm_BillTypeCode,'')
11     if  @tmp_BillTypeCode > '' 
12     begin
13         select @tmp_BillTypeCode = replace(@tmp_BillTypeCode,'-',',')+','
14     end 
15     else 
16     begin
17         select @Parm_BillType ='BillType'
18         select @tmp_BillTypeCode =replace('SO-PO-LN-PR-BN-OSET-QR','-',',')+','
19     end
20  
21     select a.UserCode,a.UserName,a.TaskItemType,a.LineItemID as TaskLineItemID,a.SPBillID, a.SPBillType, b.BillID,b.BillTypeCode,b.BillNO,b.BillNOExt, 
22         a.TaskItemTitle,a.TaskItemDescription,
23         CONVERT(varchar(12) , a.CreatedAt, 112) as CreatedDate , 
24         replace(CONVERT(varchar, a.CreatedAt, 108 ),':','') as CreatedTime,
25         (select top 1 UserCode  from SYS_Log where SYS_Log.SPBillID  = a.SPBillID and LogPoint = ''  order by LogTime asc) as CreatedUserCode,
26         (select top 1 UserName  from SYS_Log where SYS_Log.SPBillID  = a.SPBillID and LogPoint = ''  order by LogTime asc) as CreatedUserName,
27         (select top 1 UserCode  from SYS_Log where SYS_Log.SPBillID  = a.SPBillID and LogPoint > ''  order by LogTime desc) as PreUserCode,
28         (select top 1 UserName  from SYS_Log where SYS_Log.SPBillID  = a.SPBillID and LogPoint > ''  order by LogTime desc) as PreUserName
29         
30     from SYS_WorkflowTask a, BL_BillClips b
31     where a.spbillid = b.spbillid and 
32     a.lineitemid in (select min(lineitemid) from SYS_WorkflowTask c  where c.usercode = @Parm_UserCode and c.completedFlag='N' group by taskitemid ) AND
33     (@tmp_BillTypeCode ='' OR 
34         ( @tmp_BillTypeCode > '' and
35             ( 
36                 (@Parm_BillType ='SPBillType'  and   @tmp_BillTypeCode like '%'+a.SPBillType+',%' ) or 
37                 (@Parm_BillType ='BillType' and   @tmp_BillTypeCode like '%'+b.BillTypeCode+',%'  and a.SPBillType not in ('160','161','162','163','164','171','172','173','174','186','187','188'))
38             )
39         )
40     )
41     
42     UNION ALL
43 
44     select a.UserCode,a.UserName,a.TaskItemType,a.LineItemID as TaskLineItemID,a.SPBillID, a.SPBillType, '' as BillID, 'OSET' as BillTypeCode, '' as BillNO, '' as BillNOExt, 
45         a.TaskItemTitle,a.TaskItemDescription,
46         CONVERT(varchar(12) , a.CreatedAt, 112) as CreatedDate , 
47         replace(CONVERT(varchar, a.CreatedAt, 108 ),':','') as CreatedTime,
48         (select top 1 UserCode  from SYS_Log where SYS_Log.SPBillID  =a.SPBillID and LogPoint = '' order by LogTime asc) as CreatedUserCode,
49         (select top 1 UserName  from SYS_Log where SYS_Log.SPBillID  =a.SPBillID and LogPoint = '' order by LogTime asc) as CreatedUserName,
50         (select top 1 UserCode  from SYS_Log where SYS_Log.SPBillID  =a.SPBillID and LogPoint > '' order by LogTime desc) as PreUserCode,
51         (select top 1 UserName  from SYS_Log where SYS_Log.SPBillID  =a.SPBillID and LogPoint > '' order by LogTime desc) as PreUserName
52         
53     from SYS_WorkflowTask a 
54     where a.SPBillType in (select CVFrom  from SYS_CodeTranslation  where  CTCode ='SP.BillType' and CVTo ='OSET' ) and 
55     a.lineitemid in (select min(lineitemid) from SYS_WorkflowTask c where c.usercode = @Parm_UserCode and c.completedFlag='N' group by taskitemid ) AND
56     (
57         @tmp_BillTypeCode > '' or
58         ( 
59             @tmp_BillTypeCode > '' and
60             ( 
61                 (@Parm_BillType ='SPBillType'  and   @tmp_BillTypeCode like '%'+a.SPBillType+',%' ) or 
62                 (@Parm_BillType ='BillType' and   @tmp_BillTypeCode like '%OSET,%' )
63             )
64         )
65     )
66 
67 END

就给我这么一个存储过程,其他流程没说,我前面做了一点点,实在是没想法了,领导还是不满意。

守望雲影的主页 守望雲影 | 初学一级 | 园豆:14
提问于:2014-03-21 16:28
< >
分享
最佳答案
0

(select top 1 UserCode from SYS_Log where SYS_Log.SPBillID = a.SPBillID and LogPoint = '' order by LogTime asc) as CreatedUserCode, 26 (select top 1 UserName from SYS_Log where SYS_Log.SPBillID = a.SPBillID and LogPoint = '' order by LogTime asc) as CreatedUserName, 27 (select top 1 UserCode from SYS_Log where SYS_Log.SPBillID = a.SPBillID and LogPoint > '' order by LogTime desc) as PreUserCode, 28 (select top 1 UserName from SYS_Log where SYS_Log.SPBillID = a.SPBillID and LogPoint > '' order by LogTime desc) as PreUserName

 

这些语句改成一个关联语句,你这种写法很不合理,会很慢

a.lineitemid in (select min(lineitemid) from SYS_WorkflowTask c where c.usercode = @Parm_UserCode and c.completedFlag='N' group by taskitemid )

还有这里也会引起慢,最好不要用in

 

(@tmp_BillTypeCode ='' OR 34 ( @tmp_BillTypeCode > '' and 35 ( 36 (@Parm_BillType ='SPBillType' and @tmp_BillTypeCode like '%'+a.SPBillType+',%' ) or 37 (@Parm_BillType ='BillType' and @tmp_BillTypeCode like '%'+b.BillTypeCode+',%' and a.SPBillType not in ('160','161','162','163','164','171','172','173','174','186','187','188')) 38 ) 39 ) 40 )

 

还有这里也会引起慢

这些是最基本的引起慢的地方了,你可以看看它的索引建的是否合理,表结构建的是否合理。。。等等还有很多需要注意的地方。你自己按照这些思路慢慢的摸索吧。。。。。会有收获的!

 

如果最后优化到1秒以内,你就成功了!

收获园豆:20
华安 | 菜鸟二级 |园豆:226 | 2014-03-24 13:40
其他回答(4)
0

没法优化 把业务规则也写进来了吧 职责不够单一啊

收获园豆:20
Yu | 园豆:12990 (专家六级) | 2014-03-22 08:47
0
  select @Parm_BillType ='BillType'
         select @tmp_BillTypeCode =replace('SO-PO-LN-PR-BN-OSET-QR','-',',')+','
这个一般用set Parm_BillType ='BillType'

(select top 1 UserCode  from SYS_Log where SYS_Log.SPBillID  = a.SPBillID and LogPoint = ''  order by LogTime asc) as CreatedUserCode,
26         (select top 1 UserName  from SYS_Log where SYS_Log.SPBillID  = a.SPBillID and LogPoint = ''  order by LogTime asc) as CreatedUserName,
27         (select top 1 UserCode  from SYS_Log where SYS_Log.SPBillID  = a.SPBillID and LogPoint > ''  order by LogTime desc) as PreUserCode,
28         (select top 1 UserName  from SYS_Log where SYS_Log.SPBillID  = a.SPBillID and LogPoint > ''  order by LogTime desc) as PreUserName

这一堆我看你在使用了二次,那么可以在前面先查出来保存到变量;

a.lineitemid in (select min(lineitemid) from SYS_WorkflowTask c  where c.usercode = @Parm_UserCode and c.completedFlag='N' group by taskitemid ) AND
33     (@tmp_BillTypeCode ='' OR 
34         ( @tmp_BillTypeCode > '' and
35             ( 
36                 (@Parm_BillType ='SPBillType'  and   @tmp_BillTypeCode like '%'+a.SPBillType+',%' ) or 
37                 (@Parm_BillType ='BillType' and   @tmp_BillTypeCode like '%'+b.BillTypeCode+',%'  and a.SPBillType not in ('160','161','162','163','164','171','172','173','174','186','187','188'))
38             )
39         )
40     )

这个in查询,试试能不能改成连接查询。
收获园豆:20
幻天芒 | 园豆:37207 (高人七级) | 2014-03-22 22:17
0

先用工具分析一下那条语句执行时间多

收获园豆:20
walleyekneel | 园豆:306 (菜鸟二级) | 2014-03-23 01:47
0

不使用UNION ALL

收获园豆:20
MinKong | 园豆:202 (菜鸟二级) | 2014-03-24 09:47

union all 可比union快。

支持(0) 反对(0) 幻天芒 | 园豆:37207 (高人七级) | 2014-04-01 17:07
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册