Performant Loop or Alternatives

Question

My current data set looks like following

[Table 1]

Key	Group	Amount	Sequence
1	1	50	1
2	1	60	2
3	1	70	3
4	1	90	4
5	2	120	1

I have a second table that records a "Rolling rank" of each of these which looks like this:

[Table 2]

Key	KeySK	Rank	TotalAmount	Amount
1	1	1	50	50
2	2	1	60	60
3	2	2	110	50
4	3	1	70	70
5	3	2	130	60
6	3	3	180	50

I perform this by looking for all records in Table 1 where they do not have a subsequent record in Table 2

SELECT
  Key
  ,Group
FROM
  [Table 1] [T1]
  LEFT JOIN [Table 2] [T2] ON [T1].Key = [T2].KeySK 
WHERE
  [T2].Key IS NULL

Then, using SSIS ForEach Loop, I run the following stored procedure:

    SELECT
        @InstalmentSequence = [Sequence]
    FROM
        [Table 1] [T1]
    WHERE
        [T1].Key = @Key

    INSERT INTO [Table 2]
    SELECT

        [T1].[Key]                                              AS 'KeySK'
        ,RANK() 
            OVER (ORDER BY [T1].[Sequence] DESC)                AS 'Rank'
        ,SUM(ISNULL([T1].Amount,0.00))  
            OVER (ORDER BY [T1].[Sequence] DESC)                AS 'TotalAmount'

        ,ISNULL([T1].Amount, 0.00)                              AS 'Amount'
    FROM
        [Table 1] [T1]
    WHERE
        [T1].AccountSK = @Group
        AND [T1].[Sequence] <= @Sequence

All of this works and produces the correct outcome, but the performance is very poor. I am wondering if these is a better solution to this that I am not thinking of.

Answer 1

Seems like you could just do the whole thing in one big query, by using a partitioning clause.

Note also the use of ROWS UNBOUNDED PRECEDING which is more efficient than the default RANGE UNBOUNDED PRECEDING

INSERT INTO [Table 2]
  (KeySK, Rank, TotalAmount, Amount)
SELECT
  T1.[Key],
  T1.Rank,
  T1.TotalAmount,
  T1.Amount
FROM (
    SELECT
      T1.[Key],
      RANK() OVER (PARTITION BY T1.[Group] ORDER BY T1.Sequence DESC) AS Rank,
      SUM(ISNULL(T1.Amount, 0.00)) OVER (PARTITION BY T1.[Group]
             ORDER BY T1.Sequence DESC ROWS UNBOUNDED PRECEDING) AS TotalAmount,
      ISNULL(T1.Amount, 0.00) AS Amount
    FROM [Table 1] T1
) T1
WHERE NOT EXISTS (SELECT 1
    FROM [Table 2] T2
    WHERE T1.[Key] = T2.KeySK
);

If this results in a lot of rows being calculated but never getting inserted, you might be able to limit it by Group like this

INSERT INTO [Table 2]
  (KeySK, Rank, TotalAmount, Amount)
SELECT
  T1.[Key],
  T1.Rank,
  T1.TotalAmount,
  T1.Amount
FROM (
    SELECT DISTINCT
      T1.[Group]
    FROM [Table 1] T1
    WHERE NOT EXISTS (SELECT 1
        FROM [Table 2] T2
        WHERE ToFind.[Key] = T2.KeySK
    )
) ToFind
CROSS APPLY (
    SELECT
      T1.[Key],
      RANK() OVER (PARTITION BY T1.[Group] ORDER BY T1.Sequence DESC) AS Rank,
      SUM(ISNULL(T1.Amount, 0.00)) OVER (PARTITION BY T1.[Group]
             ORDER BY T1.Sequence DESC ROWS UNBOUNDED PRECEDING) AS TotalAmount,
      ISNULL(T1.Amount, 0.00) AS Amount
    FROM [Table 1] T1
    WHERE T1.[Group] = ToFind.[Group]
) T1;

Do yourself a favour and try not to use column names that need quoting with []. Do not quote columns with '' only [].