在一个视图查询中,执行速度很慢,是因为执行调用两个函数导致,
大概查询8000条数据,不调用函数1秒就可以搞定,加上调用的函数,则需要1分50秒左右。
SQL语句如下:
select ea.ExpenseAppId,ea.ExpenseCode,ea.Template,
ea.StatusId,dbo.GetApprovalStatus_Module(ea.statusId,2) as Status,ea.CreateTime,ea.ProjectId,ea.ProgramId,
ea.CreatorId,dbo.GetStaffName(ea.CreatorId) as Creator,ea.ApplicantId,dbo.GetStaffName(ea.ApplicantId) as Applicant,
dbo.f_AppHistoryActApproverIdUserList(ea.ExpenseAppId,2)'ActApproverIdUserList',
dbo.f_AppHistoryOrgApproverIdUserList(ea.ExpenseAppId,2)'OrgApproverIdUserList'
from ExpenseApp ea
where ExpenseAppId in(select distinct(ExpenseAppId) from ApprovalHistory where ExpenseAppId!=0 and TripAppId=0)
函数如下:
ALTER function [dbo].[f_AppHistoryActApproverIdUserList](@id int,@ModuleId int)
returns varchar(1000)
as
begin
declare @r varchar(1000)
set @r=''
select @r=@r+','+ ltrim(str(ActApproverId)) from AppHistory where (StatusId>1 or Actions like 'Approv%' or Actions like 'Reject%') and ModuleId=@ModuleId and OrgApproverId=ActApproverId and ApplicationId=@id
return stuff(@r,1,1,'')+','
end
请问可以怎样优化?
大概查询8000条数据,不调用函数1秒就可以搞定,加上调用的函数,则需要1分50秒左右。
SQL语句如下:
select ea.ExpenseAppId,ea.ExpenseCode,ea.Template,
ea.StatusId,dbo.GetApprovalStatus_Module(ea.statusId,2) as Status,ea.CreateTime,ea.ProjectId,ea.ProgramId,
ea.CreatorId,dbo.GetStaffName(ea.CreatorId) as Creator,ea.ApplicantId,dbo.GetStaffName(ea.ApplicantId) as Applicant,
dbo.f_AppHistoryActApproverIdUserList(ea.ExpenseAppId,2)'ActApproverIdUserList',
dbo.f_AppHistoryOrgApproverIdUserList(ea.ExpenseAppId,2)'OrgApproverIdUserList'
from ExpenseApp ea
where ExpenseAppId in(select distinct(ExpenseAppId) from ApprovalHistory where ExpenseAppId!=0 and TripAppId=0)
函数如下:
ALTER function [dbo].[f_AppHistoryActApproverIdUserList](@id int,@ModuleId int)
returns varchar(1000)
as
begin
declare @r varchar(1000)
set @r=''
select @r=@r+','+ ltrim(str(ActApproverId)) from AppHistory where (StatusId>1 or Actions like 'Approv%' or Actions like 'Reject%') and ModuleId=@ModuleId and OrgApproverId=ActApproverId and ApplicationId=@id
return stuff(@r,1,1,'')+','
end
请问可以怎样优化?
-- 试试这个
SELECT ea.ExpenseAppId,ea.ExpenseCode,ea.Template,
ea.StatusId,dbo.GetApprovalStatus_Module(ea.statusId,2) as Status,ea.CreateTime,ea.ProjectId,ea.ProgramId,
ea.CreatorId,dbo.GetStaffName(ea.CreatorId) as Creator,ea.ApplicantId,dbo.GetStaffName(ea.ApplicantId) as Applicant,
STUFF(ap.ApproverIdUserList,1,1'') AS ActApproverIdUserList,
STUFF(ap.ApproverIdUserList,1,1,'') AS OrgApproverIdUserList
FROM ExpenseApp ea
CROSS APPLY (
SELECT ','+ LTRIM(str(ActApproverId)) FROM AppHistory WHERE ModuleId=2 and OrgApproverId=ActApproverId and ApplicationId=ea.ExpenseAppId
and (StatusId>1 or Actions like 'Approv%' or Actions like 'Reject%')
FOR XML PATH('')
) ap (ApproverIdUserList)
WHERE ea.ExpenseAppId IN(SELECT DISTINCT (ExpenseAppId) FROM ApprovalHistory WHERE ExpenseAppId!=0 and TripAppId=0)