0

I have two tables with many to many relationship, which I join on `userId and then sum/count values so I would get the summary of them. This is how it looks:

            select COUNT(DISTINCT [s].[UserId]) AS NumberOfCustomers, 
            sum(s.[ProbabilityAlive]) as ProbabilityAlive,
            avg([s].[ExpectedMonetaryValue]) as ExpectedMonetaryValue, sum(ExpectedTransactions) as ExpectedTransactions,
            sum(s.[CustomerLifetimeValue]) as CustomerLifetimeValue, sum(s.[ResidualLifeTimeValue]) as ResidualLifetimeValue, 
            sum(s.[Transactions]) as Transactions, sum(s.[TotalSpend]) as TotalSpend,
            sum(s.[DeltaProbabilityAlive]) as DeltaProbabilityAlive, sum(s.[ProbabilityActive]) as ProbabilityActive,
            sum(s.[DeltaProbabilityActive]) as DeltaProbabilityActive,
            sum(s.[DeltaEstimatedNumberOfTransactionsShort]) as DeltaEstimatedNumberOfTransactionsShort, sum(s.[EstimatedNumberOfTransactionsLong]) as EstimatedNumberOfTransactionsLong,
            sum(s.[DeltaEstimatedNumberOfTransactionsLong]) as DeltaEstimatedNumberOfTransactionsLong, sum(s.[EstimatedNumberOfTransactionsLongLeft]) as EstimatedNumberOfTransactionsLongLeft,
            sum(s.[ResidualLifeTimeValueLeft]) as ResidualLifeTimeValueLeft, sum(s.[CustomerLifetimeValueLeft]) as CustomerLifetimeValueLeft,
            SUM(a.PeakClv) as PeakClv
            FROM (
            SELECT DISTINCT([Clv].[UserId]), sum([dbo].[Clv].[ProbabilityAlive]) as ProbabilityAlive,
            avg([Clv].[ExpectedMonetaryValue]) as ExpectedMonetaryValue, sum([dbo].[Clv].[EstimatedNumberOfTransactionsShort]) as ExpectedTransactions,
            sum([Clv].[CustomerLifetimeValue]) as CustomerLifetimeValue, sum([dbo].[Clv].[ResidualLifeTimeValue]) as ResidualLifetimeValue, 
            sum([dbo].[Clv].[NumberOfTransactions]) as Transactions, sum([dbo].[Clv].[TotalSpend]) as TotalSpend,
            sum([dbo].[Clv].[DeltaProbabilityAlive]) as DeltaProbabilityAlive, sum([dbo].[Clv].[ProbabilityActive]) as ProbabilityActive,
            sum([dbo].[Clv].[DeltaProbabilityActive]) as DeltaProbabilityActive,
            sum([dbo].[Clv].[DeltaEstimatedNumberOfTransactionsShort]) as DeltaEstimatedNumberOfTransactionsShort, sum([dbo].[Clv].[EstimatedNumberOfTransactionsLong]) as EstimatedNumberOfTransactionsLong,
            sum([dbo].[Clv].[DeltaEstimatedNumberOfTransactionsLong]) as DeltaEstimatedNumberOfTransactionsLong, sum([dbo].[Clv].[EstimatedNumberOfTransactionsLongLeft]) as EstimatedNumberOfTransactionsLongLeft,
            sum([dbo].[Clv].[ResidualLifeTimeValueLeft]) as ResidualLifeTimeValueLeft, sum([dbo].[Clv].[CustomerLifetimeValueLeft]) as CustomerLifetimeValueLeft  
            FROM [Clv]  WHERE Clv.[ClientId] = '2' AND   Clv.[CalculationDate] = '02/28/2021' AND  Clv.[Product] = 'Total'  GROUP BY UserId ) s
            LEFT JOIN (
                SELECT  DISTINCT(UserId), SUM([PeakClv]) as PeakClv
                FROM [dbo].[AdditionalClvData]
                WHERE ClientId = '2' AND CalculationDate = '02/28/2021'
                GROUP BY [UserId]
            ) a ON a.[UserId] = s.[UserId]

I take almost all values from one table, and one from the other and use pre-aggregating in subqueries. Select from single table takes couple of seconds, however this query takes around 20seconds. Am I doing joining/aggregating incorrectly? Is there a way to optimise so it would work faster?

2
  • @Ronaldo sql server
    – user122222
    Mar 9 at 17:04
  • As a general rule of thumb, to connect two tables with many-to-many relationship, you should use Linking Table in which you have to use two foreign keys and one primary key. When you join these two table by inner join you break the rules of database design.
    – Commander
    Mar 9 at 18:24
3

Firstly, you can remove the DISTINCT operator from each subquery - you're grouping by UserId and aggregating all other columns, so DISTINCT isn't doing anything:- You will always get a single row per distinct UserId value because of the grouping.

Secondly, because of this 'single row per UserId' behaviour courtesy of the grouping, you should actually use an INNER JOIN. You will always return a single row per UserId in both subqueries, so you've effectively turned your many-to-many into a one-to-one by grouping on the key column used to link the two tables.

Thirdly, you can potentially remove the subqueries altogether. Test the results and performance, but the below query should return the same results and should be quicker:

SELECT
    s.[UserId]                                       AS UserId                                 ,
    SUM(s.[ProbabilityAlive])                        AS ProbabilityAlive                       ,
    AVG(s.[ExpectedMonetaryValue])                   AS ExpectedMonetaryValue                  ,
    SUM(s.[EstimatedNumberOfTransactionsShort])      AS ExpectedTransactions                   ,
    SUM(s.[CustomerLifetimeValue])                   AS CustomerLifetimeValue                  ,
    SUM(s.[ResidualLifeTimeValue])                   AS ResidualLifetimeValue                  ,
    SUM(s.[NumberOfTransactions])                    AS Transactions                           ,
    SUM(s.[TotalSpend])                              AS TotalSpend                             ,
    SUM(s.[DeltaProbabilityAlive])                   AS DeltaProbabilityAlive                  ,
    SUM(s.[ProbabilityActive])                       AS ProbabilityActive                      ,
    SUM(s.[DeltaProbabilityActive])                  AS DeltaProbabilityActive                 ,
    SUM(s.[DeltaEstimatedNumberOfTransactionsShort]) AS DeltaEstimatedNumberOfTransactionsShort,
    SUM(s.[EstimatedNumberOfTransactionsLong])       AS EstimatedNumberOfTransactionsLong      ,
    SUM(s.[DeltaEstimatedNumberOfTransactionsLong])  AS DeltaEstimatedNumberOfTransactionsLong ,
    SUM(s.[EstimatedNumberOfTransactionsLongLeft])   AS EstimatedNumberOfTransactionsLongLeft  ,
    SUM(s.[ResidualLifeTimeValueLeft])               AS ResidualLifeTimeValueLeft              ,
    SUM(s.[CustomerLifetimeValueLeft])               AS CustomerLifetimeValueLeft              ,
    SUM([a.PeakClv])                                 AS PeakClv
FROM [Clv] s
LEFT JOIN [AdditionalClvData] a ON a.[UserId] = s.[UserId]
WHERE 
    s.[ClientId]            = '2'
    AND s.[CalculationDate] = '02/28/2021'
    AND s.[Product]         = 'Total'
    AND a.[ClientId]        = '2'
    AND a.[CalculationDate] = '02/28/2021'
GROUP BY
    s.[UserId]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.