SQLite続き



SQLite速度確認

これの続き
まあ多少マシだが、全面的に採用可能かと言うと描画速度確認してからかな。
意外とArcGISてかCOM経由で速ければそれはそれで良しかと。
現状だと特殊用途以外は厳しそう (DLL配布範囲次第では色々できそうだが)

テスト1は、OpenとDLL読みこみ入ったままなので、およそ単体では270msくらいか?
FGDBもOpenFeatureClass等しているので、まあそれ考えると今ひとつ速度はでなさそう?

属性結合や空間条件含みの結合においてはSQLiteの方が可読性の高いものとなりそうだが。
DBMSに移しても同様の手法が使用可能なこと考える1コスト 50ms程度でレコード数等でリニアに増えなきゃ微差だし
ただ、描画に関しては細かい性能差は気になる。

条件なし:472.0689 ミリ秒
165.0244 ミリ秒
151.0217 ミリ秒@READER
117.0142 ミリ秒
84.0149 ミリ秒@READER
124.0183 ミリ秒
117.0142 ミリ秒@READER
135.0208 ミリ秒
101.0105 ミリ秒@READER
112.0213 ミリ秒
91.0347 ミリ秒@READER
88.9903 ミリ秒
111.0173 ミリ秒@READER
73.0092 ミリ秒
58.0084 ミリ秒@READER
92.0124 ミリ秒
87.0133 ミリ秒@READER
97.0139 ミリ秒
95.0314 ミリ秒@READER
76.9988 ミリ秒
70.0059 ミリ秒@READER
60.0076 ミリ秒
60.0082 ミリ秒@READER
65.01 ミリ秒
68.0136 ミリ秒@READER
77.0074 ミリ秒
96.0156 ミリ秒@READER
85.0107 ミリ秒
116.0177 ミリ秒@READER
99.0148 ミリ秒
112.0127 ミリ秒@READER
100.0181 ミリ秒
67.0086 ミリ秒@READER
66.0062 ミリ秒
101.0191 ミリ秒@READER
137.0334 ミリ秒
149.0108 ミリ秒@READER
140.0178 ミリ秒
162.0239 ミリ秒@READER

コードは下記

呼び出し側

static void Main(string[] args)
{
  string test = "130.945242511000060,31.955611141000077|130.781871140000020,33.235353548000035|132.470041976000060,34.596781641000064|133.041841775000080,33.562096290000056|134.130984249000110,33.970524718000036|136.036983579000090,35.032438631000048|137.017211806000090,36.284952476000058|139.358868126000060,35.930981172000031|137.942982910000070,35.440867059000027|140.039582173000100,37.782523379000054|140.883667591000060,39.770208395000054|141.754981570000040,39.715751271000045|142.136181436000020,43.446064246000049|143.334238158000060,43.772806988000070|143.089181101000010,42.847035885000025|139.413325250000070,37.292409265000060|140.202953544000020,36.393866724000077|134.621098363000100,35.304724249000060|133.314127393000040,34.678467327000078";

  const string path = @"C:\Data\ST_GEOM.sqlite";

  DateTime now = DateTime.Now;

  using (SqliteTest tester = new SqliteTest(path))
  {
    tester.Test1();
    //SqliteTest.Test1(path);

    string msg = string.Format("条件なし:{0} ミリ秒", (DateTime.Now - now).TotalMilliseconds);
    Console.WriteLine(msg);

    string[] pts = test.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
    foreach (string ptXY in pts)
    {
      string[] pt = ptXY.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
      if (pt.Length != 2)
        continue;

      double x = double.Parse(pt[0]);
      double y = double.Parse(pt[1]);

      now = DateTime.Now;
      tester.Test2(x, y, x + 3, y + 3);
      //SqliteTest.Test2(path, x, y, x + 3, y + 3);

      msg = string.Format("{1},{2}       {0} ミリ秒", (DateTime.Now - now).TotalMilliseconds, x, y);
      Console.WriteLine(msg);

      now = DateTime.Now;
      tester.Test3(x, y, x + 3, y + 3);
      msg = string.Format("{1},{2}       {0} ミリ秒@READER", (DateTime.Now - now).TotalMilliseconds, x, y);
      Console.WriteLine(msg);

    }//end loop
  }

とりあえず、開いたまま用の適当実装

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

public class SqliteTest : IDisposable
{
  /// <summary>
  /// クラス持ちの接続
  /// </summary>
  private SQLiteConnection m_Con = null;

  /// <summary>
  /// コマンド
  /// </summary>
  private SQLiteCommand m_Cmd = null;

  /// <summary>
  /// アダプタ
  /// </summary>
  private SQLiteDataAdapter m_Adapter = null;

  /// <summary>
  /// コンストラクタ
  /// </summary>
  public SqliteTest(string filePath)
  {

    this.m_Con = new System.Data.SQLite.SQLiteConnection("Data Source=" + filePath);
    this.m_Con.Open();

    this.m_Cmd = this.m_Con.CreateCommand();

    SQLiteCommand cmd = this.m_Cmd;
    const string EXT_QUERY = "SELECT load_extension('stgeometry_sqlite.dll','SDE_SQL_funcs_init');";
    cmd.CommandText = EXT_QUERY;
    cmd.ExecuteNonQuery();

    SQLiteDataAdapter dataAdapter = new System.Data.SQLite.SQLiteDataAdapter();
    this.m_Adapter = dataAdapter;

  }//end method

  public void Test1()
  {
    SQLiteConnection sqlCon = this.m_Con;
    SQLiteCommand cmd = this.m_Cmd;
    SQLiteDataAdapter dataAdapter = this.m_Adapter;

    using (DataSet ds = new System.Data.DataSet())
    {

      cmd.CommandText = "SELECT * FROM JPN71";      
      dataAdapter.SelectCommand = cmd;

      dataAdapter.Fill(ds);

      DataTable dt = ds.Tables[0];

      DataColumn oidCol = dt.Columns["OBJECTID"];

      //using (dataAdapter)
      using (dt)
      {
        bool isFirstTime = true;
        foreach (DataRow row in dt.Rows)
        {
          if (isFirstTime)
          {
            isFirstTime = false;
            Console.Write(Environment.NewLine + row[oidCol].ToString());
          }
          else
            Console.Write(",{0}", row[oidCol]);

        }//end row

        Console.Write(Environment.NewLine);
      }//using dt

    }//end connection
  }//end method

  public void Test2(double minX, double minY, double maxX, double maxY)
  {
    SQLiteConnection sqlCon = this.m_Con;
    SQLiteCommand cmd = this.m_Cmd;
    SQLiteDataAdapter dataAdapter = this.m_Adapter;

    //sde.st_geomfromtext('polygon ((10.02 20.01, 11.92 35.64, 25.02 34.15,19.15 33.94, 10.02 20.01))', 4326)
    string rectGeom = string.Format("st_geomfromtext('polygon (({0} {1}, {2} {3}, {4} {5}, {6} {7}, {0} {1}))',  300001)",
      minX, minY,
      minX, maxY,
      maxX, maxY,
      maxX, minY);

    using (DataSet ds = new System.Data.DataSet())
    {
      cmd.CommandText = string.Format(@"
SELECT
 OBJECTID

FROM
 JPN71 TAB
WHERE
 st_intersects({0},  TAB.Shape) = 1", rectGeom);

      dataAdapter.SelectCommand = cmd;

      dataAdapter.Fill(ds);
      DataTable dt = ds.Tables[0];

      DataColumn oidCol = dt.Columns["OBJECTID"];

      //using (dataAdapter)
      using (dt)
      {
        bool isFirstTime = true;
        foreach (DataRow row in dt.Rows)
        {
          if (isFirstTime)
          {
            isFirstTime = false;
            Console.Write(Environment.NewLine + row[oidCol].ToString());
          }
          else
            Console.Write(",{0}", row[oidCol]);

        }//end row

        Console.Write(Environment.NewLine);
      }//using dt

    }//end connection

  }//end method

  public void Test3(double minX, double minY, double maxX, double maxY)
  {
    SQLiteConnection sqlCon = this.m_Con;
    SQLiteCommand cmd = this.m_Cmd;

    string rectGeom = string.Format("st_geomfromtext('polygon (({0} {1}, {2} {3}, {4} {5}, {6} {7}, {0} {1}))',  300001)",
      minX, minY,
      minX, maxY,
      maxX, maxY,
      maxX, minY);

    cmd.CommandText = string.Format(@"
SELECT
 OBJECTID

FROM
 JPN71 TAB
WHERE
 st_intersects({0},  TAB.Shape) = 1", rectGeom);

    using (SQLiteDataReader reader = cmd.ExecuteReader())
    {
      bool isFirstTime = true;

      while (reader.Read())
      {
        if (isFirstTime)
        {
          isFirstTime = false;
          Console.Write(Environment.NewLine + reader[0].ToString());
        }
        else
          Console.Write(",{0}", reader[0]);
      }
      Console.Write(Environment.NewLine);
    }

  }

  public void Dispose()
  {
    if (this.m_Adapter != null)
      this.m_Adapter.Dispose();
    this.m_Adapter = null;

    if (this.m_Cmd != null)
      this.m_Cmd.Dispose();
    this.m_Cmd = null;

    if (this.m_Con != null)
    {
      this.m_Con.Close();
      this.m_Con.Dispose();
    }
    this.m_Con = null;

  }//end method

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