如何使用子句使用组来优化包含多个子查询的存储过程

Question

我有一个存储过程，可获取60个记录并花了7-8秒，提示改进了存储过程。

ALTER PROCEDURE [dbo].[Exat_ProductionLog]
    @Condition varchar(max) = ''
AS
BEGIN
    EXEC ('declare @startedstatus uniqueidentifier =  (select [dbo].[GetMasterSatusByName](''WIP''))
    DECLARE @Clarification uniqueidentifier =  (select [dbo].[GetMasterSatusByName](''OnHold''))
    DECLARE @Completed uniqueidentifier =  (select [dbo].[GetMasterSatusByName](''Completed''))
    SELECT A.*,Total =  A.NoofAccounts 
    FROM
        (SELECT DISTINCT 
             J.ReceivedOn ReceivedOn,
             CONVERT(varchar(10), J.ReceivedOn, 20) ReceivedDate,
             (SELECT TOP 1 
                  Convert(varchar(10), AssignedDate, 20) 
              FROM AssignedCase 
              WHERE CaseID = AC.CaseID 
              ORDER BY AssignedDate DESC) AssignedDate,
             UF.Name Facility, UT.Name [Type],
             C.CaseNumber Batch, C.ACHA ACHA, J.Noofaccounts,
             [BatchStartdate] = Convert(varchar(10), (select top 1 StatusOnUTC from log_CaseStatus where CaseID = C.CaseID and StatusID=@startedstatus order by StatusOnUTC asc),20),
             [AccountProcessed]=(select COUNT(*) from Account ACC inner join log_AccountStatus LA on LA.AccountID = ACC.AccountId where ACC.CaseId = C.CaseID and LA.StatusID=@Completed),
             [ClarificationLog] =(select dbo.[Exat_GetClarificationCount](C.CaseID)),
             [Status] = (select Name from [MasterStatus] where StatusId = (select [dbo].[GetStatusIdByCaseID](C.CaseID))),
             [Reviewer] =(select SUBSTRING((SELECT ( '','' + DisplayName)  from um_user where UserID in
                 (select UserID from AssignedCase where  CaseID = C.CaseID and [Type] in(''DC'',''Demo'',''Charges''))FOR XML PATH('''')), 2, 1000)),
                [Auditor] =(select SUBSTRING((SELECT ( '','' + DisplayName)  from um_user where UserID in
                 (select ValidatedBy from Account where Validated = 1 and CaseID = C.CaseID)FOR XML PATH('''')), 2, 1000)),
                [AuditCount] = (select Count(*) from Account where  CaseID = C.CaseID),
                [Errors] = (select Count(*) from log_AccountError LA inner join Account ACO on ACO.AccountID = LA.AccountID where  ACO.CaseID = C.CaseID)
     from [Job] J inner join [Case] C on J.JobID = C.JobID
     inner join log_casestatus lcs on lcs.caseid=c.caseid
     inner join [Account] A on C.CaseID = A.CaseID inner join [AssignedCase] AC on AC.CaseID = C.CaseID
     inner join um_Facility UF on UF.FacilityID = J.FacilityID
     inner join um_Type UT on UT.TypeID = J.TypeID
     inner join log_AccountStatus LA on LA.AccountID = A.AccountID)A  where 1=1 '+@Condition+'')

End

提高上述SP的性能的任何线索？

预先感谢。

Answer 1

我已经用不可重复的代码替换了您的存储过程，并且A仅使用您的表即可加入一次。尝试这样的事情：

ALTER PROCEDURE [dbo].[Exat_ProductionLog]
    @Condition varchar(max) = ''
AS
BEGIN
    EXEC ('declare @startedstatus uniqueidentifier =  (select [dbo].[GetMasterSatusByName](''WIP''))
    DECLARE @Clarification uniqueidentifier =  (select [dbo].[GetMasterSatusByName](''OnHold''))
    DECLARE @Completed uniqueidentifier =  (select [dbo].[GetMasterSatusByName](''Completed''))
    SELECT A.*,Total =  A.NoofAccounts 
    FROM
        (SELECT DISTINCT 
             J.ReceivedOn,
             CONVERT(varchar(10), J.ReceivedOn, 20) ReceivedDate,
             ASSC.AssignedDate,
             UF.Name Facility, UT.Name [Type],
             C.CaseNumber Batch, C.ACHA , J.Noofaccounts ,
             BSD.BatchStartdate,
             LSS.AccountProcessed,
             [ClarificationLog] =(select count(distinct LAS.AccountID ) from log_AccountStatus LAS where A.AccountID = LAS.AccountID and LAS.StatusID=@Clarification) ,                 
             [Status] = (select Name from [MasterStatus] where StatusId = (select [dbo].[GetStatusIdByCaseID](C.CaseID))),
             [Reviewer] = (select SUBSTRING((SELECT ( '','' + DisplayName)  
                           from um_user where UserID in
                 (select UserID from AssignedCase where  CaseID = C.CaseID and [Type] in(''DC'',''Demo'',''Charges''))FOR XML PATH('''')), 2, 1000)),
             [Auditor] =(select SUBSTRING((SELECT ( '','' + DisplayName)  from um_user where UserID in
                 (select ValidatedBy from Account where Validated = 1 and CaseID = C.CaseID)FOR XML PATH('''')), 2, 1000)),
             [AuditCount] = (select Count(*) from Account where  CaseID = C.CaseID),
             ERR.[Errors]
             from [Job] J 
             inner join [Case] C on J.JobID = C.JobID
             inner join [Account] A on C.CaseID = A.CaseID 
             inner join um_Facility UF on UF.FacilityID = J.FacilityID
             inner join um_Type UT on UT.TypeID = J.TypeID
             cross apply
             (
              select top 1 Convert(varchar(10), AC.AssignedDate, 20) as AssignedDate from AssignedCase AC
              where AC.CaseID = C.CaseID
              ORDER BY AC.AssignedDate DESC 
             ) ASSC
             cross apply
             (
              select Convert(varchar(10), min(case when lcs.StatusID=@startedstatus then lcs.StatusOnUTC else null end) ,20)  BatchStartdate  from log_CaseStatus lcs
              where lcs.CaseID = C.CaseID
             ) BSD
             cross apply
             (
              select sum(case when LA.StatusID=@Completed then 1 else 0 end) AccountProcessed   from log_AccountStatus la
              where LA.AccountID = A.AccountID
             ) LSS
             cross apply
             (
              select count(*) as [Errors]  from log_AccountError lae
              where A.AccountID = LAE.AccountID
             ) ERR
        )A  
        where 1=1 '+@Condition+'')

End

Answer 2

这是一个删除功能的新副本。

DECLARE @Condition varchar(max) = --insert value here
declare @startedstatus uniqueidentifier =  --insert value here
DECLARE @Clarification uniqueidentifier =  --insert value here
DECLARE @Completed uniqueidentifier =  --insert value here
declare @Clarification uniqueidentifier = (select StatusID from MasterStatus where Name=('OnHold')  --remove this from funtion so it only executes once
SELECT DISTINCT 
    J.ReceivedOn ReceivedOn
    ,CONVERT(varchar(10), J.ReceivedOn, 20)  AS ReceivedDate
    ,UF.Name Facility
    ,UT.Name [Type]
    ,C.CaseNumber Batch
    ,C.ACHA ACHA 
    ,J.Noofaccounts
    ,[AssignedDate] = (SELECT TOP 1 Convert(varchar(10), AssignedDate, 20) 
        FROM AssignedCase 
        WHERE CaseID = AC.CaseID 
        ORDER BY AssignedDate DESC)
    ,[BatchStartdate] = Convert(varchar(10), (select top 1 StatusOnUTC 
                                        from log_CaseStatus 
                                        where CaseID = C.CaseID 
                                            and StatusID=@startedstatus 
                                        order by StatusOnUTC asc),20)
    ,[AccountProcessed]=(select COUNT(*) 
                        from Account ACC inner join log_AccountStatus LA on LA.AccountID = ACC.AccountId 
                        where ACC.CaseId = C.CaseID and LA.StatusID=@Completed)
    --,[ClarificationLog] =(select dbo.[Exat_GetClarificationCount](C.CaseID))
    --REMOVE FUNCTION FROM STORED PROCEDURE
    ,[ClarificationLog] =(select COUNT(DISTINCT LA.AccountID)
                            from log_AccountStatus LA 
                            inner join Account A 
                            on A.AccountID = LA.AccountID 
                            where LA.StatusID=@Clarification 
                            and A.CaseId = C.CaseId)

    ,[Status] = (select Name from [MasterStatus] where StatusId = (select [dbo].[GetStatusIdByCaseID](C.CaseID)))
    ,[Reviewer] =(select SUBSTRING((SELECT ( ',' + DisplayName)  from um_user where UserID in
                    (select UserID from AssignedCase where  CaseID = C.CaseID and [Type] in('DC','Demo','Charges'))FOR XML PATH('')), 2, 1000))
    ,[Auditor] =(select SUBSTRING((SELECT ( ',' + DisplayName)  from um_user where UserID in
                    (select ValidatedBy from Account where Validated = 1 and CaseID = C.CaseID)FOR XML PATH('')), 2, 1000))
    ,[AuditCount] = (select Count(*) from Account where  CaseID = C.CaseID)
    ,[Errors] = (select Count(*) from log_AccountError LA inner join Account ACO on ACO.AccountID = LA.AccountID where  ACO.CaseID = C.CaseID)
from 
    [Job] J 
        inner join [Case] C 
            on 
            J.JobID = C.JobID
        inner join log_casestatus lcs 
            on 
            lcs.caseid=c.caseid
        inner join [Account] A 
            on 
            C.CaseID = A.CaseID 
        inner join [AssignedCase] AC 
            on 
            AC.CaseID = C.CaseID
        inner join um_Facility UF 
            on 
            UF.FacilityID = J.FacilityID
        inner join um_Type UT 
            on 
            UT.TypeID = J.TypeID
        inner join log_AccountStatus LA 
            on LA.AccountID = A.AccountID
        ;