Optimize SQL Server query for speed

Question

How can I optimize my SQL Server query ? Here is the code that I want to optimize

CREATE TABLE #Temp
(
    TransactionId int PRIMARY KEY,
    TransactionStepId int
) 

INSERT INTO #Temp(TransactionId, TransactionStepId)
    SELECT 
        TransactionId, MAX(TransactionStepId) TransactionStepId
    FROM 
        [WarehouseMgmt].[FactPaymentTrans]  FPT
    JOIN 
        WarehouseMgmt.DimTimeZone DTZ on FPT.[TimeId] = DTZ.TimeUTCId   
    WHERE 
        FactType = 'SOURCE' 
        AND (DTZ.TimeId BETWEEN @DimStartDate AND @DimEndDate)
    GROUP BY 
        TransactionId 

IF(UPPER(@ReportBy) = 'TRANSACTION')
BEGIN
SET @sql = '
    INSERT INTO #Results (      
    [PaymentTypeId],
    [TransactionDate],
    [PaymentMethodId],
    [3DSecureId],
    [ProductId],
    [ProductTypeId],
    [TransactionStatusId],
    [Amount],
    [Currency],
    [PlayerId],
    [PlayerSourceOrigId],
    [Username],
    [FirstName],
    [LastName],
    [BrandId],
    [VIPLevelId],
    [MarketingChannelId],
    [MarketingSourceId],
    [CommentId],
    [CommentRefId],
    [AdminName],
    [OriginalTransactionId],
    [TransactionId],
    [RelatedTransactionId],
    [ProviderTransactionOrigId]
    )
    SELECT
        DTST.[Id],
        FPT.[StartTime],
        FPT.[PaymentMethodId],
        FPT.[3DSecureId],
        FPT.[ProductId],
        DPT.[Id],
        FPT.[TransactionStatusId],
        FPT.[Amount],
        FPT.CurrencyId,
        FPT.[PlayerId],
        DPL.[SourceOrigId],
        DPL.[Username],
        DPL.[FirstName],
        DPL.[LastName],
        DPL.[BrandId],
        DPL.[VIPLevelId],
        DPL.[MarketingChannelId],
        DPL.[MarketingSourceId],
        FPT.[PaymentReasonTextId],
        FPT_Ref.[PaymentReasonTextId],
        FPT.CreatedByAdminId,
        FPT.[OriginalTransactionId],
        FPT.[TransactionId],
        FPT_Ref.[OriginalTransactionId],
        FPT.[ProviderTransactionOrigId]
  FROM WarehouseMgmt.FactPaymentTrans AS FPT        
    JOIN #Temp T ON FPT.TransactionId = T.TransactionId AND FPT.TransactionStepId = T.TransactionStepId
    JOIN WarehouseMgmt.DimTransactionStepType AS DTST ON FPT.[TransactionStepTypeId] = DTST.[Id] 
    JOIN WarehouseMgmt.DimPlayer AS DPL ON FPT.PlayerId = DPL.Id
    JOIN WarehouseMgmt.DimProduct AS DP ON DP.Id = FPT.ProductId
    JOIN WarehouseMgmt.DimProductType AS DPT ON DPT.Id = DP.ProductTypeId
    --JOIN WarehouseMgmt.DimTimeZone DTZ on FPT.[TimeId] = DTZ.TimeUTCId
    JOIN [WarehouseMgmt].[DimLoyaltyProgramLevel] DLPL ON DLPL.[Id]=DPL.VipLevelId      
    LEFT JOIN 
  (
              SELECT FPT_Ref_1.TransactionId,FPT_Ref_1.FactType,FPT_Ref_1.OriginalTransactionId,FPT_Ref_1.[PaymentReasonTextId] 
              FROM WarehouseMgmt.FactPaymentTrans AS FPT_Ref_1                         
              JOIN #Temp T ON T.TransactionId = FPT_Ref_1.OriginalTransactionId AND T.TransactionStepId = FPT_Ref_1.TransactionStepId
   ) AS FPT_Ref ON FPT_Ref.OriginalTransactionId = FPT.TransactionId AND FPT_Ref.FactType = ''SOURCE'' 
  WHERE (FPT.FactType = ''SOURCE'')  ' + @sqlFilters

I tried to put this

            SELECT TransactionId,MAX(TransactionStepId) TransactionStepId
            FROM [WarehouseMgmt].[FactPaymentTrans] 
            WHERE FactType = ''SOURCE'' 
            GROUP BY TransactionId 

in a temp table, but this is even worst that without temp table. I want to select latest TransactionId(thats made by MAX(TransactionStepId) and also to select last TransactionId in the left JOIN

My execution plan is:

Answer 1

There are multiple factors which can improve query performance and one of them which can be done, without necessarily knowing details about indexes, database schema, data distribution etc. is the order of the JOINs in the query.

I have refactored your query and I think you should get the same result as before, but with an improved execution time:

SELECT DTST.[Id]
    ,FPT.[StartTime]
    ,FPT.[PaymentMethodId]
    ,FPT.[3DSecureId]
    ,FPT.[ProductId]
    ,DPT.[Id]
    ,FPT.[TransactionStatusId]
    ,FPT.[Amount]
    ,FPT.CurrencyId
    ,FPT.[PlayerId]
    ,DPL.[SourceOrigId]
    ,DPL.[Username]
    ,DPL.[FirstName]
    ,DPL.[LastName]
    ,DPL.[BrandId]
    ,DPL.[VIPLevelId]
    ,DPL.[MarketingChannelId]
    ,DPL.[MarketingSourceId]
    ,FPT.[PaymentReasonTextId]
    ,FPT_Ref.[PaymentReasonTextId]
    ,FPT.CreatedByAdminId
    ,FPT.[OriginalTransactionId]
    ,FPT.[TransactionId]
    ,FPT_Ref.[OriginalTransactionId]
    ,FPT.[ProviderTransactionOrigId]
FROM WarehouseMgmt.FactPaymentTrans AS FPT
INNER JOIN WarehouseMgmt.DimPlayer AS DPL
    ON FPT.PlayerId = DPL.Id
INNER JOIN WarehouseMgmt.DimProduct AS DP
    ON DP.Id = FPT.ProductId
INNER JOIN WarehouseMgmt.DimProductType AS DPT
    ON DPT.Id = DP.ProductTypeId
INNER JOIN WarehouseMgmt.DimTransactionStepType AS DTST
    ON FPT.[TransactionStepTypeId] = DTST.[Id]    
WHERE (FPT.FactType = '' SOURCE '')
    AND EXISTS (SELECT 1
                FROM [WarehouseMgmt].[FactPaymentTrans]
                WHERE FactType = '' SOURCE ''
                    AND FPT.TransactionId = TransactionId
                    AND FPT.TransactionStepId = TransactionStepId)
    AND EXISTS (SELECT 1
                FROM [WarehouseMgmt].[DimLoyaltyProgramLevel] DLPL
                WHERE DLPL.[Id] = DPL.VipLevelId)
    AND EXISTS (SELECT 1
                FROM WarehouseMgmt.DimTimeZone DTZ
                WHERE FPT.[TimeId] = DTZ.TimeUTCId
                    AND DTZ.TimeId BETWEEN @DimStartDate AND @DimEndDate)

Answer 2

Have you considered using ROW_NUMBER instead of joining your table twice? Please try this:

;WITH FactPaymentTrans_Last
AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY TransactionID ORDER BY TransactionStepID DESC) AS RN
    FROM WarehouseMgmt.FactPaymentTrans
    )
SELECT DTST.[Id]
    , FPT.[StartTime]
    , FPT.[PaymentMethodId]
    , FPT.[3DSecureId]
    , FPT.[ProductId]
    , DPT.[Id]
    , FPT.[TransactionStatusId]
    , FPT.[Amount]
    , FPT.CurrencyId
    , FPT.[PlayerId]
    , DPL.[SourceOrigId]
    , DPL.[Username]
    , DPL.[FirstName]
    , DPL.[LastName]
    , DPL.[BrandId]
    , DPL.[VIPLevelId]
    , DPL.[MarketingChannelId]
    , DPL.[MarketingSourceId]
    , FPT.[PaymentReasonTextId]
    , FPT_Ref.[PaymentReasonTextId]
    , FPT.CreatedByAdminId
    , FPT.[OriginalTransactionId]
    , FPT.[TransactionId]
    , FPT_Ref.[OriginalTransactionId]
    , FPT.[ProviderTransactionOrigId]
FROM FactPaymentTrans_Last AS FPT
INNER JOIN WarehouseMgmt.DimTransactionStepType AS DTST
    ON FPT.[TransactionStepTypeId] = DTST.[Id]
INNER JOIN WarehouseMgmt.DimPlayer AS DPL
    ON FPT.PlayerId = DPL.Id
INNER JOIN WarehouseMgmt.DimProduct AS DP
    ON DP.Id = FPT.ProductId
WHERE FPT.RN = 1;

It's just a snippet to give you idea how you can use this to get latest TransactionIds based on its TransactionStepId.

If it's not enough - please post these:

1. Your table structure
2. Indices on them
3. Your execution plan

It will help to give you suggestions.