Oplossingen voor INSERT OF UPDATE op SQL Server

Veronderstel een tabelstructuur van MyTable(KEY, datafield1, datafield2...).

Vaak wil ik een bestaande record bijwerken of een nieuwe record invoegen als deze niet bestaat.

In wezen:

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

Wat is de best presterende manier om dit te schrijven?


Antwoord 1, autoriteit 100%

vergeet transacties niet. Prestaties zijn goed, maar een eenvoudige (INDIEN BESTAAN..) aanpak is erg gevaarlijk.
Wanneer meerdere threads zullen proberen om Insert-or-update uit te voeren, kunt u dit eenvoudig
krijg schending van de primaire sleutel.

Oplossingen geboden door @Beau Crawford & @Esteban laat een algemeen idee zien, maar is foutgevoelig.

Om impasses en PK-overtredingen te voorkomen, kunt u zoiets als dit gebruiken:

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

of

begin tran
   update table with (serializable) set ...
   where key = @key
   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

Antwoord 2, autoriteit 96%

Bekijk mijn gedetailleerde antwoord op een zeer vergelijkbare vorige vraag

@Beau Crawford’sis een goede manier in SQL 2005 en lager, maar als je rep verleent, zou het moeten gaan aan de eerste man die het ZO DOET. Het enige probleem is dat het voor inserts nog steeds twee IO-bewerkingen zijn.

MS Sql2008 introduceert MERGEvan de SQL:2003 standaard:

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, ... )

Nu is het eigenlijk maar één IO-bewerking, maar vreselijke code 🙁


Antwoord 3, autoriteit 46%

Doe een UPSERT:

UPDATE MyTable SET [email protected] WHERE [email protected]
ALS @@ROWCOUNT = 0
  INVOER IN MijnTable (FieldA) WAARDEN (@FieldA)

http://en.wikipedia.org/wiki/Upsert


Antwoord 4, autoriteit 26%

Veel mensen zullen je aanraden om MERGEte gebruiken, maar ik waarschuw je ervoor. Standaard beschermt het je niet tegen gelijktijdigheid en race-omstandigheden, net zo min als meerdere verklaringen, en het introduceert andere gevaren:

Zelfs met deze “eenvoudigere” syntaxis die beschikbaar is, geef ik nog steeds de voorkeur aan deze benadering (foutafhandeling weggelaten voor de beknoptheid):

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

Veel mensen zullen op deze manier voorstellen:

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

Maar dit alles zorgt ervoor dat u de tabel mogelijk twee keer moet lezen om de rij(en) te vinden die moeten worden bijgewerkt. In het eerste voorbeeld hoeft u de rij(en) maar één keer te zoeken. (In beide gevallen, als er geen rijen worden gevonden vanaf de eerste lezing, vindt er een invoeging plaats.)

Anderen zullen op deze manier voorstellen:

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

Dit is echter problematisch als om geen andere reden dan SQL Server uitzonderingen te laten opvangen die u in de eerste plaats had kunnen voorkomen, veel duurder is, behalve in het zeldzame scenario waarin bijna elke invoeging mislukt. Dat bewijs ik hier:


Antwoord 5, autoriteit 16%

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

Bewerken:

Helaas, zelfs tot mijn eigen nadeel, moet ik toegeven dat de oplossingen die dit doen zonder een selecte groep beter lijken te zijn, omdat ze de taak met één stap minder volbrengen.


Antwoord 6, autoriteit 9%

Als u meer dan één record tegelijk wilt UPSERT, kunt u de ANSI SQL:2003 DML-instructie MERGE gebruiken.

MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

Bekijk MERGE-instructie nabootsen in SQL Server 2005.


Antwoord 7, autoriteit 3%

Hoewel het vrij laat is om hier commentaar op te geven, wil ik een completer voorbeeld toevoegen met MERGE.

Dergelijke Insert+Update-instructies worden meestal “Upsert”-instructies genoemd en kunnen worden geïmplementeerd met MERGE in SQL Server.

Een heel goed voorbeeld wordt hier gegeven:
http://weblogs.sqlteam .com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

Het bovenstaande verklaart ook vergrendelings- en gelijktijdigheidsscenario’s.

Ik citeer hetzelfde ter referentie:

ALTER PROCEDURE dbo.Merge_Foo2
      @ID int
AS
SET NOCOUNT, XACT_ABORT ON;
MERGE dbo.Foo2 WITH (HOLDLOCK) AS f
USING (SELECT @ID AS ID) AS new_foo
      ON f.ID = new_foo.ID
WHEN MATCHED THEN
    UPDATE
            SET f.UpdateSpid = @@SPID,
            UpdateTime = SYSDATETIME()
WHEN NOT MATCHED THEN
    INSERT
      (
            ID,
            InsertSpid,
            InsertTime
      )
    VALUES
      (
            new_foo.ID,
            @@SPID,
            SYSDATETIME()
      );
RETURN @@ERROR;

Antwoord 8, autoriteit 2%

/*
CREATE TABLE ApplicationsDesSocietes (
   id                   INT IDENTITY(0,1)    NOT NULL,
   applicationId        INT                  NOT NULL,
   societeId            INT                  NOT NULL,
   suppression          BIT                  NULL,
   CONSTRAINT PK_APPLICATIONSDESSOCIETES PRIMARY KEY (id)
)
GO
--*/
DECLARE @applicationId INT = 81, @societeId INT = 43, @suppression BIT = 0
MERGE dbo.ApplicationsDesSocietes WITH (HOLDLOCK) AS target
--set the SOURCE table one row
USING (VALUES (@applicationId, @societeId, @suppression))
    AS source (applicationId, societeId, suppression)
    --here goes the ON join condition
    ON target.applicationId = source.applicationId and target.societeId = source.societeId
WHEN MATCHED THEN
    UPDATE
    --place your list of SET here
    SET target.suppression = source.suppression
WHEN NOT MATCHED THEN
    --insert a new line with the SOURCE table one row
    INSERT (applicationId, societeId, suppression)
    VALUES (source.applicationId, source.societeId, source.suppression);
GO

Vervang tabel- en veldnamen door wat je maar nodig hebt.
Zorg voor de gebruik AANvoorwaarde.
Stel vervolgens de juiste waarde (en type) in voor de variabelen op de DECLARE-regel.

Proost.


Antwoord 9, autoriteit 2%

U kunt de MERGE-instructie gebruiken. Deze instructie wordt gebruikt om gegevens in te voegen als deze niet bestaan of om bij te werken als deze wel bestaan.

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

Antwoord 10

Als u de route UPDATE if-no-rows-updated en INSERT gaat, overweeg dan eerst INSERT te doen om een raceconditie te voorkomen (ervan uitgaande dat er geen tussenkomst van DELETE is)

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

Afgezien van het vermijden van een raceconditie, als in de meeste gevallen het record al bestaat, zal de INSERT mislukken, waardoor CPU wordt verspild.

Het gebruik van MERGE heeft waarschijnlijk de voorkeur voor SQL2008 en later.


Antwoord 11

Dat hangt af van het gebruikspatroon. Men moet naar het grote gebruik kijken zonder te verdwalen in de details. Als het gebruikspatroon bijvoorbeeld 99% updates is nadat het record is gemaakt, dan is de ‘UPSERT’ de beste oplossing.

Na de eerste invoeging (hit), zijn het allemaal updates van afzonderlijke instructies, geen ifs of buts. De ‘waar’-voorwaarde op de invoeging is noodzakelijk, anders worden er duplicaten ingevoegd en wilt u niet te maken krijgen met vergrendeling.

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

Antwoord 12

MS SQL Server 2008 introduceert de MERGE-instructie, die volgens mij deel uitmaakt van de SQL:2003-standaard. Zoals velen hebben aangetoond, is het niet erg om gevallen met één rij af te handelen, maar als je te maken hebt met grote datasets, heb je een cursor nodig, met alle prestatieproblemen die daarbij komen kijken. De MERGE-verklaring zal een welkome aanvulling zijn bij het omgaan met grote datasets.


Antwoord 13

Maken de race-omstandigheden er echt toe als je eerst een update probeert, gevolgd door een insert?
Stel dat u twee threads heeft die een waarde willen instellen voor key key:

Draad 1: waarde = 1
Discussie 2: waarde = 2

Voorbeeld raceconditiescenario

  1. sleutelis niet gedefinieerd
  2. Draad 1 mislukt met update
  3. Draad 2 mislukt met update
  4. Precies een van draad 1 of draad 2 slaagt met insert. bijv. draad 1
  5. De andere thread mislukt met insert (met fout duplicaatsleutel) – thread 2.

    • Resultaat: de “eerste” van de twee treden die moet worden ingevoegd, bepaalt de waarde.
    • Gewenst resultaat: de laatste van de 2 threads om gegevens te schrijven (bijwerken of invoegen) moet de waarde bepalen

Maar; in een omgeving met meerdere threads beslist de OS-planner over de volgorde van de uitvoering van de thread – in het bovenstaande scenario, waar we deze race-conditie hebben, was het het OS dat besliste over de volgorde van uitvoering. Dat wil zeggen: het is verkeerd om te zeggen dat “thread 1” of “thread 2” de “eerste” was vanuit systeemoogpunt.

Als het tijdstip van uitvoering zo dichtbij is voor thread 1 en thread 2, doet de uitkomst van de race-conditie er niet toe. De enige vereiste zou moeten zijn dat een van de threads de resulterende waarde moet definiëren.

Voor de implementatie: als update gevolgd door invoegen resulteert in de fout “duplicaatsleutel”, moet dit als geslaagd worden beschouwd.

Je moet er natuurlijk ook nooit van uitgaan dat de waarde in de database hetzelfde is als de waarde die je het laatst hebt geschreven.


Antwoord 14

Voordat iedereen naar HOLDLOCK-s springt uit angst voor deze snode gebruikers die je sprocs rechtstreeks runnen 🙂 wil ik je erop wijzen dat je uniekheid van nieuwe PK-s moet garanderen door hun ontwerp(identiteit sleutels, sequentiegeneratoren in Oracle, unieke indexen voor externe ID-s, queries gedekt door indexen). Dat is de alfa en omega van het probleem. Als je dat niet hebt, zullen geen HOLDLOCK-s van het universum je redden en als je dat wel hebt, dan heb je niets meer nodig dan UPDLOCK bij de eerste selectie (of om eerst update te gebruiken).

Sprocs werken normaal gesproken onder zeer gecontroleerde omstandigheden en met de aanname van een vertrouwde beller (middenlaag). Dit betekent dat als een eenvoudig upsert-patroon (update + invoegen of samenvoegen) ooit een dubbele PK ziet, dit een bug in je mid-tier- of tabelontwerp betekent en het is goed dat SQL in dat geval een fout schreeuwt en het record verwerpt. Het plaatsen van een HOLDLOCK staat in dit geval gelijk aan het eten van uitzonderingen en het opnemen van mogelijk foutieve gegevens, naast het verminderen van uw prestatie.

Dat gezegd hebbende, is het gebruik van MERGE of UPDATE en vervolgens INSERT gemakkelijker voor uw server en minder foutgevoelig, omdat u niet hoeft te onthouden om (UPDLOCK) toe te voegen om eerst te selecteren. Ook als u invoegingen/updates in kleine batches uitvoert, moet u uw gegevens kennen om te kunnen beslissen of een transactie gepast is of niet. Als het slechts een verzameling niet-gerelateerde records is, zal een extra “omhullende” transactie schadelijk zijn.


Antwoord 15

Ik had onderstaande oplossing geprobeerd en het werkt voor mij, wanneer een gelijktijdig verzoek om een insert-statement plaatsvindt.

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

Antwoord 16

U kunt deze zoekopdracht gebruiken. Werk in alle SQL Server-edities. Het is eenvoudig en duidelijk. Maar je hebt 2 queries nodig. U kunt gebruiken als u MERGE niet kunt gebruiken

   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

OPMERKING: leg het negatieve antwoord uit


Antwoord 17

Ervan uitgaande dat u een enkele rij wilt invoegen/bijwerken, is de meest optimale benadering het gebruik van het REPEATABLE READtransactie-isolatieniveau van SQL Server:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION
    IF (EXISTS (SELECT * FROM myTable WHERE [email protected])
        UPDATE myTable SET ...
        WHERE [email protected]
    ELSE
        INSERT INTO myTable (key, ...)
        VALUES (@key, ...)
COMMIT TRANSACTION

Dit isolatieniveau voorkomt/blokkeert volgende herhaalbare leestransactiesom toegang te krijgen tot dezelfde rij (WHERE [email protected]) terwijl de momenteel lopende transactie open is.
Aan de andere kant worden bewerkingen op een andere rij niet geblokkeerd(WHERE [email protected]).


Antwoord 18

In SQL Server 2008 kunt u de MERGE-instructie gebruiken


Antwoord 19

Als u een if bestaat … anders … doet u minimaal twee verzoeken (één om te controleren, één om actie te ondernemen). De volgende benadering vereist slechts één waar het record bestaat, twee als een invoeging vereist is:

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')

Antwoord 20

Ik doe meestal wat verschillende van de andere posters hebben gezegd met betrekking tot eerst controleren of het bestaat en dan doen wat het juiste pad is. Een ding dat u moet onthouden wanneer u dit doet, is dat het uitvoeringsplan dat door sql in de cache wordt opgeslagen, voor het ene of het andere pad niet optimaal kan zijn. Ik denk dat de beste manier om dit te doen is om twee verschillende opgeslagen procedures aan te roepen.

FirstSP:
Als bestaat
  Bel SecondSP (UpdateProc)
Anders
  Bel ThirdSP (InsertProc)

Ik volg mijn eigen advies niet zo vaak op, dus neem het met een korreltje zout.


Antwoord 21

Als je ADO.NET gebruikt, regelt de DataAdapter dit.

Als je het zelf wilt regelen, is dit de manier:

Zorg ervoor dat er een primaire sleutelbeperking is voor uw sleutelkolom.

Dan jij:

  1. Doe de update
  2. Als de update mislukt omdat er al een record met de sleutel bestaat, voer dan invoeging uit. Als de update niet mislukt, bent u klaar.

Je kunt het ook andersom doen, d.w.z. eerst de invoeging doen en de update uitvoeren als de invoeging mislukt. Normaal gesproken is de eerste manier beter, omdat updates vaker worden gedaan dan invoegingen.


Antwoord 22

Voer een selectie uit, als je een resultaat krijgt, update het, zo niet, maak het dan.

Other episodes