how to fix the query for single table selection with multiple statment

Question

I have one query which is use multiple statement on single table. Where I can change to optimize the query?

Query is fetching count from single table with multiple selection ,I need to reduce the query size and optimized the query.

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)) )

I need to optimized the query with the same output like previous one with new modification.

Answer 1

This is a pure guesswork answer, as we have no sample data and expected results. Like I said though, seems like we need conditional aggregation here. I've removed a lot of the subqueries, and moved one, and turned them into conditional aggregates.

There's a lot of guesses here though, but this should be near to what you need. Hopefully, if it isn't quite right, you can easily correct it:

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;

Note I have assumed all your objects are on the dbo schema.