[SQL]SQLServerのDB内に存際する全テーブルの件数取得(T-SQL) (その2)

GDD Blog: [SQL]SQLServerのDB内に存際する全テーブルの件数取得(T-SQL)では真っ当？に全テーブルの件数を取する。をやってみましたが、今度、sp_MSforeachtableという便利な関数？を見つけましたので、こちらで応用をやってみました。コードはこんな感じ。

--一時変数(結果格納用)
CREATE TABLE #TEMP_TABLE(
     T_NAME varchar(128)
    ,T_CNT int
)

--sp_MSforeachtableでユーザテーブル全部をループ処理
EXEC sp_MSforeachtable  @command1 ='
    INSERT INTO #TEMP_TABLE 
    SELECT 
        ''?'' as T_NAME
       ,COUNT(*) as T_CNT 
    FROM ?
'

--実行結果を表示する
SELECT * FROM #TEMP_TABLE ORDER BY T_NAME
DROP TABLE #TEMP_TABLE

?の部分がテーブル名に置き換わるので、SELECTの項目に入れており、かつ、FROMのテーブル名になるようにしてみました。

尚、このての関数でSP_MSForEachDBというのあります。関数名からもわかるとおり、こちらはテーブルではなく全DBをループして処理するようです。

[.NET]CSVデータを操作する(OleDb)(その2)

GDD Blog: [.NET]CSVデータを操作する(OleDb)では、OleDbConnectionを使ったCSV操作を行いましたが、今度はOdbcConnectionを使ってCSVの操作を行ってみました。コードはこんな感じ。

OleDbConnectionも同じかもしれませんが、1行目のデータにコーテーションがあるか無いかで型を決めている節があります。GDD Blog: [.NET]CSVデータを操作する(OleDb)のときとは微妙にデータの出具合が違う様です。
Select時に型の情報を見てみると、int32,String,DateTimeという順で並んでいました。

細かな型指定をしたい場合は、同フォルダにSchema.iniを置くとそれを参考にするらしい。こんど少し調べてみようと思います。

private void button1_Click(object sender, EventArgs e)
{
    //using System.Data.Odbc;
    string csvDir = @"d:\temp";
    string csvFileName = "test.csv";

    //ODBC接続文字列を組み立てる
    OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
    builder["Driver"] = "Microsoft Text Driver (*.txt; *.csv)";
    builder["dbq"] = csvDir;
    builder["Extension"] = "asc,csv,tab,txt";

    string strSQL = "";
    int count = 0;

    using (OdbcConnection con = new OdbcConnection(builder.ConnectionString))
    {
        con.Open();

        strSQL = "SELECT count(*) FROM " + csvFileName;
        using (OdbcCommand cmd = new OdbcCommand(strSQL, con))
        {
            //スカラー値を取得します
            object obj = cmd.ExecuteScalar();
            count = (int)obj;

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("--------------------------------------");
            sb.Append("実行結果 [").Append(obj).Append("]\r\n");
            System.Diagnostics.Debug.WriteLine(sb.ToString());

        }

        strSQL = "SELECT MAX(idx) FROM " + csvFileName;
        using (OdbcCommand cmd = new OdbcCommand(strSQL, con))
        {
            //スカラー値を取得します
            object obj = cmd.ExecuteScalar();

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("--------------------------------------");
            sb.Append("実行結果 [").Append(obj).Append("]\r\n");
            System.Diagnostics.Debug.WriteLine(sb.ToString());

        }

        strSQL = "INSERT INTO " + csvFileName + " values(" + count++ + ", 'abc', '" + DateTime.Now.ToString() + "')";
        using (OdbcCommand cmd = new OdbcCommand(strSQL, con))
        {

            //更新処理を実行します
            int result = cmd.ExecuteNonQuery();

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("--------------------------------------");
            sb.Append("登録件数 [").Append(result).Append("]\r\n");
            System.Diagnostics.Debug.WriteLine(sb.ToString());

        }

        //if (count > 0)
        //{
        //    strSQL = "DELETE " + csvFileName + " WHERE idx='" + count + "'";
        //    using (OdbcCommand cmd = new OdbcCommand(strSQL, con))
        //    {
        //        //更新処理を実行します
        //        int result = cmd.ExecuteNonQuery();

        //        StringBuilder sb = new StringBuilder();
        //        sb.AppendLine("--------------------------------------");
        //        sb.Append("更新件数 [").Append(result).Append("]\r\n");
        //        System.Diagnostics.Debug.WriteLine(sb.ToString());
        //    }
        //    //strSQL = "UPDATE " + csvFileName + " SET text1 = 'def' WHERE idx='" + count + "'";
        //    strSQL = "UPDATE " + csvFileName + " SET text1 = 'def'";
        //    using (OdbcCommand cmd = new OdbcCommand(strSQL, con))
        //    {
        //        //更新処理を実行します
        //        int result = cmd.ExecuteNonQuery();

        //        StringBuilder sb = new StringBuilder();
        //        sb.AppendLine("--------------------------------------");
        //        sb.Append("更新件数 [").Append(result).Append("]\r\n");
        //        System.Diagnostics.Debug.WriteLine(sb.ToString());
        //    }
        //}

        strSQL = "SELECT * FROM " + csvFileName; //+ " WHERE idx<3";
        using (OdbcCommand cmd = new OdbcCommand(strSQL, con))
        using (OdbcDataReader rd = cmd.ExecuteReader())
        {
            PrintData(rd);
        }

    }

}

private void PrintData(OdbcDataReader rd)
{
    StringBuilder sb = new StringBuilder();
    //カラム名を取得し、カンマ区切りで編集します
    sb.AppendLine("--------------------------------------");
    for (int cnt = 0; cnt < rd.FieldCount; cnt++)
    {
        sb.Append(rd.GetName(cnt)).Append(",");
    }
    sb.Remove(sb.Length - 1, 1);
    sb.AppendLine("");
    sb.AppendLine("--------------------------------------");

    //データを取得しカンマ区切りで編集します
    for (int row = 0; rd.Read(); row++)
    {
        for (int col = 0; col < rd.FieldCount; col++)
        {
            sb.Append(rd.GetValue(col)).Append(",");
        }
        sb.Remove(sb.Length - 1, 1);
        sb.AppendLine("");
    }
    sb.AppendLine("--------------------------------------");

    System.Diagnostics.Debug.WriteLine(sb.ToString());

}

■実行結果

--------------------------------------
実行結果 [6]

--------------------------------------
実行結果 [5]

--------------------------------------
登録件数 [1]

--------------------------------------
idx,text1,text2
--------------------------------------
0,abc,2009/09/06 22:44:44
1,abc,2009/09/06 22:49:58
2,abc,2009/09/06 22:49:58
3,abc,2009/09/06 22:50:00
4,abc,2009/09/06 22:50:00
5,abc,2009/09/06 22:50:01
6,abc,2009/09/06 22:50:06
--------------------------------------



■データファイル

idx,text1,text2
0,"abc",2009/09/06 22:44:44
1,"abc",2009/09/06 22:49:58
2,"abc",2009/09/06 22:49:58
3,"abc",2009/09/06 22:50:00
4,"abc",2009/09/06 22:50:00
5,"abc",2009/09/06 22:50:01
6,"abc",2009/09/06 22:50:06


[.NET]ADO.NET 2.0 基本クラスおよびファクトリによる汎用的なコーディング

ADO.NET 2.0 基本クラスおよびファクトリによる汎用的なコーディングにしたがって、ファクトリメソッドを使ったDB接続(今回もCSVファイル)をやってみました。

DbProviderFactories.GetFactoryの引数で渡す情報はmachine.configに定義されており、私の環境では、

• Odbc Data Provider(System.Data.Odbc)
• OleDb Data Provider(System.Data.OleDb)
• OracleClient Data Provider(System.Data.OracleClient)
• SqlClient Data Provider(System.Data.SqlClient)
• Microsoft SQL Server Compact Data Provider(System.Data.SqlServerCe.3.5)

が定義されていました(括弧内がファクトリに渡す文字列)。コードはこんな感じ。

private void button1_Click(object sender, EventArgs e)
{
    //using System.Data.Common;

    string csvDir = @"d:\temp";
    string csvFileName = "test.csv";

    //接続文字列を組み立てる
    DbProviderFactory f = DbProviderFactories.GetFactory("System.Data.Odbc");
    DbConnectionStringBuilder builder = f.CreateConnectionStringBuilder();
    builder["Driver"] = "Microsoft Text Driver (*.txt; *.csv)";
    builder["dbq"] = csvDir;
    builder["Extension"] = "asc,csv,tab,txt";

    using (IDbConnection con = f.CreateConnection())
    {
        con.ConnectionString = builder.ConnectionString;
        con.Open();

        string strSQL = "SELECT * FROM " + csvFileName + " WHERE idx < 3";
        using (IDbCommand cmd = f.CreateCommand())
        {
            cmd.CommandText = strSQL;
            cmd.Connection = con;
            using (IDataReader rd = cmd.ExecuteReader())
            {
                PrintData(rd);
            }
        }

    }

}

private void PrintData(IDataReader rd)
{
    StringBuilder sb = new StringBuilder();
    //カラム名を取得し、カンマ区切りで編集します
    sb.AppendLine("--------------------------------------");
    for (int cnt = 0; cnt < rd.FieldCount; cnt++)
    {
        sb.Append(rd.GetName(cnt)).Append(",");
    }
    sb.Remove(sb.Length - 1, 1);
    sb.AppendLine("");
    sb.AppendLine("--------------------------------------");

    //データを取得しカンマ区切りで編集します
    for (int row = 0; rd.Read(); row++)
    {
        for (int col = 0; col < rd.FieldCount; col++)
        {
            sb.Append(rd.GetValue(col)).Append(",");
        }
        sb.Remove(sb.Length - 1, 1);
        sb.AppendLine("");
    }
    sb.AppendLine("--------------------------------------");

    System.Diagnostics.Debug.WriteLine(sb.ToString());

}
}

システム的には汎用性が高い。といえそうですが、実際にお客さんの要望として、複数のDBMSに対応した。。。というのは少ないような気がします。フレームワーク的なものであれば別ですが。

まぁとはいえ単体でユニットテストを組む場合などに応用できそうですね。

[.NET]OdbcConnectionStringBuilderを使ってみた

先日、調べ事をしていると、偶然DB接続文字列を組み立てるクラスを発見しましたので、ちょっと試しに使ってみました。たとえば、CSVファイルに接続するならこんな感じ。

//ODBC接続文字列を組み立てる
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
builder["Driver"] = "Microsoft Text Driver (*.txt; *.csv)";
builder["dbq"] = csvDir;
builder["Extension"] = "asc,csv,tab,txt";

using (OdbcConnection con = new OdbcConnection(builder.ConnectionString)){
    //SQLの実行
}

ほかにもSQLConnectionStringBuilder(SQLServer用)や抽象クラスDbConnectionStringBuilderが存在します。

[SQL]SQLServer のエラー一覧(その2)

GDD Blog: [SQL]SQLServer のエラー一覧では、SQLServer自体に登録されているメッセージを取得する方法を紹介しましたが、MSのサイトにもっといいものがありましたので紹介します。

SQL Server 2008 オンライン ブック (2009 年 5 月)システム エラー メッセージ

むしろこちらのほうが詳しい。

[.NET]CSVデータを操作する(OleDb)(その3 SQLでファイルへ出力)

[.NET]CSVデータを操作する(OleDb)(その2 ファイルへ出力)では、FileのIOでソート結果をファイルに出力してみましたが、コレをOleDBを使ったSQLで実行してみました。
コードはこんな感じ。

private void button1_Click(object sender, EventArgs e)
{
    //using System.Data.OleDb;
    string csvDir = @"d:\temp";
    string csvFileName = "KEN_ALL.CSV";
    string csvOutFileName = "KEN_ALL_OUT.CSV";

    //接続文字列    
    string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csvDir +
                       ";Extended Properties=\"text;HDR=No;FMT=Delimited\"";
    //OleDBでプレースホルダを使う場合はHDRが必須らしい
    string conOutString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csvDir +
                       ";Extended Properties=\"text;HDR=Yes;FMT=Delimited\"";

    //ファイルがあったら初期化
    using (StreamWriter w = new StreamWriter(new FileStream(csvDir + @"\" + csvOutFileName, FileMode.Create))) 
    {
        w.WriteLine("F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15");
    }

    System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
    sw.Start();


    using (OleDbConnection con = new OleDbConnection(conString))
    using (OleDbConnection conOut = new OleDbConnection(conOutString))
    {
        con.Open();
        conOut.Open();

        // HDR=Noだと、Fxというフィールド名になるらしい。↓は3つめのフィールド名でソート
        string strSQL = "SELECT * FROM " + csvFileName + " ORDER BY F3";
        string strOutSQL = "INSERT INTO " + csvOutFileName + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        using (OleDbCommand cmd = new OleDbCommand(strSQL, con))
        using (OleDbDataReader rd = cmd.ExecuteReader())
        using (OleDbCommand cmdOut = new OleDbCommand(strOutSQL, conOut))
        {
            //カラムの属性から、SQLパラメータを作成する
            for (int col = 0; col < rd.FieldCount; col++)
            {
                cmdOut.Parameters.Add(new OleDbParameter(rd.GetName(col), rd.GetFieldType(col)));
            }

            //データを取得しカンマ区切りで編集します
            while (rd.Read())
            {
                for (int col = 0; col < rd.FieldCount; col++)
                {
                    cmdOut.Parameters[col].Value = rd.GetValue(col);
                }
                cmdOut.ExecuteNonQuery();
            }

        }
    }
    sw.Stop();
    label1.Text=  String.Format("完了{0:#,##0}ms", sw.ElapsedMilliseconds);
}
}

・・・実行して1時間ほど放置してみましたが終わりませんでした。で強制終了させてみると、2万件ほどデータが出力されていました。
しかも、すべてのフィールドがテキスト形式として保存されていました。型指定で手抜きしたせいだろうか。。。

パフォーマンス的な話で言えば、OleDBのCSVに対するINSERTは実用的ではありませんね。

[.NET]CSVデータを操作する(OleDb)(その2 ソートしてファイルへ出力)

CSVファイルのある列でソートしたい。といった場合、DBにいったん保存したり、何かしらコマンドを使ったり。方法はいろいろありますが、どれもしっくりきません。以前GDD Blog: [.NET]CSVデータを操作する(OleDb)で、単純にCSVファイルのCRUDを試しましたが、SELECTするときにORDER BYつけれないかなぁということでやってみました。

適当なデータが無かったので、郵便番号データ(全国版)をつかってちょっとした実験をしてみました。
このデータは12万件ありますが、郵便番号順に並んでいないようなので、これでソートしてみました。
コードはこんな感じ。

private void button1_Click(object sender, EventArgs e)
{
    //using System.Data.OleDb;
    string csvDir = @"d:\temp";
    string csvFileName = "KEN_ALL.CSV";

    //接続文字列 
    string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csvDir +
                       ";Extended Properties=\"text;HDR=No;FMT=Delimited\"";

    System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
    sw.Start();
   

    using (OleDbConnection con = new OleDbConnection(conString))
    {
        con.Open();

        // HDR=Noだと、Fxというフィールド名になるらしい。↓は3つめのフィールド名でソート
        string strSQL = "SELECT * FROM " + csvFileName + " ORDER BY F3";
        using (OleDbCommand cmd = new OleDbCommand(strSQL, con))
        using (OleDbDataReader rd = cmd.ExecuteReader())
        using (StreamWriter w = new StreamWriter(new FileStream(csvDir + @"\test.txt", FileMode.Create)))
        {
            StringBuilder sb = new StringBuilder();
            //カラム名を取得し、カンマ区切りで編集します
            for (int col = 0; col < rd.FieldCount; col++)
            {
                sb.Append(rd.GetName(col)).Append(",");
            }
            sb.Remove(sb.Length - 1, 1);
            w.WriteLine(sb);

             //データを取得しカンマ区切りで編集します
            while (rd.Read())
            {
                sb.Length = 0;
                for (int col = 0; col < rd.FieldCount; col++)
                {
                    sb.Append(rd.GetValue(col)).Append(",");
                }
                sb.Remove(sb.Length - 1, 1);
                w.WriteLine(sb);
            }
        }
    }
    sw.Stop();
    label1.Text=  String.Format("完了{0:#,##0}ms", sw.ElapsedMilliseconds);
}

パフォーマンスを測ってみました。結果こんな感じ(5回平均)。
ソートなし：4.21秒
ソートあり：10.09秒

・・・12万件を10秒とすると、1件あたり0.08msとなります。
一般的なDBと比較するとアレですが、テキストファイルで且、ファイルへの出力も含む。と考えた場合、以外といい数字のような気がします。

ちなみに、うちのマシンは1年半ほど前に購入したC2D 2GHzのノートPCです。

[SQL]実行計画の表示(SQLServer)

SQLServerでは、SQLServer Management Studio(クエリアナライザ)でSQLのコスト評価を行うことができます。実行計画の評価はGUIを使ったマップ画面で確認することできます。

しかし、複数のSQLのコスト評価を見るには適しません。ということで、クエリの実行結果として、実行結果を取得する方法を紹介します。

--実行プランTEXT形式
SET SHOWPLAN_ALL ON


--実行プランXML形式
SET SHOWPLAN_XML ON

XML形式で出力すると、後にGUIで表示することもできます。