2014/06/07

【C#】SQLを書くときはStringBuilder.AppendLineを使うべし

プログラム内でSQL文を書くことがよくある。
しかし、記述方式がまちまちで、見難いし腹が立ってくる。

だから「こう書け!」というのをまとめてみた。


ダメなコーディング


// ダメな例
string query = String.Empty;

query += "SELECT ";
query += "    DENPYONUM ";
query += "   ,ITEM ";
query += "FROM ";
query += "    DENPYO";
query += "WHERE ";
query += "    CHUMONBI >= '20140101'";


ダメな理由


  • string型の文字結合(+)は性能問題がある
  • 抽出条件にリテラルが使用されている
  • バグが見つけにくい

なぜリテラルを使っちゃいけないかというと
こちら→【PL/SQL】性能改善のためのバインド変数(ホスト変数)の使い方 を参照してほしい。





推奨しないコーディング



// 推奨しない例
StringBuilder query = new StringBuilder();

query.Append("SELECT ");
query.Append("    DENPYONUM ");
query.Append("   ,ITEM ");
query.Append("FROM ");
query.Append("    DENPYO");
query.Append("WHERE ");
query.Append("    CHUMONBI >= @DATE ");


推奨しない理由


  • ログやデバッグ時のSQLが1列になるため、読みにくい
  • バグが見つけにくい

StringBuilderを使っているため、性能的には問題ない。
しかし、バグが見つけにくい。

実際、上記コードにはDBExceptionになるバグが隠されている。
どこかわかるだろうか?

そのバグは、7行目のquery.Append("    DENPYO");の部分。
もうお気づきの方はいつかもしれないが、このSQL文は~ DENPYOWHERE CHUMONBI ~となる。




推奨するコーディング



// 推奨する例
StringBuilder query = new StringBuilder();

query.AppendLine("SELECT");
query.AppendLine("    DENPYONUM");
query.AppendLine("   ,ITEM");
query.AppendLine("FROM");
query.AppendLine("    DENPYO");
query.AppendLine("WHERE");
query.AppendLine("    CHUMONBI >= @DATE");


推奨する理由


  • StringBuilderを使用しているため、性能問題はない
  • 文字列の前後にスペースを入れる必要がない
  • ログに改行された状態で表示されるため、読みやすい




例外だってある(性能重視!)



推奨コーディングを紹介したが、シビアな性能が求められている場合は、一概にこうとは言えない。



StringBuilder.Appendを使う


AppendとAppendLineの違いは、後ろに改行コードがつくかどうか。
コンマ何秒の性能改善が求められる場合は、Appendを使うことでDBMSのキャッシュを圧迫しなくてすむ。



余分なスペースを消す


Appendを使う理由同様に、余分なスペースを消すことでDBMSのキャッシュを圧迫しなくてすむ。
特に長いSQLや、無駄なコメントが書かれている場合に有効だ。



StringBuilderのキャパシティを指定する


StringBuilder.Capacityで想定される最大文字列を指定しておくと、必要以上にメモリを割り当てなくてすむ。
また、StringBuilderの仕様として、文字列が指定容量を超えた時に、自動的に大きなバッファを割り当て、今までのデータをコピーする。
だからCapacityの値を頻繁に更新させないために、最初に必要容量を確保しておく。


参考:StringBuilder.Capacity プロパティ (System.Text)


// 性能を求めて
StringBuilder query = new StringBuilder(128);

query.Append("SELECT ");
query.Append("DENPYONUM ");
query.Append(",ITEM ");
query.Append("FROM ");
query.Append("DENPYO ");
query.Append("WHERE ");
query.Append("CHUMONBI >= @DATE");

ポイントは2点。
1点目は、あらかじめCapacityを決めておく。
規定は16で、16を超える度にバッファが倍々に増えていき性能が落ちるため。

2点目は、1行でstring query = “SELECT DENPYONUM ~”;と書いてもいいけど、最低限の可読性・保守性を残しておくこと。




以上

6 件のコメント :

  1. リテラルの連結にStringBuilderなんか使っちゃダメ。
    @付きのリテラルで改行コート付きで書きましょうね。
    +=もダメですね。せめて+で結合しましょうね。
    連続でStringBuilder.AppendLineなんて書いてあったらソースを再確認したほうがいいです。
    StringBuilderを使うべきはそこではないから、C#を知らない人が書いていると思いましょう。

    返信削除
    返信
    1. ザ・マニュアル人間ですね。

      削除
    2. ごめんなさい、なぜStringBuilder.AppendLineを使うかちゃんと説明できていなかったですね。

      ご指摘のとおり、@をつけてヒアドキュメントのように書くのもわかりやすいです。
      ですが、SQLを組み立てるときに1箇所にまとめて書く、というのはあまり見かけません。

      たとえば条件分岐によりSQLの構造が大きく変えなければならないシーンですと、ヒアドキュメントで書くより、AppendLineなどを使って1行ずつ組み立てて行くほうがわかりやすいときもあると思います。そういった用途では有用ではないでしょうか?

      ただ、C#は新卒の頃に1年くらい書いていただけで「C#を知らない人が書いている」と思われても仕方ありません。

      削除
  2. SQLを組み立てる際の昔から有名なテクニックですね。
    なぜStringBuilderを使うのか理由がわかって大変参考になりました。
    枠にはまった人では思いつかない活用方法ですね。

    返信削除
    返信
    1. コメントありがとうございます!
      参考にしていただいたようでなによりですw

      削除
  3. SQLの内容が動的に変わるなら、StringBuilderと@の改行コード付き文字列の組み合わせが良いと思います。

    SQLが動的に変わらないなら@の改行コード付き文字列だけでStringBuilderは不要でしょう。

    StringBuilderで一行ずつ組み立てるのは、書くのに余計な編集に余計な手間がかかりますので、避けるべきでしょう。

    可読性は好みの領域ですが、若干、私は@の改行コード付き文字列が読みやすいと思います。

    いずれにしても、@の改行コード付き文字列はこの用途でかなり使われていますので、本文で全く触れないのではなく、言及すべきでしょう。

    返信削除