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 FieldA=@FieldA WHERE Key=@Sleutel 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:
- Controleren op mogelijke schendingen van beperkingen voordat TRY/CATCH wordt ingevoerd
- Invloed op de prestaties van verschillende technieken voor foutafhandeling
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 FieldA=@FieldA
WHERE Key=@Key
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>=@field WHERE key=@key;
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
- sleutelis niet gedefinieerd
- Draad 1 mislukt met update
- Draad 2 mislukt met update
- Precies een van draad 1 of draad 2 slaagt met insert. bijv. draad 1
-
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 key=@key)
UPDATE myTable SET ...
WHERE key=@key
ELSE
INSERT INTO myTable (key, ...)
VALUES (@key, ...)
COMMIT TRANSACTION
Dit isolatieniveau voorkomt/blokkeert volgende herhaalbare leestransactiesom toegang te krijgen tot dezelfde rij (WHERE key=@key) terwijl de momenteel lopende transactie open is.
Aan de andere kant worden bewerkingen op een andere rij niet geblokkeerd(WHERE key=@key2).
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:
- Doe de update
- 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.