I need to optimize the following stored proc.  Please let me know of any techniques or modifications that I can make to optimize this piece of code. The procedure is for a report that needs to run and span a couple of months' worth of data.ALTER PROCEDURE [dbo].[UP_Report_OverallSales_SP]  @DateType INT = 1, --default date type to recognised date   @DateFrom DATETIME = null,  @DateTo DATETIME = null ,  @BrokerEmail VARCHAR(50) = null ,  @EventName VARCHAR(50) = null,  @SellerTypeList varchar(max) = '500,550,600', --default 'Large,Indy, Platinum'  @AffiliateID INT = null,  @AffiliateCategoryID INT  = NULL,  @StatusIDList varchar(max) = '2,3,5', --default 'Prcoessed,Completed, Dispatched'  @FraudStatusIDList varchar(100)  = '1', --default 'green'  @LMSOrders BIT  = 0AS   BEGIN    SET NOCOUNT ON    Declare @ProcessedDateFrom DATETIME = NULL    Declare @ProcessedDateTo DATETIME = NULL    Declare @RecognisedDateFrom DATETIME = NULL    Declare @RecognisedDateTo DATETIME = NULL    IF @DateType = 1 -- 1 is recognised date         begin          set @RecognisedDateFrom = @DateFrom        set @RecognisedDateTo = @DateTo        end    IF @DateType = 2 -- 2 is processed date          begin        set @ProcessedDateFrom = @DateFrom        set @ProcessedDateTo = @DateTo        end    if @AffiliateID is null         set @AffiliateID = -1    if @AffiliateCategoryID is null     set @AffiliateCategoryID = -1    Declare @BrokerId INT = null    IF @EventName =''           SELECT @EventName = NULL    IF @BrokerEmail =''          SELECT @BrokerEmail = NULL    -- get broker id from the broker email passed as a parameter    IF @BrokerEmail IS NOT NULL       Select @BrokerId = UserId from tbUser where email = @BrokerEmail    --create seller type table      --this will have all comma separated seller type ids     Declare @SellerTypeTable Table (SellerTypeId  int not null)    If @SellerTypeList is not null     Insert into @SellerTypeTable (SellerTypeId) Select ID from dbo.FN_SplitToINT(@SellerTypeList, ',')    --create status id table      --this will have all comma separated status id    DECLARE @StatusIDTable TABLE (StatusID INT NOT NULL)    If @StatusIDList is not null     INSERT INTO @StatusIDTable (StatusID) SELECT ID FROM dbo.FN_SplitToINT(@StatusIDList, ',')    --create fraud status id table      --this will have all comma separated fraud status id     DECLARE @FraudStatusIDTable TABLE (FraudStatusID INT NOT NULL)    If @FraudStatusIDList is not null     INSERT INTO @FraudStatusIDTable (FraudStatusID) SELECT ID FROM dbo.FN_SplitToINT(@FraudStatusIDList, ',')      DECLARE @SaleWithDatesTable TABLE      (       SaleID INT NOT NULL,       SaleDate DATETIME NULL,       RecognisedDate DATETIME NULL,       ProcessedDate DATETIME NULL,       CompletedDate DATETIME NULL,       CancellationDate DATETIME NULL,       ShippedDate DATETIME NULL,       DroppedDate DATETIME NULL,       StatusID INT NULL,       PRIMARY KEY (SaleID)      )      INSERT INTO @SaleWithDatesTable (SaleID)      SELECT DISTINCT s.SaleID      FROM        tbSale s        INNER JOIN tbTicketHistory th ON s.TicketHistoryID = th.TicketHistoryID      WHERE         SaleID IN            (            select SaleID            from tbSaleHistory             where ISNULL(FraudStatusID,1) in (select fraudstatusid from @FraudStatusIDTable)            group by SaleID            having MIN(UpdateTime) between @DateFrom AND @DateTo            )        AND (th.BrokerID = @BrokerID         OR @BrokerID IS NULL)        AND             (s.AffiliateID = @AffiliateID             OR @AffiliateID = - 1)        --print 'Table @SaleWithDatesTable'    --select * from @SaleWithDatesTable    -- 600 -- indy seller    -- 550 -- platinum broker     -- 500 -- is broker       DECLARE @SaleIdsForIndySellersTable TABLE      (       SaleID INT NOT NULL,       PRIMARY KEY (SaleID)      )      DECLARE @SaleIdsForPlatinumSellersTable TABLE      (       SaleID INT NOT NULL,       PRIMARY KEY (SaleID)      )     -- insert all saleids that belong to indy seller in table @SaleIdsForIndySellersTable     INSERT INTO @SaleIdsForIndySellersTable (SaleID)     SELECT DISTINCT s.SaleID     FROM tbSale s      INNER JOIN [tbTicketHistory] th ON s.[TicketHistoryID] = th.[TicketHistoryID]      INNER JOIN [tbUserRole] ur ON th.[BrokerID] = ur.[UserID] AND ur.[RoleID] = 600     -- insert all saleids that belong to platinum seller in table @SaleIdsForPlatinumSellersTable     INSERT INTO @SaleIdsForPlatinumSellersTable (SaleID)     SELECT DISTINCT s.SaleID     FROM tbSale s      INNER JOIN [tbTicketHistory] th ON s.[TicketHistoryID] = th.[TicketHistoryID]      INNER JOIN [tbUserRole] ur ON th.[BrokerID] = ur.[UserID] AND ur.[RoleID] = 550     -- remove all saleids that do not belong to indy seller from @SaleWithDatesTable      -- if seller type selected do not have indy seller    IF NOT EXISTS (SELECT 1 FROM @SellerTypeTable WHERE SellerTypeId = 600) -- INDY      DELETE FROM @SaleWithDatesTable WHERE saleid IN (SELECT saleid FROM @SaleIdsForIndySellersTable)        -- remove all saleids that do not belong to platinum seller from @SaleWithDatesTable      -- if seller type selected do not have platinum seller      IF NOT EXISTS (SELECT 1 FROM @SellerTypeTable WHERE SellerTypeId = 550) -- PLATINUM      DELETE FROM @SaleWithDatesTable WHERE saleid IN (SELECT saleid FROM @SaleIdsForPlatinumSellersTable)       -- remove all saleids that belong to neither platinum nor indy sellers    -- if seller type selected do not have LARGE seller      IF NOT EXISTS (SELECT 1 FROM @SellerTypeTable WHERE SellerTypeId = 500) -- LARGE       DELETE FROM @SaleWithDatesTable WHERE saleid NOT IN (SELECT saleid FROM @SaleIdsForIndySellersTable)                                      AND saleid not in (SELECT saleid FROM @SaleIdsForPlatinumSellersTable)      -- update dates in the table     UPDATE @SaleWithDatesTable SET RecognisedDate = SH2.UpdateTime    from     (    SELECT SH.SaleID, Min(SH.UpdateTime) as 'UpdateTime'    from tbSaleHistory SH    INNER JOIN @SaleWithDatesTable S ON SH.SaleID = S.SaleID    where     ISNULL(SH.FraudStatusID,1) IN (select fraudstatusid from @FraudStatusIDTable)     -- SH.FraudStatusID IN (select fraudstatusid from @FraudStatusIDTable)     and SH.statusID in (select statusid from @StatusIDTable)    GROUP BY SH.SaleID    ) SH2    INNER JOIN @SaleWithDatesTable S ON SH2.SaleID = S.SaleID     UPDATE @SaleWithDatesTable SET ProcessedDate = ssd.UpdateTime FROM        tbSaleStatusDate ssd        INNER JOIN @SaleWithDatesTable s ON ssd.SaleID = s.SaleID      WHERE ssd.StatusID = 2    -----------------------------------------------------------------------------------    UPDATE @SaleWithDatesTable SET CompletedDate = ssd.UpdateTime FROM        tbSaleStatusDate ssd        INNER JOIN @SaleWithDatesTable s ON ssd.SaleID = s.SaleID      WHERE ssd.StatusID = 3    -----------------------------------------------------------------------------------    UPDATE @SaleWithDatesTable SET CancellationDate = ssd.UpdateTime FROM        tbSaleStatusDate ssd        INNER JOIN @SaleWithDatesTable s ON ssd.SaleID = s.SaleID      WHERE ssd.StatusID = 4    -----------------------------------------------------------------------------------         UPDATE @SaleWithDatesTable SET ShippedDate = ssd.UpdateTime FROM        tbSaleStatusDate ssd        INNER JOIN @SaleWithDatesTable s ON ssd.SaleID = s.SaleID      WHERE ssd.StatusID = 5    -----------------------------------------------------------------------------------      UPDATE @SaleWithDatesTable SET DroppedDate = ssd.UpdateTime FROM        tbSaleStatusDate ssd        INNER JOIN @SaleWithDatesTable s ON ssd.SaleID = s.SaleID      WHERE ssd.StatusID = 9    -----------------------------------------------------------------------------------    UPDATE @SaleWithDatesTable SET SaleDate = ssd.UpdateTime FROM        tbSaleStatusDate ssd        INNER JOIN @SaleWithDatesTable s ON ssd.SaleID = s.SaleID      WHERE ssd.StatusID = 1    --print @RecognisedDateFrom      --print @RecognisedDateTo    --select * from @SaleWithDatesTable    IF @RecognisedDateFrom IS NOT NULL         DELETE FROM @SaleWithDatesTable WHERE RecognisedDate  < @RecognisedDateFrom or RecognisedDate  IS NULL    IF @RecognisedDateTo IS NOT NULL     DELETE FROM @SaleWithDatesTable WHERE RecognisedDate  > @RecognisedDateTo or RecognisedDate  IS NULL    IF @ProcessedDateFrom IS NOT NULL       DELETE FROM @SaleWithDatesTable WHERE ProcessedDate < @ProcessedDateFrom or ProcessedDate IS NULL    IF @ProcessedDateTo IS NOT NULL       DELETE FROM @SaleWithDatesTable WHERE ProcessedDate > @ProcessedDateTo or ProcessedDate IS NULL    select TSH.*     into #tmptbSaleHistory    from tbSaleHistory TSH    left Join @SaleWithDatesTable SD ON (SD.saleid = TSH.saleid)    WHERE TSH.StatusID IN (Select StatusID from @StatusIDTable)    and TSH.saleHistoryID in    (select min(saleHistoryID) from tbSaleHistory where StatusID IN (Select StatusID from @StatusIDTable)and SaleID in (select SaleID from @SaleWithDatesTable) group by SaleID)    SELECT DISTINCT      TS.ApplicationID,      TAL.NAME AS 'ApplicationName',      TS.SaleID,      SD.SaleDate AS 'Saledate',      SD.RecognisedDate AS 'RecognisedDate',      SD.ProcessedDate AS 'ProcessedDate',      SD.CompletedDate AS 'CompletedDate',      SD.CancellationDate AS 'CancellationDate',      SD.ShippedDate AS 'ShippedDate',      SD.DroppedDate AS 'DroppedDate',      TSS.[ShowDate],      TTH.BrokerID,      ISNULL(TA2.Company,'') AS 'Broker',      TEC.[Name] AS 'EventName',      TEC.EventID,      TSH.Quantity,      CAST(ROUND((TSH.SellAmount / ((100 + TSH.MarkupRate) / 100)) / TSH.ExchangeRateToGBP, 2) AS decimal(10,2)) AS 'TicketAmountWoMarkup', --for Tiered fee no change      CAST(ROUND((TSH.Quantity * TSH.SellAmount / ((100 + TSH.MarkupRate) / 100)) / TSH.ExchangeRateToGBP, 2) as decimal(10,2)) AS 'TotalTicketAmountWoMarkup', --for Tiered fee no change      TSH.MarkupRate,      TSH.InitialMarkupRate,      CASE WHEN TSH.FeeTypeID = 1      THEN        CAST(ROUND(((TSH.Quantity * (TSH.MarkupRate / (100 + TSH.MarkupRate) * TSH.SellAmount)) + TSH.TieredProcessingFee) / TSH.ExchangeRateToGBP, 2) as decimal(10,2))      ELSE        CAST(ROUND((TSH.Quantity * (TSH.MarkupRate / (100 + TSH.MarkupRate) * TSH.SellAmount)) / TSH.ExchangeRateToGBP, 2) as decimal(10,2))      END  AS 'MarkupAmount',      CAST(ROUND(TSH.ShippingAmount / TSH.ExchangeRateToGBP, 2) as decimal(10,2))  AS 'ShippingAmount',      TSH.TaxRate,      CAST(ROUND(TSH.TaxAmount / TSH.ExchangeRateToGBP, 2) as decimal(10,2)) AS 'TaxAmount',      CAST(ROUND(TSH.DiscountAmount / TSH.ExchangeRateToGBP, 2) as decimal(10,2)) AS 'DiscountAmount',      CAST(ROUND(TSH.ServiceChargeAmount / TSH.ExchangeRateToGBP, 2) as decimal(10,2)) AS 'ServiceChargeAmount',      CASE WHEN TSH.FeeTypeID = 1      THEN        CAST(ROUND((((TSH.Quantity * TSH.SellAmount) + TSH.TieredProcessingFee) + TSH.ShippingAmount) / TSH.ExchangeRateToGBP, 2) as decimal(10,2))      ELSE        CAST(ROUND((TSH.Quantity * TSH.SellAmount + TSH.ShippingAmount) / TSH.ExchangeRateToGBP, 2) as decimal(10,2))      END  AS 'APamount',      CAST(ROUND((TSH.Quantity * TSH.SellAmount / ((100 + TSH.MarkupRate) / 100) + TSH.ShippingAmount) / TSH.ExchangeRateToGBP, 2) as decimal(10,2)) AS 'ARamount', --for Tiered fee no change      CAST(ROUND(TSH.TotalAmount / TSH.ExchangeRateToGBP, 2) as decimal(10,2)) AS 'TotalAmount',      TS.StatusID,      TSSL.Description AS 'Status',      tvc.venuename 'Venue',      TCCV.CityName VenueCity,      ISNULL(TS.FraudStatusID,1) as 'FraudStatusID',      TTH.FaceAmount,      TCML.currencyName as 'CurrencyName',      CASE             WHEN EXISTS (SELECT 1 FROM tbUserRole AS UR                WHERE (UR.RoleID = 600) AND (UR.UserID = TTH.BrokerID))                THEN 'I'            WHEN EXISTS (SELECT 1 FROM tbUserRole AS UR                WHERE (UR.RoleID = 550) AND (UR.UserID = TTH.BrokerID))                THEN 'P'            WHEN EXISTS (SELECT 1 FROM tbUserRole AS UR                WHERE (UR.RoleID = 500) AND (UR.UserID = TTH.BrokerID))                THEN 'L'            ELSE 'NA'        END AS 'SellerType',      ISNULL(TS.Lock,0) as 'IsLocked',      TSH.CommissionRate,      TSH.CommissionAmount,      TS.ShippingMethodID,      TS.ShippingText,      TS.TrackingNumber,      TS.IsPaid,      TU.PayPalID,      PML.Description as 'PaymentMethod'    INTO      #T1    FROM      @SaleWithDatesTable SD       INNER JOIN [tbSale] TS (NOLOCK) ON TS.SaleID = SD.SaleID      INNER JOIN #tmptbSaleHistory TSH ON TSH.saleID = SD.SaleID      INNER JOIN tbTicketHistory TTH ON TS.TicketHistoryID = TTH.TicketHistoryID      INNER JOIN tbshow TSS ON TSS.[ShowID] = TTH.[ShowID]      INNER JOIN [tbSaleStatusLookup] TSSL ON TSSL.[SaleStatusID] = TS.[StatusID]      INNER JOIN tbVenue TV ON TSS.VenueID = TV.VenueID      INNER JOIN [tbApplicationLookup] TAL ON TAL.ApplicationID = TS.ApplicationID      INNER JOIN [tbEvent] TEC ON TEC.[EventID] = TSS.[EventID]      LEFT OUTER JOIN [tbUserAddress] TUA2 ON TUA2.[UserID] = tth.[BrokerID]                                              AND TUA2.[AddressTypeID] = 4 AND TUA2.isdefault=1      LEFT OUTER JOIN [tbAddress] TA2 ON TUA2.[AddressID] = TA2.[AddressID]            LEFT OUTER  JOIN dbo.tbAddress A ON TV.VenueAddressID = A.AddressID      LEFT OUTER JOIN dbo.tbCityCulture TCCV ON A.CityID = TCCV.CityID                                                AND TCCV.CultureID = 1      LEFT OUTER JOIN [tbVenueCulture] TVC ON tvc.venueid = tss.venueid                                              AND tvc.cultureid = 1      LEFT OUTER JOIN [tbCurrencyMasterLookup] TCML ON TCML.currencyid = TS.CurrencyID      LEFT OUTER JOIN [tbUser] TU ON TU.[UserID] = TS.[BuyerID]      LEFT OUTER JOIN tbSalePaymentReceived SPR ON SPR.SaleID = TS.SaleID      LEFT OUTER JOIN tbPaymentReceived PR ON SPR.PaymentID = PR.PaymentID      LEFT OUTER JOIN tbPaymentMethodLookup PML ON PML.PaymentMethodID = PR.PaymentMethodID      LEFT OUTER JOIN [tbAffiliate] TAA ON TAA.affiliateID = TS.affiliateID       LEFT OUTER JOIN tbaffiliatecategory TAC ON TAA.affiliateCategoryID = TAC.CategoryID       WHERE        (@EventName IS NULL       OR TEC.[Name] LIKE @EventName)      -- AND (@EventTypeID IS NULL OR TEC.EventTypeID = @EventTypeID)      AND (TAA.AffiliateCategoryID = @AffiliateCategoryID or @AffiliateCategoryID = -1)      AND (TS.FraudStatusID IN  (select fraudstatusid from @FraudStatusIDTable))      AND (TS.StatusID IN (select StatusID from @StatusIDTable))            AND (TTH.IsLMS = @LMSOrders)    SELECT      *    FROM #t1 AS t    ORDER BY      t.RecognisedDate    DROP TABLE [#T1]    DROP TABLE #tmptbSaleHistory  END
Optimizing sales report stored procedure
sql;sql server;t sql;stored procedure