このページは、以下の英語ページの抄訳です。最新の情報については、英語ページを参照してください。
https://blogs.sap.com/2013/09/18/from-the-archives-how-to-give-a-query-optimizer-fits/
この記事のオリジナルは、Glenn Paulley が sybase.com に 2008 年 9 月に掲載したものです。
私は現在 SQL 構造の研究にたいへん興味を持っています。
よくみられる SQL 構造を理解して予測するのは、私の仕事の一部です。なぜならば SQL Anywhere サーバーはこれらの文を最適化して実行しなければならないからです。
私はアプリケーション開発者がどのようにアプリケーションを書くのか、そしてデータベースのサブ言語として SQL が成功しているのか（あるいは失敗しているのか）に関してさらなる洞察を得たいと思っており、これらの構造の研究に興味を持っています。
最近、お客様数社とのやりとりで、ある特定の SQL 構造において興味深いトレードオフがみられましたので、お客様の実例で解説したいと思います。
ただし、ここでは、SQL Anywhere 11 のサンプルとして提供されている DEMO データベースで実行できるように変換して説明します。
CREATE FUNCTION CurrentSalesRep(IN @CustomerID INTEGER, IN @FinancialCode CHAR(2), IN @Region CHAR(20), IN @dFrom DATE, IN @dTo DATE) RETURNS INTEGER
BEGIN
DECLARE @SalesRep INTEGER;
SET @SalesRep = NULL;
IF ISNULL( @CustomerID, 0 ) <> 0 then
SELECT FIRST r.SalesRepresentative INTO @SalesRep
FROM SalesOrders r
WHERE r.CustomerID = @CustomerID AND r.FinancialCode = @FinancialCode
AND TRIM(ISNULL(r.Region, '')) = TRIM(ISNULL(@Region, ''))
AND ISNULL(r.OrderDate, '1900-01-01') <= ISNULL(@dTo, CURRENT DATE)
AND ISNULL(r.OrderDate, '2200-01-01') >= ISNULL(@dFrom, CURRENT DATE)
END IF;
RETURN @SalesRep
END
この関数は、以下のパラメーターで呼び出されています。
SELECT CurrentSalesRep( 101, 'r1', 'Eastern', '2000-03-01', '2000-04-01') FROM DUMMY
*（注）この例ではオリジナルから改編するにあたり少し策を講じています。
お客様の関数では、このクエリーは @dTo と @dFrom を比較するために 2 つの異なる日付を参照していました。
しかし、残念ながら、Demo データベースの SalesOrder テーブルには、単一の DATE カラムしか含まれていません。
そこで、これを補完するための CurrentSalesRep プロシージャー内のクエリーのテキストプランが以下です。実際のクエリープランをトレーシングするアプリケーションプロファイリングを使用してキャプチャーしたものです。
( Plan [ Total Cost Estimate: 0.00069293, Estimated Cache Pages: 4960 ]
( RowLimit
( IndexScan ( SalesOrders r ) FK_CustomerID_ID[ r.CustomerID = 102 : 0.99869% Statistics ]
[ ( r.FinancialCode IS NOT NULL : 100% Statistics | Bounded ) AND
( r.FinancialCode = 'r1' : 100% Statistics ) AND
( 'Eastern' = trim(isnull(r.Region,'')) : 5% Guess ) AND
( CAST(r.OrderDate AS timestamp) >= 2000-03-01 00:00:00.000 : 25% Guess ) AND
( CAST(r.OrderDate AS timestamp) <= 2000-04-01 00:00:00.000 : 25% Guess )
] )
)
)
ここでいくつかのことがみられました。
- マイナーな詳細 ： このクエリーは、
SELECT FIRST を使用していますが、ORDER BY 句を含んでいないため、SQLCODE +122 アラート (決定的な結果にならない) が出ました。
- Line 5 のプロシージャーは、よくみられるデータ管理の問題を指摘しています。このケースでは、プログラマーは、NULL 値、または 0 の値のどちらかにリスクを分散して「未知」の Customer ID を表しています。このお客様のプロシージャーとビュー全体にわたってこの構造が使用されており、興味深い副作用がみられます。
- クエリー自体をみてみます。最初に注目すべきは、Line 8 です ： @CustomerID は、すでに検証されています。しかし、@FinancialCode は全く検証されていません。潜在的な問題になるでしょうか？ おそらく：このプロシージャーへのその他の引数は全て検証されています。パフォーマンスの観点で良い点は、Line 8 のそれぞれの述部が sargable であり、Line 19-21 のテキストプランを見ることで検証できます。
- Line 9 は問題です。 ISNULL 関数をまた使用した結果です。NULL または空の文字列のどちらも、同様に扱われるべきです。SQL Anywhere 11 のクエリーオプティマイザーは、理にかなっており賢いため、クエリー実行とは独立して
TRIM(ISNULL(@Region, '')) を評価できることを検知します。そしてこの特定の呼び出しに対する結果が、文字列「Eastern」（Line 22 参照）です。しかしながら、SalesOrder テーブル内の Region カラムは、null になることが可能なため、他の式ではこのような単純化は行われません。r.Region に対する ISNULL 関数は、述部が sargable にならないようにし、この式はテーブルカラムではないため、オプティマイザーはカラムヒストグラムを利用してこの述部の選択性を評価できません。むしろ、オプティマイザーは、5 % の選択性という「魔法」の値を仮定します。これは、非常に大雑把な想定でしかありません。
- Line 10 と 11 は、9 の問題と似た結果になります。しかし、わずかに異なる理由によります。問題は NULL 値がオンザフライでデフォルトの日に置き換えられることです。この代用は、このプロシージャーにだけ有効と思われます。さらに暗に示されるものがあります。Line 10 の述部を例として使用したいと思います。クエリーの最適化はこのような不平等性に対しても賢く機能し、特別な register である
CURRENT DATE を評価する必要性を除外して引数 @dTo が NULL ではないと判断します。しかしながら、このような不平等性が存在する反面、リテラル 文字列 ‘1900-01-01’ は、DATE としては認識されません。なぜならば、文字列はパースされてなければならず、フォーマットは接続オプション設定に依存してしているため、この文字列は潜在的な TIMESTAMP と仮定されるからです。SQL Anywhere は、特定のケースによっては、この状況を認識してリテラル定数を自動的に DATE に CAST します。しかしこのケースでは、クエリーの中に ISNULL 関数が存在するため、それはありません。このインパクトは、r.OrderDate を含む比較が TIMESTAMP ドメイン (ライン 23 と 24 参照) で発生するということです。このセマンティクスは正しいものです。問題は、選択性の見積もりです。なぜならば、比較は TIMESTAMP ドメインにあり、DATE ドメインにはないからです。このような状況では、OrderDate のカラムヒストグラムは無視され、25% の「魔法」 ( 大雑把な推測 ) の選択性の見積もりが使用されるからです。LHS 式を ISNULL(r.OrderDate, CAST( '1900-01-01' as DATE)) と入れ替えることによって、DATE ドメインにおける比較が可能になります。SalesOrder.OrderDate は null にすることが可能ではないため、いずれにしても ISNULL 関数は意味がありません。オプティマイザーはこれを除外し、選択性見積もりのカラムヒストグラムの使用を有効化して、潜在的に OrderDate 上のインデックスを通して SalesOrder のローを retrieve することによって述部を sargable として扱うことが可能になります。
最終的に、この例では、パラメーター化されたクエリーが原因で失われた述部の最適化機会は、それほど問題あるものではありません。なぜならば、これらはアクセスプラン全体に影響を与えるわけではないからです。
@CustomerID の述部は、外部キーインデックス（Line 19）を使用して SalesOrder テーブルへのインデックスされたストラテジーをイールドします。それゆえこのクエリーのアクセスプランキャッシングは可能です。これにより、複数の呼び出しにわたる最適化のコストをアモタイズすることで全体の elapsed 時間をさらに改善することができます。これのマイナス面は、そのパラメーターが NULL 値ではない場合に
ISNULL 関数の除外のような値固有の最適化は実行できないということです。なぜならば、キャッシュされたプランでは、変数の値は、最適化時では未知だからです。このようなプロシージャー内のクエリーに対して SQL Anywhere はプランキャッシングを自動的に実行します。
私のチームでは、パラメーター化されたクエリーが発生させうるいくつかの問題について、全てではありませんが、軽減できる適応可能なランタイム最適化戦略について話し合いました。そのうち主な 2 つは、述部の選択性の見積もりと、中間結果のカーディナリティーの見積もりです。
最適化は重要です。なぜならば、ユーザーが毎回必要とされる
正確なクエリーを構築することによって潜在的にパラメーターをなくすことができたとしても --- プロシージャー内のネストされた
IF または構築されたクエリー文字列に
EXECUTE IMMEDIATE を使用して --- 可能性のあるクエリーの数は、変数の数において指数関数的だからです。さらに、結局パラメーターをなくすことがクエリー実行パフォーマンスに対して違いをもたらすのかを決定するためには多少の専門知識が必要です。というのは、実行結果プランの品質とそれぞれ、そして全ての invocation の最適化のオーバーヘッドとのトレードオフだからです。本当にセルフマネージングなデータベースであるためには、データベースシステム自身でこれを自動的に行う必要がありますが、これは難しい問題です。
上の例はとてもシンプルです。クエリーにテーブルは 1 つしかありません。正常機能しなくなるケースは、パラメーター化されたクエリーに join がいくつか含まれる場合です。このような場合には、選択性の見積もりの誤りや、おそまつな仮定によってカーディナリティーの見積もりの問題になりがちです。そして、それが発生した場合、オプティマイザーが選択した join の順序が好ましくないものになりかねないリスクがあります。
次にこの問題に関する 2 つ目の例を説明します。同様にこのクエリーのコンテキストは、ストアドプロシージャー内に存在します。引数は、“@” サインを使用して表示されています。
SELECT c.client_id, c.term_date, c.created_time
FROM client c
JOIN branch b ON (b.branch_id = c.branch_id)
JOIN area a ON (a.area_id = b.area_id)
JOIN region r ON (r.region_id = a.region_id)
, employee e
WHERE r.region_id = COALESCE(@region_id, r.region_id)
AND a.area_id = COALESCE(@area_id, a.area_id)
AND b.branch_id = COALESCE(@branch_id, b.branch_id)
AND emp_id = @emp_id
AND @emp_id =
CASE e.emp_role
WHEN 'BM' THEN b.BRANCH_MGR_ID
WHEN 'AVP' THEN a.AREA_MGR_ID
WHEN 'RP' THEN r.REGION_MGR_ID
WHEN 'RA' THEN r.REGION_ADMIN_ID
WHEN 'CA' THEN e.emp_id
WHEN 'CU' THEN e.emp_id
END;
この例におけるクエリーは、最初の例と同様の問題に直面しています。しかしながら、join が含まれているため、カーディナリティーの見積もりエラーによって次善のプランに導かれる可能性があります。特に、クエリーの
WHERE の最後の
CASE 式に注意してください。ここでできることは join ストラテジーの最後の最後でこれを評価する以外、ほとんどありません。なぜならば、プラン内のほとんど全てのテーブルに依存するからです。アクセスプラン内のこのような配置は、述部によるあらゆる中間結果のサイズ制限を役に立たないものしてしまいます。
2 つめの例に関して、iAnywhere のコンサルティングチームの Dave Fishburn に感謝します。
そして、述部の最適化に関するテクニカルな詳細について確認してくれた Ivan Bowman にも感謝を述べたいと思います。
===
SQL Anywhere に関してはまずは
こちらをご参照ください。無期限でご利用いただける無償の Developers Edition もこちらからダウンロードが可能です。
その他 や、
SAP SCN Wiki などもご参照ください。
SQL Anywhere に関して技術的な質問のある方は
コミュニティに登録し、
「Ask a Question」機能をご利用ください。
Language には「Japanese」、
Primary Tag には「SAP SQL Anywhere」を選択
User Tagに「sql anywhere」「sql anywhere Japanese question」
を入力してください。
不具合につきましては、サポート契約者様専用の問い合わせ方法にてお問い合わせください。
======================
ご購入に関するお問い合わせ
こちらよりお問い合わせください。
