SQL Server での挿入または更新のためのソリューション




sql-server database (17)

MyTable(KEY, datafield1, datafield2...)MyTable(KEY, datafield1, datafield2...)テーブル構造を想定します。

多くの場合、既存のレコードを更新するか、存在しない場合は新しいレコードを挿入します。

基本的に:

IF (key exists)
  run update command
ELSE
  run insert command

これを書くには最高のパフォーマンスの方法は何ですか?


MS SQL Server 2008には、SQL:2003標準の一部であると思われるMERGEステートメントが導入されています。 多くの人が見てきたように、1つの行のケースを処理するのは大したことではありませんが、大きなデータセットを処理する場合は、すべてのパフォーマンス上の問題を伴うカーソルが必要です。 大規模なデータセットを扱う場合、MERGEステートメントは非常に歓迎されます。


このクエリを使用できます。 すべてのSQL Serverエディションで作業します。 シンプルでクリアです。 しかし、2つのクエリを使用する必要があります。 MERGEを使用できない場合は使用できます

    BEGIN TRAN

    UPDATE table
    SET Id = @ID, Description = @Description
    WHERE Id = @Id

    INSERT INTO table(Id, Description)
    SELECT @Id, @Description
    WHERE NOT EXISTS (SELECT NULL FROM table WHERE Id = @Id)

    COMMIT TRAN

注:回答のネガを説明してください


トランザクションについて忘れないでください。 パフォーマンスは良いですが、単純な(IF EXISTS ..)アプローチは非常に危険です。
複数のスレッドがInsert-or-Updateを実行しようとすると、簡単に主キー違反を取得できます。

@Beau Crawford&@Estebanが提供するソリューションは一般的なアイデアを示していますが、エラーが発生しやすいです。

デッドロックやPK違反を避けるには、次のようなものを使うことができます:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

または

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

それは使用パターンに依存します。 細部に迷わずに使用法の大きな画像を見なければならない。 たとえば、レコードの作成後に使用パターンが99%更新されている場合は、「UPSERT」が最適なソリューションです。

最初の挿入(ヒット)の後、すべての単一ステートメントの更新であり、ifまたはbutsはありません。 挿入位置の 'where'条件が必要な場合は、重複を挿入し、ロックを処理したくない場合です。

UPDATE <tableName> SET <field>[email protected] WHERE [email protected];

IF @@ROWCOUNT = 0
BEGIN
   INSERT INTO <tableName> (field)
   SELECT @field
   WHERE NOT EXISTS (select * from tableName where key = @key);
END

if exists ... elseを実行するには、2つの要求を最小限にする必要があります(1つはチェックし、もう1つはアクションを実行します)。 次のアプローチでは、レコードが存在する場所は1つだけ必要で、挿入が必要な場合は2つ必要です。

DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
  INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')

多くの人がMERGE使用をお勧めしますが、私はあなたに注意しています。 デフォルトでは、複数のステートメントよりも並行性と競合状態から保護されるわけではありませんが、その他の危険性があります。

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

利用可能なこの「より単純な」構文があるとしても、私はまだこのアプローチを好んでいます(簡潔さのためにエラー処理が省略されています)。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE dbo.table SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END
COMMIT TRANSACTION;

たくさんの人がこの方法を提案します:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
  UPDATE ...
END
ELSE
  INSERT ...
END
COMMIT TRANSACTION;

しかし、これを達成するには、更新する行を見つけるためにテーブルを2回読み込む必要があるかもしれません。 最初のサンプルでは、​​行の位置を一度しか指定する必要がありません。 (いずれの場合も、最初の読み取りから行が見つからない場合は、挿入が行われます)。

他の人はこの方法を提案するでしょう:

BEGIN TRY
  INSERT ...
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 2627
    UPDATE ...
END CATCH

ただし、SQL Serverが例外をキャッチできるようにする以外の理由がない場合は、ほとんどすべての挿入が失敗するまれなシナリオを除いて、はるかに高価です。 私はここで多くを証明する:


選択を行い、結果が得られた場合は更新し、そうでない場合は作成します。


UPDATE if-no-rows-updatedを実行してからINSERTルートを実行する場合、競合状態を防ぐためにINSERTを最初に実行することを検討してください(間にDELETEがないと仮定します)

INSERT INTO MyTable (Key, FieldA)
   SELECT @Key, @FieldA
   WHERE NOT EXISTS
   (
       SELECT *
       FROM  MyTable
       WHERE Key = @Key
   )
IF @@ROWCOUNT = 0
BEGIN
   UPDATE MyTable
   SET [email protected]
   WHERE [email protected]
   IF @@ROWCOUNT = 0
   ... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
END

競合状態を避けることは別として、ほとんどの場合レコードがすでに存在する場合、INSERTが失敗し、CPUが無駄になります。

おそらくMERGEをSQL2008以降で使用することをお勧めします。


最初にアップデートに続いてインサートを試してみると、競争条件は本当に問題になりますか? キーキーの値を設定する2つのスレッドがあるとします

スレッド1:値= 1
スレッド2:値= 2

競合条件シナリオの例

  1. キーが定義されていません
  2. スレッド1が更新に失敗する
  3. スレッド2が更新に失敗する
  4. スレッド1またはスレッド2の1つが挿入に成功します。 例えばスレッド1
  5. 他のスレッドは、挿入(エラー重複キー付き) - スレッド2で失敗します。

    • 結果:挿入する2つのトレッドの「最初」が値を決定します。
    • 欲しい結果:データを書き込む2つのスレッドの最後(更新または挿入)が値を決定する必要があります

しかし; マルチスレッド環境では、OSスケジューラはスレッド実行の順序を決定します。上記のシナリオでは、この競合状態が存在する場合、実行順序を決定したのはOSです。 つまり、「スレッド1」または「スレッド2」は、システムの観点からは「最初」であったと言っても間違いです。

スレッド1とスレッド2の実行時間が非常に近い場合、競合状態の結果は重要ではありません。 唯一の要件は、スレッドの1つが結果の値を定義する必要があることです。

インプリメンテーションの場合:insertに続いてupdateがエラー "duplicate key"になる場合、これは成功として扱われるべきです。

また、当然データベース内の値が最後に書き込んだ値と同じであるとは決して考えないでください。



SQL Server 2008では、MERGEステートメントを使用できます


私は、以下の解決策を試してみましたが、insert文の同時リクエストが発生したときに私のために働きます。

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert table (key, ...)
   values (@key, ...)
end
commit tran

MERGEステートメントを使用することができます。このステートメントは、存在しない場合はデータを挿入するために使用され、存在する場合は更新されます。

MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`

誰もがあなたのsprocsを直接実行しているこれらのnafariousユーザーからの恐怖からHOLDLOCK-sに飛び乗る前に:-)私は、 あなたが新しいPK-sの設計 (アイデンティティ・キー、Oracleのシーケンス・ジェネレータ、外部ID-s、索引によってカバーされる照会)。 それが問題のアルファとオメガです。 あなたがそれを持っていなければ、宇宙のHOLDLOCK-sはあなたを救うつもりはなく、もしあなたがそれを持っていれば、最初の選択でUPDLOCKを超えて何も必要ない(または最初に更新を使う)。

Sprocsは通常、非常に制御された条件下で、信頼できる呼び出し元(中間層)の前提で実行されます。 単純アップサンプリングパターン(update + insertまたはmerge)がミッドティアまたはテーブルデザインのバグを意味する重複PKを見て、そのような場合にSQLがフォールトを叫び、レコードを拒絶するのは良いことを意味します。 この場合、HOLDLOCKを置くことは、例外を食べることと、潜在的に欠陥のあるデータを取り込むことに等しい。

つまり、MERGEを使用するか、UPDATEを実行してからINSERTを実行する方が、サーバー上での操作が簡単になり、最初に選択するために(UPDLOCK)を追加する必要がないため、エラーが発生しにくくなります。 また、小さなバッチで挿入/更新を行っている場合は、トランザクションが適切かどうかを判断するためにデータを知る必要があります。 これは無関係のレコードの集まりにすぎないので、追加の "包み込む"トランザクションは有害なものになります。


私は通常、他のポスターのいくつかが、それが最初に存在するかどうかをチェックしてから、正しいパスが何であれ、何をしているのかに関して、 これを行うときに覚えておくべきことの1つは、SQLによってキャッシュされた実行計画があるパスまたは他のパスに対して最適でない可能性があることです。 これを行う最善の方法は、2つの異なるストアドプロシージャを呼び出すことです。

FirstSP:
If Exists
   Call SecondSP (UpdateProc)
Else
   Call ThirdSP (InsertProc)

さて、私は自分のアドバイスを非常に頻繁にはしないので、塩の穀物でそれを取る。


非常によく似た以前の質問に対する私の詳細な答えを見てください

@Beau CrawfordはSQL 2005以降では良い方法ですが、担当者に付与する場合は最初の人に行ってください。 唯一の問題は、挿入の場合はまだ2つのIO操作です。

MS Sql2008は、SQL:2003標準からmergeを導入mergeました:

merge tablename with(HOLDLOCK) as target
using (values ('new value', 'different value'))
    as source (field1, field2)
    on target.idfield = 7
when matched then
    update
    set field1 = source.field1,
        field2 = source.field2,
        ...
when not matched then
    insert ( idfield, field1, field2, ... )
    values ( 7,  source.field1, source.field2, ... )

今は実際には1つのIO操作ですが、ひどいコードです:-(


IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)

編集:

悲しいかな、私自身の損害にもかかわらず、選択をせずにこれを行うソリューションは、より少ないステップでタスクを達成するので、より良いと思われます。







upsert