Insertの高速化を試みる


[訂正]
普通のArcのコードでShapeCopyプロパを受け取るべきところをShapeにしていたので訂正した。
速度は変化なし。

ArcGISのデータ追記を早くできないかとSqlBulkCopyを試みたもののあまり早くならなかったので記録。
ID行を外すと多少早くなる。

ArcGISで扱っているデータ投入が遅く夜間バッチが回りそうにないらしいので速度向上を模索。
(そもそも件数が想定外なのでデータフロー見直した方が良さそうだが)
ジオメトリ含まないデータ投入時は絶大な威力を発揮することもあるSqlBulkCopyで比較してみる。

ちなみ直接関係ないがSqlBulkCopyだけならばArcView(今はBasic) かArcGIS EngineだけでもSQL Serverにデータ投入可能
ArcSDE管理下の場合は、ObjectIDのシーケンス等を気にする必要があるがとりあえず今回は無視
(Create Insertのみなら10.1からだったかArcCatalog(Basic)のみでSQL Serverへデータ投入は可能)

あとの手段はIFeatureDataConverterぐらいだが、別フローであまり芳しくないので、そもそも処理対象自体を見直しへ
(ArcGISのコピー系で.NETではFGDB系のコピーが速いがDBMSではあまり試してない、そもそも追記できたっけ)

テーブル:ID [int],SHAPE [geometry]
SQL Server 2012 / ArcGIS 10.0

以下、計測時間はミリ秒
削除含まないShapeFile読込⇒SQL Serverへの投入時間
(※コードは確認用なので一回投入したら削除している)

SqlBKCopy:4093.75 ESRI Cursor:3421.875
SqlBKCopy:3421.875 ESRI Cursor:3546.875
SqlBKCopy:3375 ESRI Cursor:3546.875

Fillは800ミリ秒程度
データを10倍増しにしただけの確認
SqlBKCopy:35122.0703 ESRI Cursor:23603.5156
SqlBKCopy:49686.5235 ESRI Cursor:36851.5625
SqlBKCopy:37991.2109 ESRI Cursor:24093.75

通常挿入との差は?等はあるがとりあえず一旦仮置きして今週は別件対応か。

自動付番のIDを抜いた場合
SqlBKCopy:3906.25 ESRI Cursor:3421.875
SqlBKCopy:3281.25 ESRI Cursor:3531.25
SqlBKCopy:3000 ESRI Cursor:4296.875

ID自分で管理すれば多少はなんとかなるか?

using System;

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Types;
using System.Diagnostics;

using System.Data.OleDb;
public static void Test()
{
    string shapefileStringTemplate =
        "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};" +
        "Extended Properties=workspacetype=esriDataSourcesFile.ShapefileWorkspaceFactory.1;Geometry={1}";
    string connectionString = string.Format(shapefileStringTemplate, @"C:\tmp", "WKB");

    OleDbConnection shapefileConn = new OleDbConnection(connectionString);
    shapefileConn.Open();
    using (shapefileConn)
    using (OleDbCommand selectCmd = shapefileConn.CreateCommand())
    using (DataTable shapeTable = new DataTable())
    {
        selectCmd.CommandText = "SELECT * FROM japan_ver71";
        selectCmd.CommandType = CommandType.Text;

        OleDbDataAdapter adapter = new OleDbDataAdapter(selectCmd);
        adapter.Fill(shapeTable);

        //foreach (DataRow row in shapeTable.Rows)
        //{
        //    Debug.Print("{0}",row[0]);
        //}//end loop
        TestInsert(shapeTable);
    }//end data

}//end method

private static void TestInsert(DataTable srcDt)
{
    SqlConnection con = new SqlConnection("Data Source=マシン名;Initial Catalog=DB名;User ID=ユーザ;Password=パス");
    con.Open();

    using (con)
    using (SqlTransaction tran = con.BeginTransaction())
    using (DataTable dataTable = new DataTable())
    using (SqlBulkCopy blkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, tran))
    {
        try
        {
            DataColumn shape = new DataColumn("SHAPE", typeof(SqlGeometry));
            dataTable.Columns.Add("ID", typeof(int));
            dataTable.Columns.Add(shape);

            DataColumn srcShape = srcDt.Columns["Shape"];

            foreach (DataRow row in srcDt.Rows)
            {
                DataRow newRow = dataTable.NewRow();

                byte[] wkb = (byte[])row[srcShape];

                newRow[shape] = SqlGeometry.STGeomFromWKB(new SqlBytes(wkb), 4612);
                dataTable.Rows.Add(newRow);
            }

            blkCopy.DestinationTableName = "JPN71TAB";
            blkCopy.WriteToServer(dataTable);

            tran.Commit();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);

            Debug.Print("{0}\n{1}", ex.Message, ex.StackTrace);

            tran.Rollback();
        }
    }//end data
}

public static void TestDelete()
{
    SqlConnection con = new SqlConnection("Data Source=マシン名
;Initial Catalog=DB名;User ID=ユーザ;Password=パス");
    con.Open();

    using (con)
    using (SqlTransaction tran = con.BeginTransaction())
    using (SqlCommand cmd = con.CreateCommand())
    {
        try
        {
            cmd.Transaction = tran;
            cmd.CommandText = "DELETE FROM JPN71TAB";
            cmd.CommandType = CommandType.Text;
            //
            cmd.ExecuteNonQuery();

            tran.Commit();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);

            Debug.Print("{0}\n{1}", ex.Message, ex.StackTrace);

            tran.Rollback();
        }
    }//end data
}

上記に対して普通にArcGISコード

using System;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.esriSystem;
using ESRI.ArcGIS.ADF;
using System.Diagnostics;
public static void Test()
{
    using (ComReleaser comObj = new ComReleaser())
    {
        Type shapeType = Type.GetTypeFromProgID("esriDataSourcesFile.ShapefileWorkspaceFactory");
        IWorkspaceFactory shapeFact = (IWorkspaceFactory)Activator.CreateInstance(shapeType);
        comObj.ManageLifetime(shapeFact);
        IFeatureWorkspace shapeFw = (IFeatureWorkspace)shapeFact.OpenFromFile(@"C:\tmp\", 0);
        comObj.ManageLifetime(shapeFw);
        IFeatureClass shapeFc = shapeFw.OpenFeatureClass("japan_ver71");
        comObj.ManageLifetime(shapeFc);

        IPropertySet propSets = new PropertySetClass();
        comObj.ManageLifetime(propSets);
        propSets.SetProperty("SERVER", "");
        propSets.SetProperty("INSTANCE", "sde:sqlserver:マシン名");
        propSets.SetProperty("DATABASE", "データベース");
        propSets.SetProperty("USER", "ユーザ");
        propSets.SetProperty("PASSWORD", "パス");
        propSets.SetProperty("VERSION", "dbo.DEFAULT");

        Type factoryType = Type.GetTypeFromProgID("esriDataSourcesGDB.SdeWorkspaceFactory");
        IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance(factoryType);
        comObj.ManageLifetime(workspaceFactory);
        IFeatureWorkspace fw = (IFeatureWorkspace)workspaceFactory.Open(propSets, 0);
        comObj.ManageLifetime(fw);
        IFeatureClass fc = fw.OpenFeatureClass("JPN71INS");
        comObj.ManageLifetime(fc);

        IFeatureCursor shapeCursor = shapeFc.Search(null, true);
        comObj.ManageLifetime(shapeCursor);

        IMultiuserWorkspaceEdit mue = (IMultiuserWorkspaceEdit)fw;
        mue.StartMultiuserEditing(esriMultiuserEditSessionMode.esriMESMNonVersioned);
        try
        {
            IFeature shapeFeature = null;
            IFeatureCursor insCursor = fc.Insert(true);
            comObj.ManageLifetime(insCursor);
            IFeatureBuffer fb = fc.CreateFeatureBuffer();
            comObj.ManageLifetime(fb);

            int loopCount = 0;
            while ((shapeFeature = shapeCursor.NextFeature()) != null)
            {
                loopCount++;
                fb.Shape = shapeFeature.ShapeCopy;
                insCursor.InsertFeature(fb);

                if (loopCount % 1000 == 0)
                    insCursor.Flush();

            }//end loop

            insCursor.Flush();

            ((IWorkspaceEdit)mue).StopEditing(true);
        }
        catch(Exception ex)
        {
            Console.WriteLine(ex.Message);

            Debug.Print("{0}\n{1}", ex.Message, ex.StackTrace);
            ((IWorkspaceEdit)mue).StopEditing(false);
        }//end try

    }//end com obj
}//end method

public static void TestDelete()
{
    using (ComReleaser comObj = new ComReleaser())
    {
        IPropertySet propSets = new PropertySetClass();
        comObj.ManageLifetime(propSets);
        propSets.SetProperty("SERVER", "");
        propSets.SetProperty("INSTANCE", "sde:sqlserver:マシン名");
        propSets.SetProperty("DATABASE", "データベース名");
        propSets.SetProperty("USER", "ユーザ");
        propSets.SetProperty("PASSWORD", "パス");
        propSets.SetProperty("VERSION", "dbo.DEFAULT");

        Type factoryType = Type.GetTypeFromProgID("esriDataSourcesGDB.SdeWorkspaceFactory");
        IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance(factoryType);
        comObj.ManageLifetime(workspaceFactory);
        IFeatureWorkspace fw = (IFeatureWorkspace)workspaceFactory.Open(propSets, 0);
        comObj.ManageLifetime(fw);
        IFeatureClass fc = fw.OpenFeatureClass("JPN71INS");
        comObj.ManageLifetime(fc);

        IMultiuserWorkspaceEdit mue = (IMultiuserWorkspaceEdit)fw;
        mue.StartMultiuserEditing(esriMultiuserEditSessionMode.esriMESMNonVersioned);
        try
        {
            IFeatureCursor upCursor = fc.Update(null, false);
            comObj.ManageLifetime(upCursor);

            while (upCursor.NextFeature() != null)
            {
                upCursor.DeleteFeature();
            }//end loop

            ((IWorkspaceEdit)mue).StopEditing(true);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);

            Debug.Print("{0}\n{1}", ex.Message, ex.StackTrace);
            ((IWorkspaceEdit)mue).StopEditing(false);
        }//end try
    }
}//end method
カテゴリー: 開発 タグ: , パーマリンク