「遅い…」レスポンス課題の調査・見直し観点は?SQL最適化編

サポぶろぐ

皆さま、お久しぶりです!
株式会社アシストでWebFOCUSのサポートを担当しています津田です。

今回は、前回に引き続き レスポンス課題 に関するテクニックをご紹介します。

「遅い…」レスポンス課題の調査・見直し観点は?SQL最適化編

我々がお客様からレスポンス課題に関するご質問を頂いた際には、主に以下2つの観点
調査をすることが多いです!

  ※ 細かくお伝えすると、以下2つ以外の観点での調査もしているのですが、全て紹介すると記事が小論文みたいな
   長さになりそうなので割愛します。

   1.ボトルネックの確認について
   2.SQLの最適化について

第5回では[ボトルネックの確認]について紹介したので、本記事では[SQLの最適化]について
紹介します!

🔷「SQL最適化」って何??🔷

「SQL最適化」とは、データの作成処理をできるだけ多くDB側で実行させることです!
 …と言ってもわかりにくいと思うので、以下のイラストをご確認ください。

データ取得の流れ

     

WebFOCUSではレポートやグラフを作成する際に、上のイラストのように
役割を分担しています。
WebFOCUSで作成した仕様書をできるだけ多くDBに伝わる言葉(SQL)へ変換することで、
多くの仕事をDBに任せることができます!

🔷なぜ「SQL最適化」で速くなるの?🔷

「SQL最適化」を行うことで、以下の2つの観点から処理が速くなる可能性があります!

   (1)DBからWebFOCUSへ転送するデータ量を削減できる
   (2)WebFOCUSよりDBの方が演算速度が速い

(1)WHEREによるデータの絞り込みや、GROUP BY によるデータのグループ化、SUM関数による
 集計などをDB側で先に行うことで、テーブル内のすべてのデータを持ってくるのではなく、
 必要なデータだけをWebFOCUSへ転送することができます。
 そのため、転送速度が大幅に向上する可能性があります。

(2)WebFOCUSで演算を行うより、DBの方が演算速度が速いことが多いです。
 もちろんDBの種類や処理内容によって必ずしもどちらの方が速いとは言い切れませんが、
 WebFOCUS側で処理を行って速度が遅いと感じた場合は、一度DB側で処理をさせてみると
 速くなる可能性があります!

🔷どうやって「SQL最適化」するの?🔷

では、次はいよいよどうやったら「SQL最適化」できるのかを話していきます!

サポートセンターでは以下のような方法で「SQL最適化」を行っています。
 *後述しますが、一部機能については最適化されない場合もございます

<ステップ1>   
  WebFOCUSが発行しているSQLを確認し、WHEREやJOIN、グループ化などの
  処理がSQLへ変換されているか確認する

<ステップ2>
  最適化の妨げになっている処理を、SQLへ変換できる処理に変更する


ステップ1:発行されているSQLの確認

発行されているSQLを確認するには、SQLトレースを取得する必要があります。
コマンド、App Studio、InfoAssistから取得する方法をそれぞれ紹介します!

コマンド

プロシジャの先頭に以下のコマンドを追加して、プロシジャを実行してください。
ブラウザ上にWebFOCUSがDBへ発行するSQLの内容が表示されます!

SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
SET TRACESTAMP = OFF
SET TRACEUSER = ON
SET XRETRIEVAL=OFF


InfoAssist

[手順]

1)InfoAssistにて画面上部の実行ボタン横の[▼]をクリックします。
2)[SQLトレース]を選択します。

InfoAssistでSQLトレースを取得


App Studio

[手順]

1)レポートキャンバスにてレポートを開きます。
2)画面上部の[表示]タブより、[SQL]欄の[生成されたSQLを表示]ボタンをクリックします。

App StudioでSQLトレースを取得


ステップ2:最適化の妨げになっている処理を、SQLへ変換できる
         処理に変更する

どの処理がSQLに変換されて、どの処理がSQLに変換されないのかという明確な基準は、
残念ながら公開されていません。
SQL変換されていない原因を探る時は、怪しい記述箇所を1つずつ削っていき、
都度SQLトレースの内容を確認することで、原因個所を特定していきます。

なお、今までの弊社お問い合わせ実績から以下の場合にはSQL最適化が実行されないことは
確認されています。
※すべてのパターンを網羅しているわけではありません

 ・自己参照式を使用している
 ・一時項目の式に、SQL最適化できない関数を使っている

■ 自己参照式(X=X+1)を利用している場合

  [サンプル]
    -*------------------------------
    DEFINE FILE TESTDATA
     SAMPLE/I2 = SAMPLE + 1;
    END
    
    TABLE FILE TESTDATA
    SUM COL1
    BY SAMPLE
    BY COL2
    END
    -*------------------------------
    [実行されるSQL]
    -*------------------------------
    SELECT   
    T1."COL2",  
    T1."COL1"  
    FROM   
    testdata T1; 
    -*------------------------------

SQL最適化できない関数

SQL最適化できない関数を一時項目の演算式で使用している場合は、その一時項目は
最適化されません。

最適化されない関数に関しては、SQL最適化レポートに一部の情報が公開されています。
参考までにご確認ください。

※注意※

SQL最適化レポートは最適化検討の際の参考情報として公開されていますが、
最適化可否の基準を明記した公式文書ではございません。
参考程度にとどめていただけますようお願いいたします。

 ・DBの種類やバージョンなどによってSQL最適化レポートで最適化されると
  記載されている場合でも最適化されないこともあります。

 ・SQL最適化レポートに記載がない関数でも最適化される場合もあります。

    [最適化レポート確認手順]

1. Webコンソールにアクセスします。(http://サーバ名:8121/)

2. 画面左端より [アダプタ]アイコンを選択し、画面上部から [オプション]アイコン(▽)
  – [SQL最適化レポート] を選択します。

3. [アダプタサブカテゴリ] で [1] を選択します。
  [関数カテゴリ] は表示したいカテゴリを選びますが、選ばなければすべて表示されます。
  [関数の説明を表示]、[DBCS構成の表示]、[キーパラメータ値の表示]各種にチェックを
  入れます。

4. [レポートの表示] を押下します。

5. 各関数におきまして、ご使用のアダプタ列が「はい」になっている関数は
  SQL最適化される関数です。

なお、上記情報を踏まえまして、SQL最適化されない原因がわからない、原因はわかったけど
どうすれば最適化できるのかわからないという場合は、サポートセンターまで問い合わせください!

次回は 「無償で使える!CSVからレポートを作成する方法 」編です!

WebFOCUSでは、DBだけでなくCSVファイルのデータからレポートを作成することもできます。
詳しい作成方法については、次回の記事でご紹介しますのでお楽しみに!

<この記事をかいた人>

株式会社アシスト DX推進技術本部データ活用技術統括部  津田 明日香
入社歴   : 2018年新卒入社、3年間 WebFOCUS のサポートを専任
趣味     : アニメ鑑賞、最近はVtuberに熱中
ひとこと : 雨にも負けず、風にも負けずサポセン稼働中
      (なお土日は休みます)