メインコンテンツへスキップ

クエリチューニングの流れ

  • 更新
TOP

    概要

    本記事では、クエリチューニングの目的や作業について説明します。

    TDV Studioでは各クエリに対して実行計画を確認することができ、TDVサーバから各種データサービスに対しどのようなSQLを実行しているのかが表示されます。クエリ内の複雑な処理をデータソースで実行させる（プッシュダウン）ことで、TDVサーバでの処理負荷を軽減できます。プッシュダウンができないとTDVサーバ内で処理が実行され、データパイプラインのボトルネックとなります。

    実行計画を確認し、負荷の高い処理内容を明らかにすることでクエリのチューニング箇所を特定します。

    実行計画の表示方法、およびその他のパフォーマンスチューニングについては以下の記事をご参照ください。

    パフォーマンスチューニングのベストプラクティス

     

    クエリチューニングでの目的

    実行計画に表示される各ノードは、TDVで行われている何らかの処理を表しています。

    以下の例では、Fetch や JOIN 、GROUP BY などの処理がTDVで行われていることがわかります。

    plan_node01.png

    TDVのクエリチューニングでは、このノード数を可能な限り減らすことを目的とします。

     

    クエリチューニングの作業

    ノード数を減らすために以下の作業を行います。

     

    1. Fetch数を確認する

    結合の順番や後述する関数のプッシュダウンができていない場合、余分なFethが発生しパフォーマンスが低下する可能性があります。

    理想は「Fetch数 = 異なるデータソース間での結合回数 + 1 」です。

     

    同じデータベースに存在するテーブルを複数のデータソースに分けてしまうと、Fetch数が増えてしまいます。

    以下の例では、同じデータベースのデータソースを2つイントロスペクトし、結合を行っています。

    fetch2.png

     

    同じデータソース内の結合であれば、Fetchは1つになります。

    fetch1.png

     

    2.「No Push Reason」を含む文を探す

    実行計画の各ノード選択、もしくは実行計画をテキストファイルとしてエクスポートし、「No Push Reason」を含む文が存在しないか確認します。

    「No Push Reason」が含まれる文が実行計画にある場合、クエリ内の関数がデータソースへプッシュダウンできていません。

    関数がデータソースへプッシュダウンできない場合、パフォーマンスが低下する可能性があります。

    groupby1.png

     

    plan02.png

     

    3. アダプタの設定ファイルを編集する

    「No Push Reason」となる関数が見つかった場合、アダプタの設定ファイルに当該関数が存在するか確認します。

    1. アダプタの設定ファイルが存在する以下のパスに移動します。

    [TDV Serverインストールフォルダ]\conf\adapters\system\[アダプタ名]

    カスタムJDBCアダプタの場合は以下のパスに移動します。

    [TDV Serverインストールフォルダ]\conf\adapters\custom\[アダプタ名]

    2. <adapter name>_values.xmlという形式のファイルをテキストエディタなどで開きます。

    3. ファイル内をプッシュダウンしたい関数を検索し、/runtime/query/<関数名>のname属性を持つ attribute を検索します（コメントアウトされています）。

    4. 該当の attribute 全体をコピーし、ファイル先頭から4行目あたり、Please insert customized elements after this line.のコメント下にペーストします。

    5. value属性を適切な値に設定します。

    6. TDVサーバを再起動し、関数がプッシュダウンされていることを確認します。

     

    具体的な例は以下の記事をご参照ください。

    GROUP BY句のプッシュダウンについて

    DISTINCT句のプッシュダウンについて

     

    アダプタの設定ファイルに関数が見つからない場合は他のアダプタのcapabilities ファイルをを参考に必要があります。

    他のアダプタのcapabilities ファイルの内容をチューニング対象のアダプタのcapabilities ファイルに追記することも可能ですが、編集した内容はアップグレードやCARでの以降の際に引き継がれないため、XMLファイルを編集してチューニングを行うことを推奨します。

     

    アダプタの capabilities ファイルは以下のパスに存在します。

    [TDV Serverインストールフォルダ]\app\dlm\apps\[アダプタ名]\conf

    例：SQL Server の場合（バージョンに限らず）

    [TDV Serverインストールフォルダ]\app\dlm\apps\[アダプタ名]\conf\cis_ds_mssql

     

    例）プッシュダウンできていない関数がある場合のチューニング

    SQL Server 2019 と PostgreSQL 12 の2つのデータソースから作成されたビューを例とし、実行計画からクエリチューニングを行う流れについて説明します。

     

    構成

    PostgreSQLのテーブルがが4つ、SQL Serverのテーブルが3つで構成されています。

    lineage.png

     

    チューニング前のSQLは以下の通りです。

    SELECT 
    East_Sales.sales_id, 
    East_Sales.sales_cust, 
    East_Sales.sales_emp, 
    mst_customer_e.cust_name, 
    mst_customer_e.cust_area, 
    East_Order.order_id, 
    East_Order."date", 
    East_Order.quantity, 
    East_emp.emp_id, 
    East_emp.emp_name, 
    mst_jst_e.CCC, 
    mst_jst_e.DDD, 
    mst_area_e.tk, 
    mst_area_e.price
    FROM 
    /shared/Folder/L1_PhysicalLayer/Postgres/East_Sales East_Sales INNER JOIN 
    /shared/Folder/L1_PhysicalLayer/SQL_Server/mst_customer_e mst_customer_e
    ON East_Sales.sales_cust = mst_customer_e.cust_id INNER JOIN 
    /shared/Folder/L1_PhysicalLayer/Postgres/East_emp East_emp
    ON East_Sales.sales_emp = East_emp.emp_id INNER JOIN 
    /shared/Folder/L1_PhysicalLayer/SQL_Server/mst_jst_e mst_jst_e
    ON mst_customer_e.cust_area = mst_jst_e.area INNER JOIN 
    /shared/Folder/L1_PhysicalLayer/Postgres/East_Order East_Order
    ON East_Sales.sales_id = East_Order.order_id INNER JOIN 
    /shared/Folder/L1_PhysicalLayer/SQL_Server/mst_area_e mst_area_e
    ON mst_jst_e.RFP = mst_area_e.tk
    WHERE East_Sales.sales_id = ALL (
    SELECT MAX(sales)
    FROM /shared/Folder/L1_PhysicalLayer/Postgres/target
    WHERE "year" = '2022'
    GROUP BY
    "year"
    )

     

    1. Fetch数を確認する

    異なるデータソース間での結合回数、つまりPostgreSQL と SQL Server のテーブルとの結合は「East_Sales.sales_cust = mst_customer_e.cust_id 」の1回だけとなります。

    今回チューニングではFetch数が 2 となることが理想です。

    しかし、ビューの実行計画を確認すると、Fetch数が 5 となっています。

    query_plan01.png

     

    2.「No Push Reason」を含む文を探す

    「No Push Reason」となっているノードを探します。

    FILTERノードに「No Push Reason」が表示されていました。

    No Push Reason :

    Cannot push predicate because the data source PostgreSQL does not support = ALL quantified comparison operator

    「ALL quantified comparison operator」つまりALL演算子が PostgreSQL ではプッシュダウンできていないようです。

    query_plan02.png

     

    3. アダプタの設定ファイルを編集する

    ALL演算子のプッシュダウンについては、別の記事で紹介していますが本手順では、「クエリチューニングの作業」の手順に沿って進めていきます。

    PostgreSQLのアダプタの設定ファイルを開き、「ALL」で検索します。

    いくつかヒットしましたが、ALL演算子に関する要素は見つかりませんでした。

    query_plan03.png

     

    「No Push Reaso」の説明文に記載されている単語「quantified」、「comparison」でも検索しましたが、ALL演算子に関する要素は見つかりませんでした。

     

    4.  他のアダプタの capabilities ファイルからパラメータを探索

    TDVでは SQL Server がデフォルトでALL演算子をプッシュダウン可能なため、SQL Server のcapabilities ファイルのパラメータを探します。

    「ALL」で検索したところ、ALL演算子に関連しそうなパラメータは見つかりませんでした。

    query_plan04.png

     

    「quantified」で検索したところ関連しそうなパラメータが見つかりました。

    query_plan05.png

     

    5.  パラメータをXMLファイルに適用

    PostgreSQLのアダプタの設定ファイルを開き、先ほど見つけたパラメータをXML形式に変換し、追記します。

     

    capabilities ファイルのパラメータは以下の形式で記載されています。

    [configID]: [value]

    XMLファイルへは以下のように追記します。

    <ns[一意の番号]:attribute xmlns:ns[一意の番号]="http://www.compositesw.com/services/system/util/common">
    <ns[一意の番号]:name>/runtime/query/[一意の名前]</ns[一意の番号]:name>
    <ns[一意の番号]:type>[型]</ns[一意の番号]:type>
    <ns[一意の番号]:value>[capabilities ファイルのvalue]</ns[一意の番号]:value>
    <ns[一意の番号]:configID>[capabilities ファイルのconfigID]</ns[一意の番号]:configID>
    </ns[一意の番号]0:attribute>

     

    ALL演算子の場合は以下の通りとなります。

     

    適用させるcapabilities ファイルのパラメータ

    jdbcds.support_quantified_comparisons: true

    例）XMLファイルに追記する内容

    <ns37:attribute xmlns:ns37="http://www.compositesw.com/services/system/util/common">
    <ns37:name>/runtime/query/ALL</ns37:name>
    <ns37:type>BOOLEAN</ns37:type>
    <ns37:value>true</ns37:value>
    <ns37:configID>jdbcds.support_quantified_comparisons</ns37:configID>
    </ns37:attribute>

     

    TDV Serverを再起動すると、ALL演算子がプッシュダウンされ、Fetch数が2となりました。

    mceclip0.png