SQL Server Updateの高速化を試みる


全てのレコード書き換え等、バッチ的に既存テーブルの更新をしたい時
  A:クエリを連打する ← 遅いに決まっている、通信とかプロセス関係を理解していない場合こういう結論しかでない。

BulkInsertで一時テーブル経由に更新すると速い。
全部一時テーブルだが、更新したいテーブルは実体で、本当は一時テーブルに更新対象をあげる形。

サンプル.

50000件 : 14546.7884ミリ秒 ( Update連打 )
50000件 : 1383.2045ミリ秒 (一時テーブル経由)

Update連打 コード

var con = new SqlConnection("Data Source=ほすと;Initial Catalog=でーたべーす;User ID=ゆーざ;Password=ぱすわーど;");
con.Open();

string createQuery = "CREATE TABLE ##TEST_DST ( CID INT PRIMARY KEY, PSS NVARCHAR(10) )";
string selectQuery = "SELECT * FROM ##TEST_DST";
string upQuery = "UPDATE ##TEST_DST SET PSS=@P1 WHERE CID=@P2";

using (con)
using (SqlCommand cmd = con.CreateCommand())
using (SqlDataAdapter adapter = new SqlDataAdapter())
using (DataTable schemaTab = new DataTable())
using (DataTable dt = new DataTable())
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
{
  cmd.CommandText = createQuery;
  cmd.CommandType = CommandType.Text;
  cmd.ExecuteNonQuery();

  cmd.CommandText = selectQuery;
  adapter.SelectCommand = cmd;

  adapter.FillSchema(schemaTab, SchemaType.Mapped);

  for (int i = 0; i < 50000; i++)
  {
    var nrow = schemaTab.NewRow();
    nrow[0] = i;
    schemaTab.Rows.Add(nrow);
  }//end loop

  bulkCopy.DestinationTableName = "##TEST_DST";
  bulkCopy.WriteToServer(schemaTab);
  //↑前処理として

  var now = DateTime.Now;

  int cnt = 0;
  using (SqlCommand upCmd = con.CreateCommand())
  {
    upCmd.CommandText = upQuery;
    upCmd.CommandType = CommandType.Text;

    SqlParameter idParam = new SqlParameter();
    idParam.ParameterName = "@P2";
    idParam.DbType = DbType.Int32;

    SqlParameter pssParam = new SqlParameter();
    pssParam.ParameterName = "@P1";
    pssParam.DbType = DbType.String;

    upCmd.Parameters.Add(idParam);
    upCmd.Parameters.Add(pssParam);

    for (int i = 0; i < 50000; i++)
    {
      idParam.Value = i;

      string pass = System.Web.Security.Membership.GeneratePassword(8, 0);
      pssParam.Value = pass;

      cnt += upCmd.ExecuteNonQuery();
    }
  }
  Console.WriteLine("{0}件 : {1}ミリ秒", cnt, (DateTime.Now - now).TotalMilliseconds);

}//end sql

一時テーブル経由

var con = new SqlConnection("Data Source=ほすと;Initial Catalog=でーたべーす;User ID=ゆーざ;Password=ぱすわーど;");
con.Open();

string createQuery1 = "CREATE TABLE ##TEST_SRC ( CID INT PRIMARY KEY, PSS NVARCHAR(10) )";
string createQuery2 = "CREATE TABLE ##TEST_DST ( CID INT PRIMARY KEY, PSS NVARCHAR(10) )";
//string selectQuery1 = "SELECT * FROM ##TEST_SRC";
string selectQuery = "SELECT * FROM ##TEST_DST";
string upQuery =
@"
UPDATE
 ##TEST_DST
SET
 PSS=SRC.PSS
FROM
 ##TEST_DST DST
INNER JOIN
 ##TEST_SRC SRC
ON
 DST.CID = SRC.CID";

using (con)
using (SqlCommand cmd = con.CreateCommand())
using (SqlDataAdapter adapter = new SqlDataAdapter())
using (DataTable schemaTab = new DataTable())
using (DataTable dt = new DataTable())
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
{
  cmd.CommandText = createQuery1;
  cmd.CommandType = CommandType.Text;
  cmd.ExecuteNonQuery();

  cmd.CommandText = createQuery2;
  cmd.ExecuteNonQuery();

  cmd.CommandText = selectQuery;
  adapter.SelectCommand = cmd;

  adapter.FillSchema(schemaTab, SchemaType.Mapped);

  for (int i = 0; i < 50000; i++)
  {
    var nrow = schemaTab.NewRow();
    nrow[0] = i;
    schemaTab.Rows.Add(nrow);
  }//end loop

  bulkCopy.DestinationTableName = "##TEST_DST";
  bulkCopy.WriteToServer(schemaTab);
  //↑前処理として

  var now = DateTime.Now;

  //テーブルとるところからやり直し
  cmd.CommandText = selectQuery;
  adapter.SelectCommand = cmd;

  adapter.FillSchema(dt, SchemaType.Mapped);

  for (int i = 0; i < 50000; i++)
  {
    var nrow = dt.NewRow();
    nrow[0] = i;
    string pass = System.Web.Security.Membership.GeneratePassword(8, 0);
    nrow[1] = pass;

    dt.Rows.Add(nrow);
  }//end loop

  bulkCopy.DestinationTableName = "##TEST_SRC";
  bulkCopy.WriteToServer(dt);

  int cnt = 0;
  using (SqlCommand upCmd = con.CreateCommand())
  {
    upCmd.CommandText = upQuery;
    upCmd.CommandType = CommandType.Text;

    cnt = upCmd.ExecuteNonQuery();
  }
  Console.WriteLine("{0}件 : {1}ミリ秒", cnt, (DateTime.Now - now).TotalMilliseconds);

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