「項目別の TOP 3 を求めるクエリー(一時テーブルとカーソル版) 」の改良版を作成しました。
みなさんのアドバイスを下に、次の点を変更しています。
■更新履歴
- カーソルを SELECT DISTINCT Name FROM T とするかわりに、Name 用一時テーブル #N を事前に作成した (2004/5/17)
- 結果をテーブル変数 @t に保存する代わりに、一時テーブル #T にした(2004/5/17)
- テーブル #R に ID のみを入れるようにし、出力時に JOIN するようにした (2004/5/19)
- テーブル #R の ID を NOT NULL にした (2004/5/19)
- ループを BEGIN TRANSACTION/COMMIT TRANSACTION でまとめた (2004/5/19)
- カーソルを STATIC にした (2004/5/19)
- 時間の計測開始を一番最初にした (2004/5/19)
■結果
約 2 倍早くなりました。特に、カーソル用に #N を用意したことが効いています。
SELECT DISCTINCT Name としている場合、FETCH NEXT のたびに、テーブルスキャンが発生します。
Name 用一時テーブルにした場合、テーブルシークになり、Name のレコード数が多くなると、特に影響します。
なお、テストデータは増やす必要があります。Name 100 x Value 100 だと、計測誤差のほうが大きい。
これに相当するセットベースの改良案を求む !
■クエリー
SET NOCOUNT ON
-- 時間計測開始
DECLARE @start datetime
SET @start = GETDATE()
-- 一時テーブル作成
CREATE TABLE #R (ID int)
CREATE TABLE #N (Name nvarchar(10) PRIMARY KEY)
INSERT INTO #N
SELECT Name FROM T GROUP BY Name
-- 変数宣言
DECLARE @t TABLE
(ID int, Name nvarchar(10), Value int)
DECLARE Names CURSOR
LOCAL STATIC
FOR
SELECT Name FROM #N
DECLARE @Name nvarchar(10)
-- カーソルオープン
OPEN Names
FETCH NEXT FROM Names INTO @Name
-- ループ
BEGIN TRANSACTION
WHILE @@FETCH_STATUS = 0
BEGIN
-- Name 単位に TOP3 を @t へインサート
INSERT INTO #R
SELECT TOP 3 ID
FROM T
WHERE Name = @Name
ORDER BY Value
FETCH NEXT FROM Names INTO @Name
END
COMMIT TRANSACTION
-- 終了
CLOSE Names
DEALLOCATE Names
-- 結果表示
SELECT T.Name, T.Value, T.ID FROM #R
INNER JOIN T
ON #R.ID = T.ID
ORDER BY T.Name, T.Value
DROP TABLE #N
DROP TABLE #R
-- 時間表示
SELECT 'TIME(ms)' = DATEDIFF(ms, @start, GETDATE())