NETはSQL性能最適化を実行します.SQL Server量産に対してSQL文を実行します.

9540 ワード
バッtch SQL 一括実行SQL ADO.NET パフォーマンス最適化
本論文では、Sql Commandを使ってSQL文を複数実行する方法をいくつか紹介します.
紹介する
ADO.NETを使用してSQL Serverに対してデータ記憶を常に無視する機能の一つは、SQL文Sql Commandを単一の文で実行することができることである.通常、プログラムは、ステートメントおよび／またはメモリプロセスをそれぞれ実行して、より大きなステートメントを実行します.もちろん、記憶プロセスを使用することは、優先的な方法であるが、場合によっては、一度に複数のステートメントを実行することが有益である.これはバッチ処理を使用して行われてもよく、これは基本的にSQLまたはT−SQLのセットを意味する.
設定
機能をテストするために、データベーステーブルを持っています.
  • 試験表
    • を作成します.
  • CREATE TABLE MultiStatementTest (
id        int not null identity(1,1),
somevalue int not null
);
    は、いくつかの行で満たされています.

  • いくつかの行を追加する
  • DECLARE @counter int = 1
BEGIN
WHILE (@counter <= 5) BEGIN
  INSERT INTO MultiStatementTest (somevalue) VALUES (RAND() * 1000);
  SET @counter = @counter + 1;
END;
END;
    今のデータは似ています.

  • クエリ初期データ
  • SELECT * FROM MultiStatementTest; id somevalue
    1 8542 733 7324 5465 267テストプログラムは、このテストプログラムが使いやすいです.テストテーブルを作成するデータベースのために正しい接続文字列を定義するだけで、テストを開始できます.
    • 複数のSQL文を実行します.
    最初の変数は、Sql Command.ExecuteNonQueryに対してテストテーブルに対して2つの個別のSQL文を実行するために使用されます.最初はフィールドをsomevalue一つ、第二の更新フィールドに更新します.この方法は以下の通りです.
    /// 
/// Executes two separate updates against the the connection
/// 
/// Connection string to use
/// Should the statement generate an error
/// True if succesful
public static bool ExecuteMultipleUpdates(string connectionString, bool generateError = false) {
 System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
 System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
 int rowsAffected;

 connection.ConnectionString = connectionString;
 command.CommandText = @"
     UPDATE MultiStatementTest SET somevalue = somevalue + 1;
     UPDATE MultiStatementTest SET" + (generateError ? "WONTWORK" : "") + 
                   " somevalue = somevalue + 2;";
 command.CommandType = System.Data.CommandType.Text;
 command.Connection = connection;

 try {
    connection.Open();
    rowsAffected = command.ExecuteNonQuery();
 } catch (System.Exception exception) {
    System.Windows.MessageBox.Show(exception.Message, "Error occurred");
    return false;
 } finally {
    command.Dispose();
    connection.Dispose();
 }
 System.Windows.MessageBox.Show(string.Format("{0} rows updated", 
    rowsAffected, "Operation succesful"));

 return true;
}
    したがって、このCommandText属性は、このバッチ処理で実行されるすべてのステートメントを含む.ステートメントはセミコロンで区切られます.
    バッチ処理を実行した後、行は二回更新されましたので、テーブルの内容は以下の通りです.
    id somevalue
    18727 76373545495270注意すべき重要なことは、戻りの影響を受ける行数ExecuteNonQueryが10であることです.表の中には5行があります.1行に2回更新したので、更新の総数は10です.そのため、ロットがあっても、どの語句を更新しても正しい行数が更新されることを確認することができます.
    Data Readerを使って二つのSELECT文を実行します.
    次のテストは二つの異なるSELECT文を実行し、Sql DataReaderクラスを使って結果を読み取ります.方法は:
    /// 
/// Executes two separate select statements against the the connection using data reader
/// 
/// Connection string to use
/// Should the statement generate an error
/// True if succesful
public static bool ExecuteReader(string connectionString, bool generateError = false) {
 System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
 System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
 System.Data.SqlClient.SqlDataReader dataReader;
 System.Text.StringBuilder stringBuilder;
 bool loopResult = true;

 connection.ConnectionString = connectionString;
 command = new System.Data.SqlClient.SqlCommand();
 command.CommandText = @"
    SELECT somevalue FROM MultiStatementTest WHERE somevalue%2 = 1;
    SELECT somevalue FROM MultiStatementTest " + (generateError ? "WONTWORK" : "WHERE") + 
               " somevalue%2 = 0;";
 command.CommandType = System.Data.CommandType.Text;
 command.Connection = connection;

 try {
    connection.Open();
    dataReader = command.ExecuteReader();
    while (loopResult) {
       stringBuilder = new System.Text.StringBuilder();
       while (dataReader.Read()) {
          stringBuilder.AppendLine(dataReader.GetInt32(0).ToString());
       }
       System.Windows.MessageBox.Show(stringBuilder.ToString(), "Data from the result set");
       loopResult = dataReader.NextResult();
    }
 } catch (System.Exception exception) {
    System.Windows.MessageBox.Show(exception.Message, "Error occurred");
    return false;
 } finally {
    command.Dispose();
    connection.Dispose();
 }

 return true;
}
    バッチ処理の考えは同じで、二つの文の間はセミコロンで区切られます.この例では、行を2つの結果セットに分け、特に数字が奇数か偶数かに依存する.ExecuteReaderが起動されると、最初の結果セットは自動的に利用可能です.この方法は各行を巡回して結果を表示します.
    857735549次の結果を得るためには、NextResult方法を使用して、次の結果セットに進むよう読者に指示しなければならない.その後、第2のグループの値は再び循環して通過することができる.第二グループの結果:
    76270
    Sql Data Adapterを複数のSELECT文に使用します.
    Sql Data Readerは結果を中に格納する場合、通常はDataSetを使用する.次のテストに対して、Sql Data Adapterを使ってデータセットを充填します.コードは以下の通りです
    /// 
/// Executes two separate select statements against the the connection
/// 
/// Connection string to use
/// Should the statement generate an error
/// True if succesful
public static bool ExecuteMultipleSelects(string connectionString, bool generateError = false) {
 System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
 System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
 System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
 System.Data.DataSet dataset = new System.Data.DataSet();

 connection.ConnectionString = connectionString;
 command = new System.Data.SqlClient.SqlCommand();
 command.CommandText = @"
     SELECT * FROM MultiStatementTest WHERE somevalue%2 = 1;
     SELECT " + (generateError ? "WONTWORK" : "*") + 
       " FROM MultiStatementTest WHERE somevalue%2 = 0;";
 command.CommandType = System.Data.CommandType.Text;
 command.Connection = connection;

 try {
    connection.Open();
    adapter.SelectCommand = command;
    adapter.Fill(dataset);
 } catch (System.Exception exception) {
    System.Windows.MessageBox.Show(exception.Message, "Error occurred");
    return false;
 } finally {
    command.Dispose();
    connection.Dispose();
 }
 System.Windows.MessageBox.Show(string.Format(
    "Dataset contains {0} tables, {1} rows in table 1 and {2} rows in table 2", 
    dataset.Tables.Count, 
    dataset.Tables[0].Rows.Count, 
    dataset.Tables[1].Rows.Count, 
    "Operation succesful"));

 return true;
}
    現在、このようにしてデータを取得するのはとても簡単です.このコードはFillアダプタのメソッドのみを呼び出し、DataSetパラメータをパラメータとして転送します.アダプターは自動的にデータセットで2つの個別のオブジェクトを作成し、それらを充填します.私のテストシーンでは、1枚目の表は3行、2枚目の表は2行が含まれています.
    この例では、テーブルは即時に作成されるので、自動的にTable 1、Table 2と命名されるので、テーブルを参照するために名前を使用すれば、より記述的な名前に変更するのが賢明である.
    匿名T-SQLブロックを実行
    格納プロセスは非常に優れているが、T-SQLコードは本質的に非常に動的である場合がある.この場合、格納プロセスを作成するのは難しいかもしれない.バッチ処理はT-SQL文のセットを実行するためにも使用できます.この方法では、データベースには名前付きオブジェクトがないが、バッチ処理の実行方法は、例えばSQL Server Management Studioから実行されてもよい.
    テストコードは以下の通りです.
    /// 
/// Executes an anonymous T-SQL batch against the the connection
/// 
/// Connection string to use
/// Should the statement generate an error
/// True if succesful
public static bool ExecuteAnonymousTSql(string connectionString, bool generateError = false) {
 System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
 System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
 int rowsAffected;

 connection.ConnectionString = connectionString;
 command.CommandText = @"
    DECLARE @counter int = 1
    BEGIN
     WHILE (@counter <= 5) BEGIN
     INSERT INTO MultiStatementTest (somevalue) VALUES (RAND() * 100000);
     SET @counter = @counter + 1;
     " + (generateError ? "WONTWORK" : "") + @"
     END;
    END;";
 command.CommandType = System.Data.CommandType.Text;
 command.Connection = connection;

 try {
    connection.Open();
    rowsAffected = command.ExecuteNonQuery();
 } catch (System.Exception exception) {
    System.Windows.MessageBox.Show(exception.Message, "Error occurred");
    return false;
 } finally {
    command.Dispose();
    connection.Dispose();
 }
 System.Windows.MessageBox.Show(string.Format("{0} rows inserted", 
    rowsAffected, 
    "Operation succesful"));

 return true;
}
    この例では、いくつかのテスト行の作成を開始するのと同じスクリプトが使用されている.参照するように、変数宣言、ループなどはバッチ処理に含まれる完全に有効なステートメントです.
    このコマンドを実行すると、他の5行をテーブルに追加します.なお、NOCOUNTはデフォルトではクローズ状態ですので、このExecute NonQuery方法は正しい数の行に戻ってバッチ処理に挿入されます.NOCOUNTがonに設定されていると、影響を受ける行数は−1です.
    エラー処理
    バッチ処理で単一のステートメントを実行した時にエラーが発生したらどうすればいいですか?いくつかの文法的な誤りを使って、これをテストしました.このバッチ処理は全体として分析されますので、後の文に文法エラーが含まれていても、このバッチ処理は機能しません.例えば、UPDATEバッチ処理にエラーの文が含まれている場合、テーブルの状態は変更されません.
    エラーが文法エラーではなく実行中に発生した場合、状況は異なります.例えば、エラー値が発生したときに検出された外キーエラーを考慮します.この場合、前のステートメントはデータベース状態(ステートメントに依存)が変更されている可能性がありますので、通常のように適切なトランザクションを使用することをおすすめします.
    結論
    バッチ処理の方式は、良い古いストレージプロセスなどに取って代わることはないが、適切に使用すれば有用である.コール間にクライアント論理が必要でない限り、何度も往復することなく非常に動的な動作を作成するために使用することができる.
    Executing multiple SQL statements as one against SQL Serverはリンクの説明を追加してください.
    プロジェクトレベルJavaインターフェース署名と検証の実現
    TensorFlow Hubの設置と使用