не забывайте о транзакциях. Производительность хороша, но проста (ЕСЛИ СУЩЕСТВУЕТ..) подход очень опасен.
, Когда несколько потоков попытаются работать Вставлять-или-обновлять, можно легко получить нарушение первичного ключа.
Решения, предоставленные @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
Сделайте выбор, если Вы получаете результат, обновляете его, в противном случае создаете его.
Я обычно делаю то, что несколько из других плакатов сказали относительно проверки его существующий первый и затем делающий независимо от того, что корректный путь. Одна вещь, которую необходимо помнить при выполнении этого, состоит в том, что план выполнения, кэшируемый sql, мог быть неоптимальным для одного пути или другого. Я верю лучшему способу сделать, это должно назвать две различных хранимых процедуры.
FirstSP: If Exists Call SecondSP (UpdateProc) Else Call ThirdSP (InsertProc)
Теперь, я не следую своему собственному совету очень часто, поэтому беру его с мелкой частицей соли.
Выполнение, если... еще существует..., включает выполнение двух минимумов запросов (один для проверки, один для принятия мер). Следующий подход требует только одного, где запись существует, два, если вставка требуется:
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')
При использовании ADO.NET DataAdapter обрабатывает это.
, Если Вы хотите обработать его сами, это - путь:
Удостоверяются, что существует ограничение первичного ключа на Ваш столбец ключа.
Тогда Вы:
можно также сделать это наоборот, т.е. сделать вставку сначала и сделать обновление, если вставка перестала работать. Обычно первый путь лучше, потому что обновления делаются чаще, чем вставляет.
Если Вы хотите к UPSERT больше чем одну запись за один раз, можно использовать СЛИЯНИЕ оператора ANSI SQL:2003 DML.
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 ...])
IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)
Редактирование:
увы, даже к моему собственному вреду, я должен допустить решения, которые делают это без выбора, кажется, лучше, так как они выполняют задачу с одним меньшим количеством шага.
Сделайте UPSERT:
UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key IF @@ROWCOUNT = 0 INSERT INTO MyTable (FieldA) VALUES (@FieldA)
В SQL Server 2008 можно использовать оператор
MERGESQL Server MS 2008 представляет оператор MERGE, которому я верю, является частью стандарта SQL:2003. Поскольку многие показали, это не грандиозное предприятие обработать случаи строки, но при контакте с большими наборами данных, каждому нужен курсор со всеми проблемами производительности, которые приходят. Оператор MERGE будет очень одобренным дополнением при контакте с большими наборами данных.
Посмотрите мой подробный ответ на очень похожий предыдущий вопрос
, @Beau Crawford является хорошим путем в SQL 2005 и ниже, хотя, если Вы предоставляете представителю, это должно перейти в первый парень к ТАК этому . Единственная проблема состоит в том, что для вставок это - все еще две операции IO.
мс Sql2008 представляет merge
из стандарта SQL:2003:
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, ... )
Теперь это - действительно всего одна операция IO, но ужасный код:-(
Хотя это довольно поздно, чтобы прокомментировать это, я хочу добавить более полный пример, используя Merge.
Такие операторы обновления вставки + обычно называются «Upsert» операторы и могут быть реализованы с использованием слияния в SQL Server.
Очень хороший пример дан здесь: http://weblogs.sqlteam.com/dang/archive/2009/01/31/upsert-race-condition-with-merge.aspx
Вышеуказанное объясняет блокировку и сценарии параллелизма.
Я буду цитировать одинаковую для справки:
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;
Прежде чем все перейдут на HOLDLOCK-ы из страха перед этими недобросовестными пользователями, запускающими ваши sprocs напрямую :-), позвольте мне заметить, что вы должны гарантировать уникальность новых PK-ов по дизайну (ключи идентификации, генераторы последовательностей в Oracle, уникальные индексы для внешних ID-ов, запросы, покрываемые индексами). Это альфа и омега проблемы. Если у вас этого нет, никакие HOLDLOCK-ы вселенной вас не спасут, а если это есть, то вам не нужно ничего, кроме UPDLOCK на первом селекте (или для использования update first).
Обычно Sprocs работают в очень контролируемых условиях и с допущением доверенного абонента (среднего уровня). Это означает, что если простой шаблон upsert (update+insert или merge) когда-либо увидит дубликаты PK, это означает ошибку в вашем среднем уровне или дизайне таблицы, и хорошо, что SQL в таком случае выдаст ошибку и отклонит запись. Помещение HOLDLOCK в этом случае равносильно поеданию исключений и приему потенциально ошибочных данных, а также снижению производительности.
Учитывая это, использование MERGE, или UPDATE, а затем INSERT проще для вашего сервера и менее подвержено ошибкам, поскольку вам не нужно помнить о добавлении (UPDLOCK) к первому select. Кроме того, если вы выполняете вставки/обновления небольшими партиями, вам нужно знать свои данные, чтобы решить, подходит ли транзакция или нет. Если это просто набор несвязанных записей, то дополнительная "обволакивающая" транзакция будет вредна.