履歴の最新の値を取り出す方法を比較してみる

# 前提となるデータの準備

100万件のデータを用意します。hoge_idの10万件に10件のhistory_idを紐づけます。

```sql
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;
```

# パターン１

```sql
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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/16609/49810baa-75d9-fe09-e36d-0ebf69ab41d9.png)

# パターン２

```sql
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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/16609/8cf23525-6c45-bac9-126b-28b237346899.png)

# パターン３

```sql
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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/16609/0280ede6-ba00-9139-476c-7c6be78c5237.png)

# 結論

パターン１がいいみたいですね