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:

Can you share an execution plan? Details about indexes on the tables? Also, there is a small apostrophe at the end of the query. Does that have any purpose there (maybe some missing code?) or is it a mistake? — Radu Gheorghiu
– Radu Gheorghiu, Commented Oct 16, 2015 at 7:49
Can you try my query and see if there are any improvements in execution time? — Radu Gheorghiu
– Radu Gheorghiu, Commented Oct 16, 2015 at 8:41

Evaldas Buinauskas · Accepted Answer · 2015-10-16 07:52:50Z

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:

Your table structure
Indices on them
Your execution plan

It will help to give you suggestions.

Radu Gheorghiu · Accepted Answer · 2015-10-16 08:52:15Z

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)

edited Oct 16, 2015 at 8:52

answered Oct 16, 2015 at 8:06

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

5 Comments

user2171512 Over a year ago

First you don't have FPT_Ref table, second cannot filter the time in and then make join on that table.It returns error. Please see my code and make changes to be the same

Radu Gheorghiu Over a year ago

@user2171512 The idea is that it's not supposed to be the same (some queries are redundant). I have made corrections to the query so that the filter works with the "time in". Please try the new query.

user2171512 Over a year ago

I have still errors with FPT_Ref.[PaymentReasonTextId] and FPT_Ref.[OriginalTransactionId] . That was my reference table(joined table). How i can i fix this to be the same like before?

Radu Gheorghiu Over a year ago

@user2171512 Ok, I see now what I missed.

user2171512 Over a year ago

did you have newer solution about the optimization ?

Collectives™ on Stack Overflow

Optimize SQL Server query for speed

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related