複数の複合条件を検索するクエリの効率

3087 ワード
SQLServer SQLServer テキストリンク

複数の複合条件を検索する際の効率

SQLServerで複数の複合条件で検索する
他のDBであれば下記のように書けるらしいがSQLServerは対応していない

SELECT * FROM t_sample WHERE (col1, col2) IN((1, 2), (3, 4))

ではSQLServerで検索する際に最も効率のいい方法を考え、検証する

結論

SELECT * FROM t_sample WHERE (col1=1 AND col2=2) OR (col1=3 AND col2=4)

きっと疲れていたのだろう。トチ狂った検証をしていた。でもなぜかスッとこの条件が浮かばなかった。

検証環境

DBはSQLServer2019Dev
テーブルは下記SQLで作成

CREATE TABLE [dbo].[t_sample](
    [id] [bigint] NOT NULL IDENTITY(1,1),
    [col1] [int] NOT NULL,
    [col2] [int] NOT NULL,
    CONSTRAINT [PK_t_sample] PRIMARY KEY CLUSTERED ([id] ASC),
    INDEX [IX_t_sample_col1] NONCLUSTERED ([col1] ASC),
    INDEX [IX_t_sample_col2] NONCLUSTERED ([col2] ASC),
)
DECLARE @i int = 0
WHILE @i<1000000
BEGIN
INSERT t_sample (col1, col2) SELECT cast(RAND()*10 as int), cast(RAND()*10 as int)
SET @i=@i+1
END

実行速度は3回実行して最も少ない時間を採用する。差が明らかだったことと結論に気づいたのでキャッシュの削除などは行わない。
また、データの取得によるオーバーヘッドをなくすため件数を出力する

解決策

実行時間が短い順に記載しますが実際に思いついたのはこの逆順です。

ANDとORで構成する

DECLARE @t1 datetime2 = SYSDATETIME()
SELECT COUNT(*) FROM t_sample WHERE (col1=1 and col2=2) Or (col1=3 and col2=4)
SELECT DATEDIFF(MILLISECOND, @t1, SYSDATETIME())

実行時間は29ms

一時テーブルを作成する

DECLARE @t1 datetime2 = SYSDATETIME()
CREATE TABLE #tmp(col1 int NOT NULL, col2 int NOT NULL)
INSERT #tmp SELECT 1, 2
INSERT #tmp SELECT 3, 4
SELECT COUNT(*) FROM t_sample WHERE EXISTS(SELECT * FROM #tmp WHERE col1=t_sample.col1 and col2=t_sample.col2)
SELECT DATEDIFF(MILLISECOND, @t1, SYSDATETIME())

実行時間は59ms

文字列結合してIN句で検索する

DECLARE @t1 datetime2 = SYSDATETIME()
DECLARE @set1 varchar(21) = cast(1 as varchar) + ',' + cast(2 as varchar)
DECLARE @set2 varchar(21) = cast(3 as varchar) + ',' + cast(4 as varchar)
SELECT COUNT(*) FROM t_sample WHERE cast(col1 as varchar) + ',' + cast(col2 as varchar) IN(@set1, @set2)
SELECT DATEDIFF(MILLISECOND, @t1, SYSDATETIME())

実行時間は429ms

感想

SQLServerで複数カラムに対してIN句が使えないと知ってからかなり間違った方向に進んでしまった。
一応それぞれのメリットを考える
ANDとOR: 速い！単純！
一時テーブル: クエリを分割できるので条件が多すぎて1クエリの上限の65536文字を超えることを回避できる
文字列結合： 憎しみを思い出させてくれる