プログラムでも書いてみよう

SQL高速化-5

システム内で実装されるSQLのコード量とそれに伴う作業量を考えてみましょう。
システム内でのSQLロジックよりもSQLをコールするアプリケーションのSQL関連処理やアプリケーションの主要処理のほうが多くなるのが一般的でしょう。
　また、システム全体から見て、コード量とそれに伴う作業量の大きなウェイトを占める処理としては画面などUI関連処理やエラー処理などその他の処理があげられるのでは無いでしょうか。

　ではSQLの処理内容から考えて処理時間に占める割合はどうでしょう？
SQLはデータにアクセスすることが主目的です、データはアクセスに時間がかかるハードディスクなどストレージに格納されますのでこの部分にかかる処理時間は大きいでしょう。
　また、通常はクライアント/サーバーになりますので、データ転送で時間のかかるネットワークを利用することでも処理時間が大きくなります。
　SQLの内部処理を考えた場合も、絞込みをするために大量のデータに対する抽出条件での比較や、データを結合する時の結合条件での比較、並び替えを行う時の比較など一回の処理時間はかからない処理ですが処理する対象が大量ですからその部分も大きなウェイトを占めてしまいます。
　システム内で占めるコード量と処理時間のウェイトを考えると以下のような関係が成り立ちます。

 

　理想的な動作をするSQLを書くことは、小さなコードで大きな処理時間のウェイトを占める可能性のあるロジックを排除することになるのです。
　
　これは言葉を変えると性能問題に直面したとき最小の作業で最大の効果をあげることが出来る可能性を秘めているのです。

自作フリーソフトi-port紹介ページ
http://www.geocities.jp/ys_and_otherjp/index.htm

SQL高速化-4

データベースを利用するメリットとして、データを格納するサーバーとして利用し複数のクライアントから同一のデータにアクセスするための機構をもっていることはシステム開発でとても大きなメリットになっています。
その重要な機能はクライアントが複数存在するC/S（クライアント/サーバー）環境の開発でははずせない機能でありほとんどの開発で必須条件として利用されています。
その場合サーバーとクライアントの関係は以下のような形になっています。

 

 

クライアント数とサーバー数の関係はサーバーをクラスタリング構成にするなどの環境でなければほとんどの場合サーバー１に対してクライアントnの関係が成り立ちます。

これは、データベースサーバー1台に対してWebサーバー1台のような実質同一台数のようなケースでもWebサーバー側で複数のプロセスやスレッドが起動されることにより1:nになっているケースも含みます。
この環境でのSQLについて考えてみましょう。
クライアントはデータの表示や、更新処理のためさまざまなSQLを発行しますがそのSQLはサーバー側で解析され、実行されます。
そのため、サーバーにかかる負荷はクライアント数に比例して増えていきます。
　仮に1時間にSQLを1回発行する場合、クライアント数が1台であればサーバーが処理するSQLは1回ですがクライアント数が10台になれば10回、100台になれば100回のSQLがサーバー側で処理されます。
SQL1回の負荷を１だとしても、実際のシステム稼動で考えた場合クライアント数分だけそのSQLが発行されますのでそれだけの負荷がサーバーにかかってしまうのです。

このケースで理想的な動作をしないSQLで実装されていたらどうなるでしょうか？
当然、SQL1回の負荷が1ではなくなり、仮に負荷が2であったとしてもクライアント数を掛けた分サーバーの負荷が増え最悪の場合サーバーが安定した動作が出来なくなってしまいます。

理想的な動作をするSQLと理想的な動作をしないSQLの１回の性能差がシステム全体でみると、性能差×発行回数×クライアント数の負荷になってしまうのです。

一つ一つのSQLが理想的な動作をするSQLにすることはシステム全体の処理速度に影響を与える重要な作業なのです。

自作フリーソフトi-port紹介ページ
http://www.geocities.jp/ys_and_otherjp/index.htm

SQL高速化-3

　性能問題に直面した場合、まずサーバーのハードウェア増強を考える方もいるでしょう。
これは、データベースサーバーのハードウェアリソースが十分でない場合有効です。　

しかし、ハードウェアでボトルネックになりやすい個所として、低速なリソースであるハードディスクやネットワークが上げられます。
これらは増強することによりアクセス速度の向上が見込みにくいデバイスになります。

　ではアプリケーション側からのアプローチを考えた場合どうでしょう。
１．通信量を減らすことによりネットワークに費やされる処理時間は短くなります。
l SQLで本当に必要なデータだけを取得しているでしょうか？
l 不要なSQLの発行はしていないでしょうか？
２．ハードディスクもアクセスするデータ量がければ処理時間の改善につながります。
l 正しくインデックスが利用されているでしょうか？
l データ全てを一括で取得し、アプリケーションで再抽出していないでしょうか？

　アプリケーションに問題があるケースや理想的でないSQLを処理を理想的な動作をするSQLに変えることで改善できる問題をハードウェア増強により対応することはナンセンスでしょう。
　性能問題に直面した時の正しいアプローチとしてはおおよそ以下の手順になるでしょう。

 

性能に対する改善の順番として理想的な動作をするSQLになっているか？アプリケーションがチューニングされているか？を確認しその上で性能的に問題が解決できない場合ハードウェア増強を検討すべきでしょう。
　理想的な動作をするSQLになっているか確認することはチューニングの第一歩なのです。
 
自作フリーソフトi-port紹介ページ
http://www.geocities.jp/ys_and_otherjp/index.htm

SQL高速化-2

　システム開発のテストフェーズから運用までを含めて、多くのシステムでは性能問題に直面するタイミングがあります。
　単体テストで実装されたシステムの一部分が極端に遅くないか初めてテストされます。
　結合テストで初めて実装されたシステムがユーザーの運用に耐える処理速度を持っているか試されるでしょう。
性能要件が明確である程度の規模の開発であれば負荷テストにより、性能要件を満たしているか精査されます。
そこまで確認された段階でリリースされますが、リリース後もユーザーの運用に基づき実際に長期にわたって評価され続けます。

開発するシステムのプロジェクト管理方法によってテストフェーズの内容や呼称は変わりますし明確にテスト仕様書にこれらの項目が記載されないこともあります。
また、ユーザーの評価でも明確な目標値に対しての評価ではなく「遅い」もしくは「遅くなった」などと漠然とした形のケース殆どでしょう。

では単体テスト、結合テストや負荷テスト、ユーザーの運用による実際の評価を通してシステム内で何が一番大きく変わるでしょうか？
　Webシステムなど接続クライアント数が変化する場合も考えられますが、RDBMSに格納されるデータ量に一番大きな変化が現れるのが普通でしょう。

単体テストの場合、少量のテストデータをもとにテストされ結合テストや負荷テストなどフェーズがリリースに近づくにつれてデータの整備も進み実際のデータに近い形になります。
　リリース後にテストデータの無い状態からユーザーの運用による実際の評価に入りますが、それ以降データベースのデータはどんどん蓄積されていくでしょう。
　もちろん、利用されなくなった古いデータを別のバックアップしデータベースのデータから削除して性能に問題が起こらないように考慮されたシステムもあります。
しかし、大抵フェーズとデータ量の関係を表すとおおよそ以下のようになるでしょう。

 

　では、データベースに格納されているデータが2倍になった時、SQLの検索時間は2倍になるでしょうか？
　「そうなるケースもあるがそうならないケースもある」というのが答えになります。
　
データ量が2倍になるということは検索対象も当然2倍になるので、2倍の処理を行わなければならないと考える方もいるでしょう。
しかし、RDBMSにはインデックスなどデータを検索する際に高速に検索する機能が備わっているため、これが有効に利用されていればそうはなりません。
但しこれには条件があります、理想的な動作をするSQLでなければインデックスなどデータを高速に検索する機能が利用されません。
　その結果、データ量の増加に伴い比例的に処理時間も長くかかってしまいます。

理想的な動作をするSQLを書くことは長期的にもシステムが性能問題に陥らないようにするための対策のひとつなのです。

自作フリーソフトi-port紹介ページ
http://www.geocities.jp/ys_and_otherjp/index.htm

SQL高速化-1

現在のシステムでデータを格納するとき、多くの場合データはRDBMS（リレーショナルデータベースシステム）に格納されるケースがほとんどだと考えます。
　そういったシステムの開発では詳細設計以降に携わる場合、そのシステムで利用される開発言語＋SQLが必須条件になります。

ほとんどのどの開発で必須条件になるSQLですが、皆さんはどのように学ばれてSQL自体をどのように考えられているでしょうか？
大半の方は、SQLの入門書を元にSQLの文法を学習し「必要なデータを読み書きできてさえしていれば正しい」といったレベルで理解されていると思います。

しかし、SQLに対しての認識は本当にそれで正しいのでしょうか？

RDBMSはデータの格納や検索のためにさまざまな工夫を持っていますが、しかし反面SQLについては、「プロが書いたSQLはアマチュアが書いたSQLの１００倍速い」と言われる事もあるようにSQLの書き方ひとつで性能が大きく変わってきます。
逆にいうと「アマチュアの書くSQLがプロの書くSQLと比べて遅いのは理想的な動作をするSQLになっていない」という事がいえます。

処理速度で１００倍というと極端な例のように感じられるかもしれませんし特異なケースのみをあらわしている様にも感じられるでしょう。
そこで、簡単なケースで一例をあげてみましょう。
データ件数が12万件格納された以下のテーブルに対して2つのSQLを発行したケースを考えてみましょう。

 

 

SQL文
1) SELECT * FROM 郵便番号一覧 WHERE 全国地方公共団体コード = 23447
2) SELECT * FROM 郵便番号一覧 WHERE 全国地方公共団体コード LIKE '23447'

SQL文の違いは全国地方公共団体コードを数値として比較しているか文字として比較しているかの違いで、処理結果のデータはまったく同じものが取得できます。
では、これらを処理するのにサーバーのコストはどのようになるでしょうか？
以下は私の手元にある環境で試したコスト比較です。

データベース	1のSQL	2のSQL
	処理コスト	処理コスト
SQL Server 2005 Express Edition	0.064875	0.95889

 

この処理コストの違いはインデックスを利用しているかそうでないかの違いです。
当然２よりも１のSQLで実装されるべきなのですが、これらについて記述された書籍は余りなく、開発現場でも「インデックスが利用されているはず」という前提で確認をしなかったり「そもそもインデックスなど意識してたことがない」開発者も多く正しく確認作業が出来ている開発者は少ないというのが私の認識です。
また、どちらのケースでも処理結果は同様の結果が出力されますので「必要なデータを読み書きできていれば正しい」というレベルで判断した場合、1も2も同様のSQLとなってしまいます。

これらは何によって確認すればよいのでしょうか？

実際には、これらの確認はRDBMSがSQLを解析した結果をどのように実行するか確認するためのツールを使って”実行計画”を元に判断する必要があります。

システム開発の中で速いSQLを書くことを一番意識すべきなのは誰でしょうか？

当然開発の中でSQLを実装する方になりますが、SQL担当というのは殆どのシステム開発では存在せず、プログラマが実装していると思います。
システム開発でRDBMSで性能が出ずデータベース担当者がチューニングを行うケースがあります。
しかし、データベース本体のチューニングはデータベース担当者の責任ですが、SQLの発行や最適なSQLになっているかを確認するのはSQLを実装するプログラマの責任なのです。

自作フリーソフトi-port紹介ページ
http://www.geocities.jp/ys_and_otherjp/index.htm