Optimise help required

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

avatar image By Felix Nathan 0 asked May 04 at 11:40 AM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ May 05 at 12:57 PM

That is one big block of code. Why did you anonymize the plan if you posted your non-anonymized SQL anyway?

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x588
x405
x84
x40

asked: May 04 at 11:40 AM

Seen: 190 times

Last Updated: May 05 at 12:57 PM