前提となるデータの準備
100万件のデータを用意します。hoge_idの10万件に10件のhistory_idを紐づけます。
CREATE TABLE hoge_history (
hoge_id BIGINT,
history_id INT,
col1 INT,
col2 INT,
rgdt DATETIME,
CONSTRAINT pk_hoge_history PRIMARY KEY(hoge_id, history_id)
);
CREATE INDEX idx_hoge_history_rgdt ON hoge_history (rgdt);
INSERT INTO hoge_history(hoge_id, history_id, col1, col2, rgdt)
SELECT
f1.id AS hoge_id,
f2.id AS history_id,
f1.id % 10 AS col1,
CASE WHEN f1.id % 3 = 0 THEN 0 ELSE f1.id END AS col2,
DATEADD(DAY, f1.id, '2022-12-27 00:00') AS rgdt
FROM(SELECT TOP(10 * 10000) ROW_NUMBER() OVER (ORDER BY o.object_id) AS id FROM sys.objects o, sys.objects o1, sys.objects o2) f1,
(SELECT TOP(10) ROW_NUMBER() OVER (ORDER BY object_id) AS id FROM sys.objects) f2;
パターン１
SET STATISTICS IO, TIME ON
SELECT
h.*
FROM hoge_history h INNER JOIN (SELECT
h.hoge_id, MAX(history_id) AS history_id
FROM hoge_history h
WHERE h.rgdt > '2022-12-28' AND h.rgdt < '2023-01-01'
GROUP BY h.hoge_id) history_last ON h.hoge_id= history_last.hoge_id AND h.history_id = history_last.history_id
SET STATISTICS IO, TIME OFF
(3 rows affected)
Table 'hoge_history'. Scan count 1, logical reads 15, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Completion time: 2023-01-12T00:55:13.9788979+09:00
パターン２
SET STATISTICS IO, TIME ON
SELECT
h.*
FROM hoge_history h
WHERE h.rgdt > '2022-12-28' AND h.rgdt < '2023-01-01'
AND NOT EXISTS
(SELECT * FROM hoge_history d WHERE d.hoge_id = h.hoge_id AND d.history_id > h.history_id)
SET STATISTICS IO, TIME OFF
(3 rows affected)
Table 'hoge_history'. Scan count 31, logical reads 223, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Completion time: 2023-01-12T00:55:31.1618957+09:00
パターン３
SET STATISTICS IO, TIME ON
SELECT
*
FROM(SELECT
h.*,
MAX(history_id) OVER (PARTITION BY h.hoge_id) AS max_history_id
FROM hoge_history h
WHERE h.rgdt > '2022-12-28' AND h.rgdt < '2023-01-01') f
WHERE f.max_history_id = f.history_id
SET STATISTICS IO, TIME OFF
(3 rows affected)
Table 'Worktable'. Scan count 3, logical reads 73, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'hoge_history'. Scan count 1, logical reads 103, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Completion time: 2023-01-12T00:54:08.4014039+09:00
結論
パターン１がいいみたいですね