如何修复具有多个状态的单表选择查询

Question

我有一个查询，它在单个表上使用多个语句。在哪里可以更改以优化查询？

查询是从具有多个选择的单表中提取计数，需要减少查询大小，优化查询。

select DISTINCT
       UM.Name as SalesExeceutiveName,
       MTPDet.FromDate,
       MTPDet.ToDate,
       (select Name from UserMaster where id=MTP.CreatedBy) as CreatedBy,
       (select Top 1 RM.Name
        from MTPDetailsStatusHistory MDSH
             inner join RouteMaster RM ON MDSH.RouteId=RM.Id
        where IsActiveRoute=1
          and MTPDetailsId=MTPDet.Id
        order by MDSH.Id Desc) As RouteName,
       ISnull((select Count(VisiteTypeId) from MTPVisitMaster where MTPDetailsId=MTPDet.Id and VisiteTypeId=1  Group By MTPDetailsId,VisiteTypeId),0)  As AssignedVisit, 
       ISnull( (select  Count(VisiteTypeId) from MTPVisitMaster where MTPDetailsId=MTPDet.Id and VisiteTypeId=2  Group By MTPDetailsId,VisiteTypeId),0) As  UnassignedVisit,
       ISnull( (select  Count(VisiteTypeId) from MTPVisitMaster where MTPDetailsId=MTPDet.Id and VisiteTypeId=4  Group By MTPDetailsId,VisiteTypeId),0) As  FollowUpVisit,
       ISnull((select Count(VisiteTypeId) from MTPVisitMaster where MTPDetailsId=MTPDet.Id),0)  As TotalVisit,
       ISnull((select Count(VisiteTypeId) from MTPVisitMaster where MTPDetailsId=MTPDet.Id and VisiteTypeId!=6 ),0)  As ActualVisit,
       ISnull((select Count(VisiteTypeId) from MTPVisitMaster where MTPDetailsId=MTPDet.Id and VisiteTypeId=6 ),0)  As CancelledVisit,
       ISnull((select Count(IsCheckInOnTime) from MTPVisitMaster where MTPDetailsId=MTPDet.Id and IsCheckInOnTime=0 ),0) As LateEntry,
       IsNULL(MtpVM.Distance,0) As Distance,
       IsNULL((select Count(Id) from DivisionOrders where OrderDate between MTPDet.FromDate and MTPDet.ToDate and SalesRepresentativeId=MTP.UserId) +
              (select Count(Id) from DistributorOrders where OrderDate between MTPDet.FromDate and MTPDet.ToDate and SalesRepresentativeId=MTP.UserId),0) ProductiveVisit,
       MtpVM.Remark  
from MTPMaster MTP 
     Inner join MTPDetails MTPDet      ON MTP.Id=MTPDet.MTPId
     inner join UserMaster UM on UM.Id=MTP.UserId
     Inner Join MTPDetailsStatusHistory MtpDetStHistory ON MTPDet.Id=MtpDetStHistory.MTPDetailsId
     Inner Join MTPVisitMaster MtpVM ON MtpVM.MTPDetailsId=MTPDet.Id      
     Inner join VisitType VT ON MtpVM.VisiteTypeId=VT.Id
WHERE MTP.UserId in (SELECT UM.Id As UserId
                     FROM UserMaster UM 
                     WHERE UM.IsDeleted=0 And UM.Id in (select UserH.UserId
                                                        from UserHQ UserH 
                                                        where UserH.HQId in (select HQM.Id from HQMaster HQM Where HQM.DivisionId=19)) )

我需要对查询进行优化，并使用与上一次相同的输出进行新的修改。

Answer 1

这是一个纯粹的猜测答案，因为我们没有样本数据和预期的结果。不过，就像我说的，这里似乎需要有条件的聚合。我删除了许多子查询，并移动了一个，并将它们转换为条件聚合。

虽然这里有很多猜测，但这应该接近你所需要的。希望，如果它不太正确，您可以很容易地纠正它：

SELECT UMse.[Name] as SalesExeceutiveName,
       MTPDet.FromDate,
       MTPDet.ToDate,
       UMc.[Name] AS CreatedBy,
       RN.[Name] AS RouteName,
       COUNT(CASE WHEN MTPVM.VisiteTypeId = 1 THEN 1 END) AS AssignedVisit,
       COUNT(CASE WHEN MTPVM.VisiteTypeId = 2 THEN 1 END) AS UnassignedVisit,
       COUNT(CASE WHEN MTPVM.VisiteTypeId = 4 THEN 1 END) AS FollowUpVisit,
       COUNT(VisiteTypeId) AS TotalVisit,
       COUNT(CASE WHEN MTPVM.VisiteTypeId != 6 THEN 1 END) AS ActualVisit,
       COUNT(CASE WHEN MTPVM.VisiteTypeId = 6 THEN 1 END) AS CancelledVisit,
       COUNT(CASE WHEN IsCheckInOnTime = 0 THEN 1 END) AS LateEntry,
       MTPVM.Distance, --Is is ASSUMED distance is the same for all rows.
       --Unlikely this will give a big benefit by being moved
       (SELECT Count(DivO.Id) FROM DivisionOrders DivO    WHERE DivO.OrderDate BETWEEN MTPDet.FromDate AND MTPDet.ToDate AND DivO.SalesRepresentativeId = MTP.UserId) + --COUNT cannot return 0, so no need for ISNULL
       (SELECT Count(DisO.Id) FROM DistributorOrders DisO WHERE DivO.OrderDate BETWEEN MTPDet.FromDate AND MTPDet.ToDate AND DisO.SalesRepresentativeId = MTP.UserId) AS ProductiveVisit,
       MTPVM.Remark
from dbo.MTPMaster MTP
     JOIN dbo.UserMaster UMc ON MTP.CreatedBy = UMc.id
     JOIN dbo.UserMaster UMse ON MTP.UserId = UMse.Id
     JOIN MTPDetails MTPDet ON MTP.Id=MTPDet.MTPId
     --Inner Join MTPDetailsStatusHistory MtpDetStHistory ON MTPDet.Id=MtpDetStHistory.MTPDetailsId --This is never reference, so I doubt it's needed.
     CROSS APPLY (SELECT TOP (1)
                         RM.[Name]
                  FROM dbo.MTPDetailsStatusHistory MDSH
                       JOIN dbo.RouteMaster RM ON MDSH.RouteId = RM.Id
                  WHERE RM.IsActiveRoute = 1 --Guessed Alias
                    AND MDSH.MTPDetailsId = MTPDet.Id --Guessed Alias
                  ORDER BY MDSH.Id DESC) RN
     JOIN dbo.MTPVisitMaster MTPVM ON MTPDet.Id = MTPVM.MTPDetailsId
     --Below is a guess, as a subquery, in a subquery in a subquery is just a mess I'm afraid. I suspect it's a 1 to 1 relationship
     JOIN dbo.UserHQ UHQ ON UMse.id = UGW.Userid
     JOIN dbo.HQMaster HQM ON UGW.HQId = HQM.id
WHERE UMse.IsDeleted = 0
  AND HQM.DivisionId = 19
GROUP BY UMse.[Name],
         MTPDet.FromDate,
         MTPDet.ToDate,
         UMc.[Name],
         RN.[Name],
         MTPVM.Distance
         MTPVM.Remark;

注意，我假设您的所有对象都在dbo模式上。

Answer 2

我可以看到两个眼前的问题。

1-选择不同--在我的经验中，这里不是不需要的，就是掩盖了坏的表联接中的行的重复。把它拿出来，注意行数，如果它增加了，就开始找出是什么原因造成的。如果您确实需要将查询的一部分区分开来，那么就分别处理它，然后加入到不需要它的部分。

相关子查询。select语句中有七个列，每个列来自表MTPVisitMaster (主from子句中也有)。这是一张桌子的八条读数。调查交叉表查询 --这将允许您一次执行所有这些子查询。

Answer 3

您可以在交叉应用程序中对计数子查询(来自MTPVisitMaster)进行分组。

select DISTINCT
       UM.Name as SalesExeceutiveName,
       MTPDet.FromDate,
       MTPDet.ToDate,
       (select Name from UserMaster where id=MTP.CreatedBy) as CreatedBy,
       (select Top 1 RM.Name
        from MTPDetailsStatusHistory MDSH
             inner join RouteMaster RM ON MDSH.RouteId=RM.Id
        where IsActiveRoute=1
          and MTPDetailsId=MTPDet.Id
        order by MDSH.Id Desc) As RouteName,
       X.AssignedVisit, 
       X.UnassignedVisit,
       X.FollowUpVisit,
       X.TotalVisit,
       X.ActualVisit,
       X.CancelledVisit,
       X.LateEntry,
       IsNULL(MtpVM.Distance,0) As Distance,
       IsNULL((select Count(Id) from DivisionOrders where OrderDate between MTPDet.FromDate and MTPDet.ToDate and SalesRepresentativeId=MTP.UserId) +
              (select Count(Id) from DistributorOrders where OrderDate between MTPDet.FromDate and MTPDet.ToDate and SalesRepresentativeId=MTP.UserId),0) ProductiveVisit,
       MtpVM.Remark  
from MTPMaster MTP 
     Inner join MTPDetails MTPDet      ON MTP.Id=MTPDet.MTPId
     inner join UserMaster UM on UM.Id=MTP.UserId
     Inner Join MTPDetailsStatusHistory MtpDetStHistory ON MTPDet.Id=MtpDetStHistory.MTPDetailsId
     Inner Join MTPVisitMaster MtpVM ON MtpVM.MTPDetailsId=MTPDet.Id      
     Inner join VisitType VT ON MtpVM.VisiteTypeId=VT.Id
     Cross Apply (
            select 
                Count(CASE WHEN VisiteTypeId=1 THEN VisiteTypeId END) AssignedVisit,
                Count(CASE WHEN VisiteTypeId=2 THEN VisiteTypeId END) UnassignedVisit,
                Count(CASE WHEN VisiteTypeId=4 THEN VisiteTypeId END) FollowUpVisit,
                Count(VisiteTypeId) TotalVisit,
                Count(CASE WHEN VisiteTypeId!=6 THEN VisiteTypeId END) ActualVisit,
                Count(CASE WHEN VisiteTypeId=6 THEN VisiteTypeId END) CancelledVisit,
                Count(CASE WHEN IsCheckInOnTime=0  THEN IsCheckInOnTime END) LateEntry,

            from MTPVisitMaster where MTPDetailsId=MTPDet.Id
        ) X
WHERE MTP.UserId in (SELECT UM.Id As UserId
                     FROM UserMaster UM 
                     WHERE UM.IsDeleted=0 And UM.Id in (select UserH.UserId
                                                        from UserHQ UserH 
                                                        where UserH.HQId in (select HQM.Id from HQMaster HQM Where HQM.DivisionId=19)) )