SqlDataReaderとSqlDataAdapter


SqlDataReaderの方が速いと言うので確認

271.0394ms ON Fill
198.2328ms ON Reader
12.9768ms ON Reader NOT GEOM
13.0072ms ON Fill NOT GEOM

254.0234ms ON Fill
168.0223ms ON Reader
15.0071ms ON Reader NOT GEOM
23.994ms ON Fill NOT GEOM

差が出ても8ミリ秒 / 2000件 ジオメトリなし
70-80ミリ秒 / 2000件 ジオメトリあり

1万件あたりでも1秒程度か、そこまで気にする必要はないか。
特に幾何情報やバイナリ含みでもなければ差がほとんどなさそう。

描画とか回数が多い場合は、100回で1分弱とかなので適用してもよいか。
マルチスレッド描画
コネクション保持したりなんだかんだで500ms/回程度にはなるだろう(レコード数依存だが)

以下コード

とりあえず、幾何情報含みと含まずで比較

    public static void TEST()
    {
      string connectionSt = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultDB"].ConnectionString;
      SqlConnection con = new SqlConnection(connectionSt);
      con.Open();

      string query = @"
SELECT 
 Shape AS SHP
FROM 
 JPN71WEB";

      List<SqlGeometry> lstGeom = new List<SqlGeometry>();

      using (con)
      using (SqlCommand cmd = con.CreateCommand())
      {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = query;

        DateTime dt = DateTime.Now;

        using (DataTable tab = new DataTable())
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
          adapter.Fill(tab);
          DataColumn shpCol = tab.Columns["SHP"];
          foreach (DataRow row in tab.Rows)
          {
            SqlGeometry geom = row[shpCol] as
            SqlGeometry;
            lstGeom.Add(geom);
          }

        }//end table

        Console.WriteLine((DateTime.Now - dt).TotalMilliseconds + "ms ON Fill");

        lstGeom.Clear();
        DateTime time = DateTime.Now;

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
          if (reader.HasRows)
          {
            while (reader.Read())
            {
              SqlGeometry geom = reader[0] as
              SqlGeometry;
              lstGeom.Add(geom);
            }//end loop
          }//end has rows
        }//end reader

        Console.WriteLine((DateTime.Now - time).TotalMilliseconds + "ms ON Reader");

      }//end sql con

    }//end method

    private struct DataStore
    {
      public int OBJECTID;
      public string SIKUCHOSON;
      public string CITY_ENG;
      public int P_NUM;
      public int H_NUM;
    }//end struct

    public static void TEST2()
    {
      string connectionSt = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultDB"].ConnectionString;
      SqlConnection con = new SqlConnection(connectionSt);
      con.Open();

      string query = @"
SELECT
 OBJECTID,
 SIKUCHOSON,
 CITY_ENG,
 P_NUM,
 H_NUM
FROM
 JPN71WEB";

      List<DataStore> lstData = new List<DataStore>();

      using (con)
      using (SqlCommand cmd = con.CreateCommand())
      {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = query;

        DateTime time = DateTime.Now;

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
          if (reader.HasRows)
          {
            while (reader.Read())
            {
              DataStore ds;
              ds.OBJECTID = reader.GetInt32(0);
              ds.SIKUCHOSON = reader.GetString(1);
              ds.CITY_ENG = reader.GetString(2);
              ds.P_NUM = reader.GetInt32(3);
              ds.H_NUM = reader.GetInt32(4);

              lstData.Add(ds);
            }//end loop
          }//end has rows
        }//end reader

        Console.WriteLine((DateTime.Now - time).TotalMilliseconds + "ms ON Reader NOT GEOM");
        lstData.Clear();

        DateTime dt = DateTime.Now;

        using (DataTable tab = new DataTable())
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
          adapter.Fill(tab);
          DataColumn objCol = tab.Columns["OBJECTID"];
          DataColumn sikuCol = tab.Columns["SIKUCHOSON"];
          DataColumn cityEngCol = tab.Columns["CITY_ENG"];
          DataColumn pnumCol = tab.Columns["P_NUM"];
          DataColumn hnumCol = tab.Columns["H_NUM"];

          foreach (DataRow row in tab.Rows)
          {
            DataStore ds;
            ds.OBJECTID = (int)row[objCol];
            ds.SIKUCHOSON = (string)row[sikuCol];
            ds.CITY_ENG = (string)row[cityEngCol];
            ds.P_NUM = (int)row[pnumCol];
            ds.H_NUM = (int)row[hnumCol];

            lstData.Add(ds);
          }//end row
        }//end table

        Console.WriteLine((DateTime.Now - dt).TotalMilliseconds + "ms ON Fill NOT GEOM");
        lstData.Clear();

      }//end sql con

    }//end method
カテゴリー: 開発 タグ: , パーマリンク