QA@IT

レコードを参照するレコードの有無だけを高速に取得したい

16099 PV

SQL Server 2005を使用しています。
以下のようなテーブルがあるとします。
テーブル A
id (int型)
テーブル B
id (int型)
a_id (int型)

この時、Aのレコードごとに、テーブルBにあるa_idが、idと一致するレコードの有無を取得するには、

select
 A.id,
 count(B.id) as B_count
from
 A
left outer join
 B
on
 B.a_id = A.id
group by
 A.id

を実行して、B_countが1以上であれば、該当するレコードがあると判定できます。
しかし、欲しい情報は、0か、1以上かのいずれかであるため、countは欲しい情報に対して過剰です。
そこで、以下のように記述したところ、テーブルBにレコードが50万ほどある状態で、実行時間が4倍ほど高速になりました。

select
 A.id,
 0 as B_exists
from
 A
where
 not exists (
  select
   *
  from
   B
  where
   B.a_id = A.id
 )
union
select
 A.id,
 1 as B_exists
from
 A
where
 exists (
  select
   *
  from
   B
  where
   B.a_id = A.id
 )

外部キー経由で参照するテーブルが1個の場合は、これで良いのですが、参照するテーブルの個数が1つ増えるごとに、unionで結合するselect文の個数が2倍に増加します。
この文と同等の結果を、同等以下の時間で得られる文を、SQL標準に則ったものか、SQL Server固有の方法か、いずれかの方法で、より簡潔に記述する方法はないでしょうか?

追記
実際のデータ量に近づけて、各SQL文の実行計画を取得しました。データ量は、Aが80レコード、Bが約50万レコードです。また、実際には、各テーブルに関係ないカラムが存在しますが、実行計画を見たところ、ほとんど影響はありませんでした。
B.a_idには、インデックスを追加してあります。
速い順に並べた結果は、以下のようになりました。

Oakbow7         0.0267094
yasuyuki        0.0301459
jiji            0.0467235
flied_onion     0.490731
alice-asahina   0.490798

共通テーブル式を使用する方法がもっとも速いようです。また、distinctは遅く、countを取得するよりも時間がかかるようです。実行計画を見ても、90%以上の時間がdistinctするために費やされていました。
性能では、Oakbow7さんの、簡潔さと性能のバランスでは、jijiさんの方法が良いようです。
Oakbow7さんの記述でも、組み合わせによってパターンが爆発することはないため、Oakbow7さんの方法をacceptします。

  • おー、実行計画込みの結果報告はとてもありがたいです。distinctは全件走査して重複を間引くので、あまり速くないと思います。 -
  • distinct自体は遅いですが、Aが増えてくると優位性が出てきます。両方100万件越えると採用したSQLより速くなる環境はあります(ディスクの速度など環境依存なのでこんなもの言いに…)。
    後はテーブル名が一度しか出て来ないようにdistinctにしたのもあります。
    なんにせよ Aがその件数であれば採用されたSQLで間違いないですね。
    -

回答

Aテーブルのレコード数が不明ですが、Bテーブルより格段に少ないんですよね?
existsは該当レコードが見つかった時点でスキャンを打ち切るのでcountより高速ですが、not existsは無いことを確かめるために全スキャンするはずなので、あまり速くありません。
なので、スキャン対象をAテーブルにすると多少マシになりはしないでしょうか。

あんまり変わらない気もしますが、CTE使うと結果セットを一時保持してくれるので、Bテーブルの全インデックススキャンより速くなるかも。。
jijiさんのselect文をお借りするとこんな感じになるのかな。
シンプルじゃなくなってますが。

WITH cte (id)
AS
(
  SELECT A.id
  FROM A
  WHERE
  EXISTS (
    SELECT 1
    FROM B
    WHERE B.a_id = A.id
  )
)
SELECT
A.id,
CASE WHEN EXISTS(SELECT 1 FROM cte WHERE cte.id = A.id) THEN 1 ELSE 0
END AS B_exists
FROM A

あと気を付ける点としては、Bテーブルのa_id列にインデックスが欲しいことと、EXISTS内の相関サブクエリのSELECTは定数にした方が良いくらいでしょうか。
後者はオプティマイザがいいように解釈してくれそうな気もしますけれど。

編集 履歴 (0)

シンプルに~をもうちょっとシンプルにしてみた。

SELECT
    A.id,
    ISNULL((SELECT DISTINCT 1 from B where B.a_id = A.id),0) AS B_exists -- MSSQL
    -- NVL((SELECT DISTINCT 1 from B where B.a_id = A.id),0) AS B_exists -- Oracle
FROM A
編集 履歴 (3)

シンプルに、こんなのは如何でしょう?

SELECT
A.id,
CASE WHEN EXISTS(SELECT * from B where B.a_id = A.id) THEN 1 ELSE 0
END AS B_exists
FROM A

編集 履歴 (0)

速度はわかりませんがこういうのではどうでしょう。

select 
  A.id,
  case 
     when B.a_id is null then 0 ELSE 1
  end B_exists
  from A
    left outer join 
      (select distinct a_id from B) B
    on A.id = B.a_id

Elseに修正しました。

編集 履歴 (2)
  • B.a_id is null => true : 1 / false : 0 がかえりますよん。
    つまり is not null ならば
    -
ウォッチ

この質問への回答やコメントをメールでお知らせします。