このページは、以下の英語ページの抄訳です。最新の情報については、
英語ページを参照してください。
この記事のオリジナルは、Glenn Paulley がsybase.com に2008年5月に掲載したものです。Glenn はこの中でデータ量が大量の場合には、セットオペレーションがパフォーマンスにたいへん効果的であると語っています。
Hibernate object persistance library が提案するアドホックなリレーショナルマッピングが起こす問題の1つに、サブクエリーを含むクエリーの生成 - 時に非常に大量 -があります。サブクエリーは、最適化するのが非常に難しく、なぜならばそのコストと選択性を見積もることが依然としてたいへんチャレンジングな研究課題であるからです。
SQL Anywhere には、サブクエリーに対して、たいへん洗練されたクエリーリライトオプティマイゼーションの機能が備わっています。しかしながら、SQL Anywhere を含む多くの商用製品におけるサブクエリーの最適化と実行には、依然として問題があります。
今週、私はあるお客様のアプリケーションにおける問題特定を行いました。このブログ記事において伝えたいのは、サブクエリー実行のインパクトは、アプリケーションのスケーラビリティに影響するかもしれないという点において明確だということです。
これまで様々なアプリケーションの複雑なクエリーを見てきた中で、それらに共通して見られるのは、サブセレクトまたは複雑なクエリー内の abstruct の測定にユーザー定義関数を活用する傾向があることです。今日分析した複雑なクエリー - 手で書かれたもの - は、複雑なビューの 4 方向の join で、最初のビュー自体が複雑なビューであり、サブクエリーを join にコンバートする最適化をリライトした後でさえ、サブクエリーが48含まれていました。全てではありませんが、サブクエリーのほとんどは、下の形でした。
- SELECT <select list from T>,
- (SELECT R.X
- FROM R
- WHERE R.PK = T.X)
- FROM T
このサブセレクトは、単一のローを確実に返すようになっています。なぜならば、サブセレクトの WHERE 句は、テーブルR のプライマリーキーカラムをカバーするからです。そして、もしT.X がNULL の場合、このサブセレクトは NULL 値を返します。これらのプロパティは、上記の nested query が outer join クエリーに相当することを意味しています。
- SELECT <select list from T>, R.X
- FROM T LEFT OUTER JOIN R ON (T.X = R.PK)
構成間のパフォーマンスの違いを説明するために、実際のお客様のデータベースを使用して、上の例と似ている構成のクエリーを実行してみました。
私のテストでは、テーブル T は、ローが 635K 行で、テーブル R は、11000万行のローから成るシングルベースのテーブルのビューでした。上の例と少しだけ異なるのは、テーブル R は4 カラムで成るキーがあり、それゆえにサブセレクトと left outer join の ON 条件の両方の検索条件に、4つの等号条件（equality predicate）が含まれていました。
クエリーの構成が間違いなくそのままであるように、一方でクライアント・サーバー伝送コストをなくすように、上のクエリーをderived テーブルに encapsulateし、それぞれのケースで aggregate 関数を使用して最終結果セットを単一のローに制限しました。FETCHTST ユーティリティーを使用して実行時間を比較しても良かったのですが、DBISQL からの実際の統計とグラフィカルなプランを使用して、それぞれの実行に関する詳細統計をとらえました。
結果は、
- 1つのサブクエリーとしては、リクエストは15.29 秒で完了しました。サブクエリーのメモ化はこのリクエストではpay off しませんでした。なぜならば、T からの相関値は（ほとんど）ユニークで、その結果として前にメモ化した結果は、その後に続く呼び出しには使用できませんでした。
- 1つの outer join としては、単一の CPU (スレッド) で、クエリーは 7.92 秒で実行できました。理由は： left outer join は、nested-iteration セマンティクスを避けるからです。クエリー内並行処理が有効な場合（デフォルト）、クエリーの実行時間は、parallel hash outer join を使用した結果 2.16 秒に落ちます。
- 最後に、サブセレクトを PSM ユーザー定義関数としてリライトしました。これは、もう1つのテクニックで、SQL 文内のロジックをencapsulateするのによく使われる方法です。この関数は、下のようなものです。
- create function dba.foo(in @x integer, in @y integer, in @w integer, in @z integer) returns char(1)
- begin
- declare @lResult char(1);
- if ISNULL(@x,-1) = -1 then return 'N' end if;
- select R.X into @lResult
- from R
- where R.PK1 = @x
- and R.PK2 = @y
- and R.PK3 = @w
- and R.PK4 = @z;
- return(@lResult)
- end
すると、クエリーは下のようになります。
- select max(dt.a), max(dt.y)
- from ( select T.a, foo(Tx, T.y, T.w, T.z ) as y from T ) as dt
このクエリーの実行時間は、およそ 6 分（320 秒）でした。
なぜでしょうか? この関数のプリアンブル内の条件的なロジックは、クエリー内へのインラインを防ぎ、実行コストにはユーザー定義関数に必要な実行コンテキストの構成/分解、そして、ユーザー定義関数の SELECT 文の周期的な再最適化が含まれるからです。
ここで学んだことは何でしょうか? セットレベルオペレーションは、データまたはトランザクションの量が少ない場合には重要ではありません。しかしながら、このパワーはデータ量が大きい場合に効力を発揮します。お客様のマイレージは、もちろん様々異なります。リレーショナルデータベースシステムではこれらの構成をより効率的に実行できないのでしょうか? 答えは、できます。しかしながら、このレベルの洗練性を実装するには、かなりの時間がかかるでしょう。
過去のブログ記事より
