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

2021年4月22日2024年3月23日サポぶろぐ

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

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

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

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

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

第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の内容が表示されます！

■InfoAssist

[手順]

■App Studio

[手順]

ステップ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最適化されない原因がわからない、原因はわかったけど
どうすれば最適化できるのかわからないという場合は、サポートセンターまで問い合わせください！

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

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