1

I am working with a table (which I control) that is being populated by another application (which I don't control), each time a record of a particular type in an external system is created or updated. Below is a heavily simplified illustration of this table, which I am calling "AllRecordVersions".

RecordTimestamp RecordID RecordState DataField1 DataField2 DataField3
2023-11-29 09:08:00 A0000001 Processed ABC123 True 102.11
2023-11-29 09:13:00 A0000002 Processed DEF789 False 96.48
2023-11-29 09:20:00 A0000001 Unprocessed ABC123 False 105.59
2023-11-29 09:22:00 A0000001 Unprocessed ABC124 False 106.02
2023-11-29 09:37:00 A0000002 Unprocessed DEF789 False 99.73

The value of "RecordState" refers to whether or not my own application has picked up the record yet. I am able to update this column to track my application's activity.

I am using a query in the format below (which I have simplified/anonymised) to pick out the latest version of each record in the table, in such a way that I can compare the values of each of its data fields to the values that they held in the previous version of the same record that I processed.

SELECT

    -- Here I can compare the latest version of each record
    -- with the last version that I had processed, e.g. with
    -- "CASE WHEN [LatestUnprocessedRecordVersion].[DataField1] <> [LatestProcessedRecordVersion].[DataField1] ...",
    -- so that I can check if and how certain fields have been changed,
    -- and use that to decide what updates to push into some other external system

FROM (
    [AllRecordVersions] AS [LatestUnprocessedRecordVersion]
    JOIN (
        SELECT
          MAX([RecordTimestamp]) AS [RecordTimestamp_Max],
          [RecordID]
        FROM [AllRecordVersions]
        WHERE [RecordState] = 'Unprocessed'
        GROUP BY [RecordID]
    ) AS [LatestUnprocessedRecordVersion_Timestamp]
          ON [LatestUnprocessedRecordVersion].[RecordTimestamp] = [LatestUnprocessedRecordVersion_Timestamp].[RecordTimestamp_Max]
         AND [LatestUnprocessedRecordVersion].[RecordID] = [LatestUnprocessedRecordVersion_Timestamp].[RecordID]
)
JOIN (
    [AllRecordVersions] AS [LatestProcessedRecordVersion]
    JOIN (
        SELECT
          MAX([RecordTimestamp]) AS [RecordTimestamp_Max],
          [RecordID]
        FROM [AllRecordVersions]
        WHERE [RecordState] = 'Processed'
        GROUP BY [RecordID]
    ) AS [LatestProcessedRecordVersion_Timestamp]
          ON [LatestProcessedRecordVersion].[RecordTimestamp] = [LatestProcessedRecordVersion_Timestamp].[RecordTimestamp_Max]
         AND [LatestProcessedRecordVersion].[RecordID] = [LatestProcessedRecordVersion_Timestamp].[RecordID]
)
    ON [LatestUnprocessedRecordVersion].[RecordID] = [LatestProcessedRecordVersion].[RecordID]

My question is: What indexes should I put on the table "AllRecordVersions" to make the above query run efficiently? The database is Microsoft SQL Server.

To also help with efficiency, the table "AllRecordVersions" will be periodically pruned by removing:

  1. All but the latest processed version of each record from the table (since these will never be needed for comparison)
  2. All but the latest unprocessed version of each record from the table (since these will never be processed)
1

1 Answer 1

0

I'm not convinced that this query is the one you should be running in the first place.

It's quite obviously generated by some tool like Access, and could do with a good dose of window functions.

SELECT

    -- Here I can compare the latest version of each record
    -- with the last version that I had processed, e.g. with
    -- "CASE WHEN LatestUnprocessedRecordVersion.DataField1 <> LatestProcessedRecordVersion.DataField1 ...",
    -- so that I can check if and how certain fields have been changed,
    -- and use that to decide what updates to push into some other external system

FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY arv.RecordID ORDER BY arv.RecordTimestamp DESC) AS rn
    FROM AllRecordVersions AS arv
    WHERE arv.RecordState = 'Unprocessed'
) LatestUnprocessedRecordVersion
JOIN (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY arv.RecordID ORDER BY arv.RecordTimestamp DESC) AS rn
    FROM AllRecordVersions AS arv
    WHERE arv.RecordState = 'Processed'
) LatestProcessedRecordVersion
    ON LatestUnprocessedRecordVersion.RecordID = LatestProcessedRecordVersion.RecordID
WHERE LatestUnprocessedRecordVersion.rn = 1
AND LatestProcessedRecordVersion.rn = 1;

Note that the semantics are slightly different, as this will not include ties. Use DENSE_RANK if you want ties.

For this query to run efficiently, you want the following index:

AllRecordVersions (RecordState, RecordID, RecordTimestamp DESC) INCLUDE (DataField1, DataField2, DataField3)

db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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