SQLの速度改善について（小ネタ）

開発中のプログラムがあまり進んでないのでSQLの小ネタ。
昔、よくあった遅いSQLの改善方法について、記載。
インデックスを張ったりするのもいいけど、SQLを作るときにデータをたくさん持ってきてまとめて条件分岐をCASE式でするだけで
圧倒的に速度が改善できる。

例えば、業務アプリで帳票などを作成する際に、列に月の集計結果を、持たせようとするケースがよくある。

　2018年　

　　　　　 　１月｜２月｜３月｜・・・・
得意先A　　　 100｜  60｜  40｜・・・・
得意先B　　　   0｜  20｜  40｜・・・・



こういう集計をSQL側でGroupByを何個も書いて集計していたりするのをよく見る。

（例）

      SELECT *
      FROM 得意先マスター AS 得意先
      LEFT JOIN (
　　　　　　　　　SELECT　SUM(売上金額） AS １月売上
　　　　　　　　　             ,得意先CD
　　　　　　　　　 FROM 売上テーブル
　　　　　　　     WHERE 年月 = '201801'
　　　　　　　   　GROUP BY 年月,得意先CD
　　　　　　　　） AS １月売上金額

　　ON　得意先.得意先CD = １月売上金額.得意先CD
　　以下、LEFT　JOIN　が１１ヶ月分続く


しかし、やむを得ない場合なら仕方ないけど、特に考えもせずにこんなソースを書いている人がいると殴りたくなってくる。
実際に昔、こんな感じのSQLを発行して、メモリエラーが発生するんでどうしたらいいでしょう。みたいなことを言われたことがある。
その時には３万行のSQLを発行して使い物にならなかった。

SUM関数の中にCASE式を書くことができるので、下記のSQLで一気に１２か月分のデータを取得できる。

（例）
　　　　　　SELECT　SUM(CASE WHEN RIGHT(年月,2) = '01' THEN 売上金額 ELSE 0 END） AS １月売上
　　　　　　             ,SUM(CASE WHEN RIGHT(年月,2) = '02' THEN 売上金額 ELSE 0 END） AS ２月売上
　　　　　　             ,SUM(CASE WHEN RIGHT(年月,2) = '03' THEN 売上金額 ELSE 0 END） AS ３月売上
　　　　　　             ,SUM(CASE WHEN RIGHT(年月,2) = '04' THEN 売上金額 ELSE 0 END） AS ４月売上
　　　　　　             ,SUM(CASE WHEN RIGHT(年月,2) = '05' THEN 売上金額 ELSE 0 END） AS ５月売上
　　　　　　             ,得意先CD
　　　　　　　　　 FROM 売上テーブル
　　　　　　　     WHERE LEFT(年月,4) = '2018'
　　　　　　　   　GROUP BY 得意先CD

件数のカウントとかも、「SUM(CASE WHEN RIGHT(年月,2) = '02' THEN 1 ELSE 0 END） AS ２月売上件数」とすることで
一つのSQL文で対応ができる。

Window関数を利用すればもっといろんなことができる。

意外と集計関数の中にCASE式が書けないことを知らない人がいたのでメモをしておく。
処理速度もSQLの文量も修正の手間も圧倒的に違うので、普段からある程度慣れ親しんでいてほしい。

SQLは慣れてくるとパズルみたいに思えてくるので、頭の体操としてはすごくいい感じ。

sqlite3を.NETで利用する

いろんなソフトのデータの保存方法についてシリアライズ化して保存したXmlファイルをまとめて
Zipファイルにすることで対応しようとしていたが、複数人で触ることを考えてsqliteのほうがトラブルが少なそうだったので
データの保存の関連を修正した。

データの取得については汎用性を持たせたかったので、DataSet、DataTableで取得するようにしておいた。
データを取得するときにインテリセンスが効かないので微妙な気もするが、
SQL側で集計したり、分析した結果を表示したりして列名を色々作ることが多そうなのでDataTableを利用したほうが融通が利く気がする。
融通を聞かせすぎると統一感がなくなるけど、どうせそこまで統一したシステムなんて組めないからいいんじゃないかな・・・。

インジェクション対策としてパラメータも利用できるようにしてみた。

sqlite以外でもコネクション周りを修正すれば利用できるDataBaseAccessクラスを作成した。
SQL Serverの場合であればSQLiteConnectionStringBuilderなどを軒並みSQLConnectionに変更すれば対応できる、気がする。
※SQL文の書き方が違うのでそのあたりは頑張っていただくしかないが・・・。
　どこかの機会に遅いSQLを早くするコツも書いてみたいと思う。いつになるかわからないけど。

そのときにサンプルプログラムを作成し、調査した内容をメモしておく。

sqliteをNuGetパッケージの管理からインストールし、参照の追加を行う




下記の using を追加する。

　　using System.Data;
　　using System.Data.SQLite;


↓↓↓↓↓ サンプルソース ↓↓↓↓↓

private void btnSample_Click(object sender, RoutedEventArgs e)
{
    string path = @"C:\test\SampleDataBase.db";
    var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = path };

    // ▽▽▽ データベース作成 ▽▽▽
    DataBaseAccess dataBaseAccess = new DataBaseAccess();
    dataBaseAccess.CreateDataBase_NewFile(path);
    // △△△ データベース作成 △△△


    // ▽▽▽ データ登録処理 ▽▽▽

    List<DataBaseAccess.ExecutionSQL> lstInsUpd = new List<DataBaseAccess.ExecutionSQL>();

    // 　　データ登録（ファイル実体）
    DataBaseAccess.ExecutionSQL InsSqlFile = new DataBaseAccess.ExecutionSQL(DataBaseAccess.ExecutionSQL.SqlType.INSERT);
    InsSqlFile.ExecuteSQL = " INSERT INTO T_File(intProjectNo,intCardNo,txtCommentNo,intFileNo,bloFile)  "
                              + "             VALUES(@intProjectNo , @intCardNo , @txtCommentNo , @intFileNo , @bloFile) ";
    InsSqlFile.Parameters.Add(new DataBaseAccess.SQLPara("@intProjectNo", "1"));
    InsSqlFile.Parameters.Add(new DataBaseAccess.SQLPara("@intCardNo", "1"));
    InsSqlFile.Parameters.Add(new DataBaseAccess.SQLPara("@txtCommentNo", "0"));
    InsSqlFile.Parameters.Add(new DataBaseAccess.SQLPara("@intFileNo", "1"));
    // 　　　　　ファイルをByte型配列で読み込み
    var data = System.IO.File.ReadAllBytes(@"C:\test\sample.txt");
    InsSqlFile.Parameters.Add(new DataBaseAccess.SQLPara("@bloFile", data));
    lstInsUpd.Add(InsSqlFile);

    // 　　データ登録（ファイル情報）
    DataBaseAccess.ExecutionSQL InsSqlFileInfo = new DataBaseAccess.ExecutionSQL(DataBaseAccess.ExecutionSQL.SqlType.INSERT);
    InsSqlFileInfo.ExecuteSQL = " INSERT INTO T_FileInfo(intProjectNo,intCardNo,txtCommentNo,intFileNo,txtFileName)  "
                              + "             VALUES(@intProjectNo , @intCardNo , @txtCommentNo , @intFileNo , @txtFileName) ";
    InsSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@intProjectNo", "1"));
    InsSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@intCardNo", "1"));
    InsSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@txtCommentNo", "0"));
    InsSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@intFileNo", "1"));
    InsSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@txtFileName", "sample.txt"));
    lstInsUpd.Add(InsSqlFileInfo);

    //　　 データ登録実行
    dataBaseAccess.ExecuteSQL_InsUpdDel(path, lstInsUpd);

    // △△△ データ登録処理 △△△

    // ▽▽▽ データ取得処理 ▽▽▽

    List<DataBaseAccess.ExecutionSQL> lstSelect = new List<DataBaseAccess.ExecutionSQL>();

    // 　　データを取得するためのSQL（ファイル実体）
    DataBaseAccess.ExecutionSQL SelSqlFile = new DataBaseAccess.ExecutionSQL(DataBaseAccess.ExecutionSQL.SqlType.SELECT);
    SelSqlFile.TableName = "T_File";
    SelSqlFile.ExecuteSQL = " SELECT * FROM  T_File ";
    lstSelect.Add(SelSqlFile);

    // 　　データを取得するためのSQL（ファイル情報）
    DataBaseAccess.ExecutionSQL SelSqlFileInfo = new DataBaseAccess.ExecutionSQL(DataBaseAccess.ExecutionSQL.SqlType.SELECT);
    SelSqlFileInfo.TableName = "T_FileInfo";
    SelSqlFileInfo.ExecuteSQL = " SELECT * FROM  T_FileInfo WHERE intProjectNo = @intProjectNo ";
    SelSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@intProjectNo", "1"));
    lstSelect.Add(SelSqlFileInfo);

    // 　　SQLを実行し、結果をDataSetで取得する
    DataSet ds = dataBaseAccess.GetDatas(path, lstSelect);

    // 　　取得したファイルを別名で出力する
    System.IO.File.WriteAllBytes(@"C:\test\output.txt", (byte[])ds.Tables["T_File"].Rows[0]["bloFile"]);

    // △△△ データ登録処理 △△△

}


/// <summary>
/// データベースアクセス関連
/// </summary>
public class DataBaseAccess
{

    #region Public Method

    /// <summary>
    /// 新規ファイル作成時のSQL
    /// </summary>
    /// <param name="strFilePath"></param>
    public void CreateDataBase_NewFile(string strFilePath)
    {
        ExecuteSQL_DDL(strFilePath, _GetCreateTableSQL_V0100());
    }

    /// <summary>
    /// SQL実行処理
    /// </summary>
    /// <param name="strFilePath"></param>
    /// <param name="ExecutionSQL"></param>
    /// <returns></returns>
    public DataSet GetDatas(string strFilePath, ExecutionSQL ExecutionSQL)
    {
        List<ExecutionSQL> lst = new List<ExecutionSQL>();
        lst.Add(ExecutionSQL);
        return GetDatas(strFilePath, lst);
    }

    /// <summary>
    /// SQL実行処理
    /// </summary>
    public DataSet GetDatas(string strFilePath, List<ExecutionSQL> ExecutionSQLs)
    {
        var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = strFilePath };
        DataSet dataSet = new DataSet();

        try
        {
            using (var cn = new SQLiteConnection(sqlConnectionSb.ToString()))
            {
                cn.Open();

                using (var cmd = new SQLiteCommand(cn))
                {
                    foreach (var param in ExecutionSQLs)
                    {
                        // ↓↓↓ チェック ↓↓↓
                        if (param.Type != ExecutionSQL.SqlType.SELECT) { throw new Exception("GetDatasメソッドはSELECT構文のみが実行可能です。SqlTypeを確認してください。"); }
                        // ↑↑↑ チェック ↑↑↑

                        cmd.CommandText = param.ExecuteSQL;

                        // SQLパラメータを設定
                        cmd.Parameters.Clear();
                        foreach (var item in param.Parameters) { cmd.Parameters.Add(new SQLiteParameter(item.Name, item.Value)); }

                        // SQLを実行し、データを取得する
                        using (var reader = cmd.ExecuteReader())
                        {
                            DataTable dt = new DataTable(param.TableName);
                            DataRow dr;

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                dt.Columns.Add(new DataColumn(reader.GetName(i), reader.GetFieldType(i)));
                            }

                            while (reader.Read() == true)
                            {
                                dr = dt.NewRow();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    dr[i] = reader[i];
                                }
                                dt.Rows.Add(dr);
                            }

                            dataSet.Tables.Add(dt);

                            reader.Close();
                        }

                    }

                }
            }

        }
        catch (Exception)
        {

            throw;
        }

        return dataSet;

    }

    /// <summary>
    /// SQL実行時の設定内容
    /// </summary>
    public class ExecutionSQL
    {
        public ExecutionSQL(SqlType sqlType)
        {
            this.Parameters = new List<SQLPara>();
            this.Type = sqlType;
        }

        public enum SqlType
        {
            INSERT, UPDATE, DELETE, SELECT
        }

        /// <summary>
        /// SELECT 構文の時には必須
        /// INSERT,UPDATE,DELETE 構文の時には不要
        /// </summary>
        public string TableName;

        /// <summary>
        /// 実行するSQL文
        /// </summary>
        public string ExecuteSQL;

        /// <summary>
        /// SQLのパラメータ
        /// </summary>
        public List<SQLPara> Parameters;

        public SqlType Type;
    }

    /// <summary>
    /// SQLパラメータ
    /// </summary>
    public class SQLPara
    {
        public SQLPara(string name, object value)
        {
            this.Name = name;
            this.Value = value;
        }
        public string Name;
        public object Value;
    }

    /// <summary>
    /// 登録・更新・削除のSQLを実行するためのメソッド
    /// </summary>
    public void ExecuteSQL_InsUpdDel(string strFilePath, List<ExecutionSQL> ExecutionSQLs)
    {
        var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = strFilePath };

        try
        {
            using (var cn = new SQLiteConnection(sqlConnectionSb.ToString()))
            {
                cn.Open();
                using (var cmd = new SQLiteCommand(cn))
                {
                    using (SQLiteTransaction trans = cn.BeginTransaction())
                    {

                        foreach (var param in ExecutionSQLs)
                        {
                            // ↓↓↓ チェック ↓↓↓
                            if (param.Type == ExecutionSQL.SqlType.SELECT) { throw new Exception("ExecuteSQL_InsUpdDelメソッドはSELECT構文以外のみが実行可能です。SqlTypeを確認してください。"); }
                            // ↑↑↑ チェック ↑↑↑

                            cmd.CommandText = param.ExecuteSQL;

                            // SQLパラメータを設定
                            cmd.Parameters.Clear();
                            foreach (var item in param.Parameters) { cmd.Parameters.Add(new SQLiteParameter(item.Name, item.Value)); }

                            cmd.ExecuteNonQuery();

                        }

                        trans.Commit();
                    }

                }

            }
        }
        catch (Exception)
        {

            throw;
        }
    }

    #endregion

    #region Private Method 1st.

    /// <summary>
    /// DDL構文（Create、Drop）系列のSQLを実行する
    /// </summary>
    /// <param name="strFilePath">ファイルパス</param>
    /// <param name="strSQL">実行SQL</param>
    private void ExecuteSQL_DDL(string strFilePath, string strSQL)
    {
        var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = strFilePath };

        using (var cn = new SQLiteConnection(sqlConnectionSb.ToString()))
        {
            cn.Open();
            using (var cmd = new SQLiteCommand(cn))
            {
                cmd.CommandText = strSQL;
                cmd.ExecuteNonQuery();
            }
        }
    }

    #region Private Method 2st.

    /// <summary>
    /// テーブル作成処理（バージョン01.00）
    /// </summary>
    /// <returns></returns>
    private string _GetCreateTableSQL_V0100()
    {
        string strSQL = "";
        strSQL = " create table T_File ( "
                + "   intProjectNo integer "
                + "   , intCardNo integer"
                + "   , txtCommentNo TEXT "
                + "   , intFileNo integer"
                + "   , bloFile BLOB "
                + "   , constraint T_File_PKC primary key (intProjectNo,intCardNo,txtCommentNo,intFileNo) "
                + " ); "
                + "  "
                + " create table T_FileInfo ( "
                + "   intProjectNo integer"
                + "   , intCardNo integer"
                + "   , txtCommentNo TEXT "
                + "   , intFileNo integer"
                + "   , txtFileName TEXT "
                + "   , constraint T_FileInfo_PKC primary key (intProjectNo,intCardNo,txtCommentNo,intFileNo) "
                + " ); ";
        return strSQL;
    }

    #endregion

    #endregion

}

↑↑↑↑↑ サンプルソース ↑↑↑↑↑