Optimise help required

Felix Nathan 2017-05-04 11:40:50

declare @BookingID INT = NULL

,@FormNumber VARCHAR(50)=NULL 
 
,@ProductTypeID INT=NULL 
 
,@SectorID INT=NULL 
 
,@ProductID INT=NULL 
 
,@TourLaunchDetailsID INT=NULL 
 
,@GroupName VARCHAR(50)=NULL 
 
,@FromDate DATE=NULL 
 
,@ToDate DATE=NULL 
 
,@IFly INT=NULL 
 
,@FirstName VARCHAR(50)=NULL 
 
,@LastName VARCHAR(50)=NULL 
 
,@Phone VARCHAR(20)='9819062701'
 
,@BranchID INT=NULL 
 
,@OwnerID INT=NULL 
 
,@TourDateFrom DATE=NULL 
 
,@TourDateTo DATE=NULL 
 
,@IsAdmin BIT =0 
 
,@PageNo INT=1 
 
,@PageSize INT =100 
 
,@Provisional BIT = 0 
 
,@IsVIP BIT = NULL 
 
,@SchemeBookingID INT=NULL 
 
,@ProductCategoryID INT=NULL 
 
,@IncludeJL INT=NULL 
 
,@LocationID INT=NULL 
 
,@RefBookingID INT=NULL 
 
,@TravelTypeID INT=NULL 
 
,@BookingSourceID INT = NULL 
 
,@IFlyServiceAvailable INT=NULL 
 
,@PriceBandID INT=NULL 
 
,@FITCarTypeID INT=NULL 
 
,@FITSpecialAccommodationID INT=NULL 
 
,@PSPCoordinatorTeamID INT = NULL

BEGIN

SET NOCOUNT ON; 
 
SET ARITHABORT ON;   
BEGIN TRY 
 
    DECLARE @TotalRows INT, 
 
            @BookedCustomerOpenStatus INT, 
 
            @AllLocationID INT = NULL, 
 
            @BranchTypeBranch INT = NULL, 
 
            @BranchTypePSP INT = NULL   
    DECLARE @TotalBookedCustomer INT, @TotalIndianBookedCustomer INT, @TotalWorldBookedCustomer INT   
    SELECT @BookedCustomerOpenStatus=StaticID FROM StaticMaster WITH(NOLOCK) 
 
        WHERE EntityType='BookingStatus' AND Code='Open';   
    IF @BookingID=0 SET @BookingID=NULL   
    IF @FormNumber=''             SET @FormNumber=NULL   
    IF @ProductTypeID=0             SET @ProductTypeID=NULL   
    IF @SectorID=0          SET @SectorID=NULL   
    IF @ProductID='' 
 
        SET @ProductID=NULL   
    IF @TourLaunchDetailsID=0 
 
        SET @TourLaunchDetailsID=NULL   
    IF @GroupName='' 
 
        SET @GroupName=NULL   
    IF @FirstName='' 
 
        SET @FirstName=NULL   
    IF @LastName='' 
 
        SET @LastName=NULL   
    IF @Phone='' 
 
        SET @Phone=NULL   
    IF @IFly=2 
 
        SET @IFly=NULL   
    IF @BranchID=0 
 
        SET @BranchID=NULL   
    IF @OwnerID=0 
 
        SET @OwnerID=NULL   
    IF @ProductCategoryID=0 
 
        SET @ProductCategoryID=NULL   
    IF @IncludeJL=2 
 
        SET @IncludeJL=NULL            
    IF @LocationID=0 
 
        SET @LocationID=NULL 
 
 
 
    IF @IFlyServiceAvailable=2 
 
        SET @IFlyServiceAvailable=NULL   
    IF @PriceBandID=0 
 
        SET @PriceBandID=NULL   
    IF @FITCarTypeID=0 
 
        SET @FITCarTypeID=NULL   
    IF @FITSpecialAccommodationID=0 
 
        SET @FITSpecialAccommodationID=NULL   
    IF @BranchID < 0 
 
        BEGIN 
 
            SET @AllLocationID = @BranchID 
 
            SELECT @BranchTypeBranch = StaticID FROM StaticMaster WITH(NOLOCK) WHERE EntityType = 'BranchType' AND Code = 'Branch' 
 
            SELECT @BranchTypePSP = StaticID FROM StaticMaster WITH(NOLOCK) WHERE EntityType = 'BranchType' AND Code = 'PSP' 
 
            SET @BranchID = NULL 
 
        END   
    ;WITH CTEBookedCustomerCount 
 
    AS 
 
    ( 
 
        SELECT 
 
            PM.ProductTypeID  
 
            ,COUNT(*) AS BookedCustomerCount 
 
        FROM BookedCustomer BC WITH(NOLOCK) 
 
            INNER JOIN Booking BKG WITH(NOLOCK) ON BKG.BookingID = BC.BookingID 
 
                AND BC.StatusID=@BookedCustomerOpenStatus 
 
                AND ISNULL(BKG.IsActive,0) = (CASE @IsAdmin WHEN 0 THEN 1 ELSE BKG.IsActive END) 
 
                AND ISNULL(BC.IsActive,0)=1 
 
            INNER JOIN ProductMaster PM WITH(NOLOCK) ON PM.ProductID = BKG.ProductID 
 
            INNER JOIN TourLaunchDetails tld WITH(NOLOCK) ON tld.TourLaunchDetailsID = BKG.TourLaunchDetailsID 
 
            INNER JOIN CustomerMaster CM WITH(NOLOCK) ON CM.CustomerID = BC.CustomerID  
 
            INNER JOIN BranchMaster BM WITH(NOLOCK) ON BM.BranchID=BKG.BranchID 
 
            LEFT JOIN PSPMaster   WITH(NOLOCK) ON PSPMaster.BranchID=BKG.BranchID 
 
                AND PSPMaster.IsActive=1 
 
        WHERE 
 
            (BKG.BookingID=@BookingID OR @BookingID IS NULL) 
 
            AND (BKG.SchemeBookingID=@SchemeBookingID OR @SchemeBookingID IS NULL) 
 
            AND (FormNumber LIKE '%'+@FormNumber + '%' OR @FormNumber IS NULL) 
 
            AND (BKG.SectorID=@SectorID OR @SectorID IS NULL) 
 
            AND (BKG.ProductID=@ProductID OR @ProductID IS NULL) 
 
            AND (BKG.TourLaunchDetailsID=@TourLaunchDetailsID OR @TourLaunchDetailsID IS NULL) 
 
            AND (GroupName=@GroupName OR @GroupName IS NULL) 
 
            AND (CONVERT(DATE,BC.CreatedDate)>=@FromDate OR @FromDate IS NULL) 
 
            ANd (CONVERT(DATE,BC.CreatedDate)<=@ToDate OR @ToDate IS NULL) 
 
            AND (ISNULL(IsIFly,0)=@IFly OR @IFly IS NULL) 
 
            AND (cm.FirstName LIKE @FirstName+'%' OR @FirstName IS NULL) 
 
            AND (cm.LastName LIKE @LastName+'%' OR @LastName IS NULL) 
 
            AND (cm.Phone1=@Phone OR cm.Phone2=@Phone OR @Phone IS NULL)  
 
            AND (BKG.BranchID=@BranchID OR @BranchID IS NULL) 
 
            AND (BKG.CreatedBy = @OwnerID OR @OwnerID IS NULL) 
 
            AND (BKG.RefBookingID=@RefBookingID OR @RefBookingID IS NULL) 
 
            AND (CONVERT(DATE,tld.ActualDepartureDate)>=@TourDateFrom OR @TourDateFrom IS NULL) 
 
            AND (CONVERT(DATE,tld.ActualDepartureDate)<=@TourDateTo OR @TourDateTo IS NULL) 
 
            AND (PM.ProductTypeID = @ProductTypeID OR @ProductTypeID IS NULL) 
 
            AND (BM.BranchTypeID = (CASE @AllLocationID WHEN -1 THEN @BranchTypePSP WHEN -2 THEN @BranchTypeBranch ELSE 0 END) OR @AllLocationID IS NULL) 
 
            AND (PM.ProductCategoryID = @ProductCategoryID OR @ProductCategoryID IS NULL)    
 
            AND (BC.IsJL=@IncludeJL OR @IncludeJL IS NULL) 
 
            AND (BKG.LocationID=@LocationID OR @LocationID IS NULL) 
 
            AND (PM.TravelTypeID = @TravelTypeID OR @TravelTypeID IS NULL) 
 
            AND (BKG.BookingSourceID = @BookingSourceID OR @BookingSourceID IS NULL) 
 
            AND (ISNULL(PM.IFlyServiceAvailable,0)=@IFlyServiceAvailable OR @IFlyServiceAvailable IS NULL) 
 
            AND (BKG.PriceBandID = @PriceBandID OR @PriceBandID IS NULL) 
 
            AND (BKG.FITCarTypeID = @FITCarTypeID OR @FITCarTypeID IS NULL) 
 
            AND (BKG.FITSpecialAccommodationID = @FITSpecialAccommodationID OR @FITSpecialAccommodationID IS NULL) 
 
            AND (PSPMaster.PSPCoordinatorTeamID = @PSPCoordinatorTeamID OR @PSPCoordinatorTeamID IS NULL) 
 
        GROUP BY 
 
            PM.ProductTypeID 
 
    )   
    SELECT  
 
            @TotalWorldBookedCustomer=CASE ProductTypeID WHEN 1 THEN BookedCustomerCount ELSE @TotalWorldBookedCustomer END 
 
           ,@TotalIndianBookedCustomer=CASE ProductTypeID WHEN 2 THEN BookedCustomerCount ELSE @TotalIndianBookedCustomer END  
 
        FROM CTEBookedCustomerCount   
    SET @TotalWorldBookedCustomer = ISNULL(@TotalWorldBookedCustomer,0) 
 
    SET @TotalIndianBookedCustomer = ISNULL(@TotalIndianBookedCustomer,0)   
    SET @TotalBookedCustomer = @TotalWorldBookedCustomer + @TotalIndianBookedCustomer   
    ;WITH CTE 
 
    AS 
 
    ( 
 
        SELECT Booking.BookingID

,Booking.SectorID
,SectorMaster.SectorName
,Booking.ProductID
,(ProductMaster.ProductCode + '-' + ProductMaster.ProductTitle) AS ProductTitle
,ProductMaster.ProductCode
,ProductMaster.StandardFC
,ISNULL(ProductCurrency.Code,'INR') AS ProductCurrency
,Booking.TourLaunchDetailsID
,tld.ScheduleDate
,tld.ActualDepartureDate
,tld.ArrivalDate
,tld.TourCode
,Booking.FormNumber
,Booking.RoomTypeID
,RoomTypeMaster.RoomType
,Booking.GroupName
,Booking.AdultCount
,Booking.ChildCount
,Booking.InfantCount
,Booking.IsIFly
,Booking.IsJL
,Booking.IsNRI
,Booking.PreferredCurrencyID
,CurrencyMaster.Code AS PreferredCurrencyCode
,ISNULL(Booking.IsActive,0) IsActive
,Booking.CreatedDate
,cm.FirstName + ' ' + cm.LastName AS CustomerName
,cm.FirstName + ' Ji ' + cm.LastName AS GuestNameOnMessage
,ISNULL(cm.Phone1,cm.Phone2) AS Phone
,Booking.BranchID
,BranchMaster.BranchName
,Booking.CreatedBy
,ISNULL(Booking.AutoGenerateEnquiry,0)AutoGenerateEnquiry
,Booking.InvoiceNumber
,Booking.Status
,StaticMaster.Code AS BookingStatus
,UserMaster.FirstName+' '+UserMaster.LastName AS UserName
,ISNULL(BookingCancellation.BookingCancellationID,0) AS BookingCancellationID
,ISNULL(BookingCancellation.FutureTourPrinted,0) AS FutureTourPrinted
,ISNULL(BookingCancellation.TourLaunchDetailsID,0) AS FutureTourID
,ISNULL(BookingCancellation.CancelledBy,0) AS CancelledBy
,Booking.RefBookingID
,ISNULL(OldBooking.FormNumber,") AS RefFormNumber
,tld.Routing
,Booking.ReasonForChange
,Booking.SchemeBookingID
,Booking.LocationID
,sm1.Code AS LocationCode
,ProductMaster.TravelTypeID
,sm2.Code AS TravelTypeCode
,ProductTypeMaster.ProductType
,ProductTypeMaster.ProductTypeID
,ISNULL(ProductMaster.IFlyServiceAvailable,0) AS IFlyServiceAvailable
,Booking.BookingSourceID
,SM3.Code AS BookingSourceCode
,ProductCategoryMaster.ProductCategoryID
,ProductCategoryMaster.ProductCategory
,Booking.PriceBandID
,smPriceBand.Code AS PriceBandName
,Booking.FITCarTypeID
,smFITCarType.Code AS FITCarTypeCode
,Booking.FITSpecialAccommodationID
,smSpecialAccommodation.Code AS FITSpecialAccommodationCode

        FROM Booking WITH (INDEX (0))

INNER JOIN SectorMaster ON SectorMaster.SectorID = Booking.SectorID

INNER JOIN ProductMaster ON ProductMaster.ProductID = Booking.ProductID

INNER JOIN ProductTypeMaster ON ProductTypeMaster.ProductTypeID=ProductMaster.ProductTypeID

INNER JOIN TourLaunchDetails tld WITH (INDEX (0))
ON tld.TourLaunchDetailsID = Booking.TourLaunchDetailsID

INNER JOIN BranchMaster ON BranchMaster.BranchID=Booking.BranchID

INNER JOIN UserMaster ON UserMaster.UserID=Booking.CreatedBy

LEFT JOIN CurrencyMaster ProductCurrency ON ProductCurrency.CurrencyID=ProductMaster.StandardFC

LEFT JOIN RoomTypeMaster ON RoomTypeMaster.RoomTypeID = Booking.RoomTypeID

LEFT JOIN BookedCustomer bc WITH (INDEX (0)) ON bc.BookingID = Booking.BookingID AND bc.IsFamilyHead = 1 AND bc.IsActive = 1

LEFT JOIN CustomerMaster cm WITH (INDEX (0)) ON cm.CustomerID = bc.CustomerID

LEFT JOIN CurrencyMaster ON CurrencyMaster.CurrencyID = Booking.PreferredCurrencyID

LEFT JOIN StaticMaster ON StaticMaster.StaticID=Booking.[Status]

LEFT JOIN BookingCancellation ON BookingCancellation.BookingID=Booking.BookingID

LEFT JOIN Booking OldBooking ON OldBooking.BookingID = Booking.RefBookingID

LEFT JOIN StaticMaster sm1 ON sm1.StaticID=Booking.LocationID AND sm1.EntityType='ExLocation'

LEFT JOIN StaticMaster sm2 ON sm2.StaticID=ProductMaster.TravelTypeID AND sm2.EntityType='TravelType'

LEFT JOIN StaticMaster SM3 ON SM3.StaticID = Booking.BookingSourceID AND SM3.EntityType = 'BookingSource'

LEFT JOIN ProductCategoryMaster ON ProductCategoryMaster.ProductCategoryID = ProductMaster.ProductCategoryID

LEFT JOIN StaticMaster smPriceBand ON smPriceBand.StaticID=Booking.PriceBandID AND smPriceBand.EntityType='ProductPriceBand'

LEFT JOIN StaticMaster smFITCarType ON smFITCarType.StaticID=Booking.FITCarTypeID AND smFITCarType.EntityType='FITCarType'

LEFT JOIN StaticMaster smSpecialAccommodation ON smSpecialAccommodation.StaticID=Booking.FITSpecialAccommodationID AND smSpecialAccommodation.EntityType='FITSpecialAccommodation'

LEFT JOIN PSPMaster ON PSPMaster.BranchID=Booking.BranchID

AND PSPMaster.IsActive=1 
 
        WHERE (Booking.BookingID=@BookingID OR @BookingID IS NULL) 
 
            AND (Booking.SchemeBookingID=@SchemeBookingID OR @SchemeBookingID IS NULL) 
 
            AND (Booking.FormNumber LIKE '%'+@FormNumber + '%' OR @FormNumber IS NULL) 
 
            AND (ProductMaster.ProductTypeID=@ProductTypeID OR @ProductTypeID IS NULL) 
 
            AND (Booking.SectorID=@SectorID OR @SectorID IS NULL) 
 
            AND (Booking.ProductID=@ProductID OR @ProductID IS NULL) 
 
            AND (Booking.TourLaunchDetailsID=@TourLaunchDetailsID OR @TourLaunchDetailsID IS NULL) 
 
            AND (Booking.GroupName=@GroupName OR @GroupName IS NULL) 
 
            AND (CONVERT(DATE,Booking.CreatedDate)>=@FromDate OR @FromDate IS NULL) 
 
            ANd (CONVERT(DATE,Booking.CreatedDate)<=@ToDate OR @ToDate IS NULL) 
 
            AND (ISNULL(Booking.IsIFly,0)=@IFly OR @IFly IS NULL) 
 
            AND (Booking.BranchID=@BranchID OR @BranchID IS NULL) 
 
            AND (Booking.CreatedBy=@OwnerID OR @OwnerID IS NULL) 
 
            AND (Booking.RefBookingID=@RefBookingID OR @RefBookingID IS NULL) 
 
            AND (CONVERT(DATE,tld.ActualDepartureDate)>=@TourDateFrom OR @TourDateFrom IS NULL) 
 
            AND (CONVERT(DATE,tld.ActualDepartureDate)<=@TourDateTo OR @TourDateTo IS NULL) 
 
            AND ((@FirstName IS NULL AND @LastName IS NULL AND @Phone IS NULL AND @IncludeJL IS NULL) OR  
 
                EXISTS(SELECT BookedCustomerID FROM BookedCustomer WITH(NOLOCK) 
 
                    INNER JOIN CustomerMaster WITH(NOLOCK) ON CustomerMaster.CustomerID=BookedCustomer.CustomerID 
 
                    WHERE BookedCustomer.BookingID=Booking.BookingID 
 
                        AND (CustomerMaster.FirstName LIKE @FirstName+'%' OR @FirstName IS NULL) 
 
                        AND (CustomerMaster.LastName LIKE @LastName+'%' OR @LastName IS NULL) 
 
                        AND (CustomerMaster.Phone1=@Phone OR CustomerMaster.Phone2=@Phone OR @Phone IS NULL ) 
 
                        AND (BookedCustomer.IsJL=@IncludeJL OR @IncludeJL IS NULL) 
 
                        AND ISNULL(BookedCustomer.IsActive,0)=1 
 
            )) 
 
            AND (BranchMaster.BranchTypeID = (CASE @AllLocationID WHEN -1 THEN @BranchTypePSP WHEN -2 THEN @BranchTypeBranch ELSE 0 END) OR @AllLocationID IS NULL) 
 
            AND (EXISTS(SELECT 1 FROM CustomerMaster WITH(NOLOCK) 
 
                    INNER JOIN BookedCustomer WITH(NOLOCK) ON CustomerMaster.CustomerID=BookedCustomer.CustomerID 
 
                        AND BookedCustomer.StatusID=@BookedCustomerOpenStatus 
 
                    WHERE BookedCustomer.BookingID=Booking.BookingID 
 
                    AND ISNULL(BookedCustomer.IsActive,0)=1 
 
                    AND (ISNULL(CustomerMaster.IsVIP,0)=@IsVIP) OR @IsVIP IS NULL)) 
 
            AND (ProductMaster.ProductCategoryID = @ProductCategoryID OR @ProductCategoryID IS NULL)                     
 
            AND ISNULL(Booking.IsActive,0) = (CASE @IsAdmin WHEN 0 THEN 1 ELSE Booking.IsActive END) 
 
            AND (Booking.LocationID=@LocationID OR @LocationID IS NULL) 
 
            AND (ProductMaster.TravelTypeID = @TravelTypeID OR @TravelTypeID IS NULL) 
 
            AND (Booking.BookingSourceID = @BookingSourceID OR @BookingSourceID IS NULL) 
 
            AND (ISNULL(ProductMaster.IFlyServiceAvailable,0)=@IFlyServiceAvailable OR @IFlyServiceAvailable IS NULL) 
 
            AND (Booking.PriceBandID = @PriceBandID OR @PriceBandID IS NULL) 
 
            AND (Booking.FITCarTypeID = @FITCarTypeID OR @FITCarTypeID IS NULL) 
 
            AND (Booking.FITSpecialAccommodationID = @FITSpecialAccommodationID OR @FITSpecialAccommodationID IS NULL) 
 
            AND (PSPMaster.PSPCoordinatorTeamID = @PSPCoordinatorTeamID OR @PSPCoordinatorTeamID IS NULL) 
 
    ) 
 
    ,CTEBookedCustomerCount AS( 
 
        SELECT BC.BookingID, COUNT(BC.BookingID) as NoOfBookedCustomer 
        FROM BookedCustomer BC WITH(NOLOCK) 
 
        WHERE EXISTS(SELECT * FROM CTE WHERE BookingID=BC.BookingID) 
 
            AND ISNULL(BC.IsActive,0)=1 
 
            AND BC.StatusID=@BookedCustomerOpenStatus 
 
        GROUP BY BC.BookingID 
 
    ) 
 
    ,CTECount AS( 
 
        SELECT COUNT(*) AS TotalRows FROM CTE 
 
    )   
    SELECT  
 
            CTE.*, 
 
            dbo.fnGetTourPriceByCurrencyAsString(CTE.BookingID) AS TourPriceAsString, 
 
            dbo.fnGetOutstandingByCurrencyAsString(CTE.BookingID) AS OutstandingAsString, 
 
            Enquiries.EnquiryIDs, 
 
            Enquiries.EnquiryNos, 
 
            ISNULL(CTEBookedCustomerCount.NoOfBookedCustomer,0) AS NoOfBookedCustomer, 
 
            ISNULL(@TotalBookedCustomer,0) AS TotalBookedCustomer, 
 
            ISNULL(@TotalWorldBookedCustomer,0) AS TotalWorldBookedCustomer, 
 
            ISNULL(@TotalIndianBookedCustomer,0) AS TotalIndianBookedCustomer 
 
            ,CTECount.TotalRows 
 
        FROM CTE 
 
            LEFT JOIN CTEBookedCustomerCount ON CTEBookedCustomerCount.BookingID=CTE.BookingID 
 
            LEFT JOIN (SELECT * FROM DBO.fnGetBookingEnquiries(@BookingID))Enquiries ON Enquiries.BookingID=CTE.BookingID 
 
            CROSS APPLY CTECount 
 
        ORDER BY BookingID 
 
        OFFSET (((@PageNo*@PageSize)-@PageSize)) ROWS 
 
        FETCH NEXT @PAGESIZE ROWS ONLY;   
    END TRY   
BEGIN CATCH 
 
    EXEC spLogError; 
 
    THROW;   
END CATCH

END

Aaron Bertrand 2017-05-05 12:57:23
That is one big block of code. Why did you anonymize the plan if you posted your non-anonymized SQL anyway?