帮忙优化下下面的语句吧。
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
就给我这么一个存储过程，其他流程没说，我前面做了一点点，实在是没想法了，领导还是不满意。
最佳答案
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
其他回答(4)
0
没法优化 把业务规则也写进来了吧 职责不够单一啊
收获园豆：20
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
0
先用工具分析一下那条语句执行时间多
收获园豆：20
0
不使用UNION ALL
收获园豆：20
union all 可比union快。
