Вставка или обновление T-SQL

У меня вопрос о производительности SQL Server.

Предположим, что у меня есть таблица persons со следующими столбцами: id, name, surname.

Теперь я хочу вставить новую строку в эту таблицу. Правило следующее:

Если id отсутствует в таблице, вставьте строку.
Если присутствует id, обновите.

У меня здесь два решения:

Во-первых:

update persons
  set [email protected]_id, [email protected]_name, [email protected]_surname
where [email protected]_id
if @@ROWCOUNT = 0 
  insert into persons(id, name, surname)
  values (@p_id, @p_name, @p_surname)

Во-вторых:

if exists (select id from persons where id = @p_id)
  update persons
    set [email protected]_id, [email protected]_name, [email protected]_surname
  where [email protected]_id
else
  insert into persons(id, name, surname)
  values (@p_id, @p_name, @p_surname)

Что такое лучший подход? Кажется, что во втором варианте, чтобы обновить строку, ее нужно искать два раза, тогда как в первом варианте - только один раз. Есть ли другие решения проблемы? Я использую MS SQL 2000.

Ответ 1

Оба работают нормально, но я обычно использую опцию 2 (pre-mssql 2008), поскольку она читается немного более четко. Я бы тоже не сказал об эффективности здесь... Если это станет проблемой, вы можете использовать NOLOCK в предложении exists. Хотя прежде чем вы начнете использовать NOLOCK повсюду, убедитесь, что вы накрыли все свои базы (индексы и объекты с большой картинкой). Если вы знаете, что будете обновлять каждый элемент более одного раза, тогда он может заплатить, чтобы рассмотреть вариант 1.

Вариант 3 - не использовать деструктивные обновления. Это требует больше работы, но в основном вы вставляете новую строку каждый раз, когда данные изменяются (никогда не обновляйте и не удаляйте из таблицы), и у вас есть представление, которое выбирает все самые последние строки. Это полезно, если вы хотите, чтобы таблица содержала историю всех предыдущих состояний, но она также может быть чрезмерной.

Ответ 2

Вариант 1 кажется хорошим. Однако, если вы работаете на SQL Server 2008, вы также можете использовать MERGE, что может пригодиться для таких задач UPSERT.

Обратите внимание, что вы можете использовать явную транзакцию и XACT_ABORT для таких задач, чтобы последовательность транзакций оставалась в случае проблема или одновременное изменение.

Ответ 3

Я предпочитаю использовать вариант 1. Если в таблице есть запись, вы сохраняете один поиск. Если этого не произойдет, вы ничего не потеряете. Более того, во втором варианте вы можете столкнуться с смешными проблемами блокировки и блокировки, связанными с несовместимостью замков. Там есть еще информация о моем блоге:

http://sqlblogcasts.com/blogs/piotr_rodak/archive/2010/01/04/updlock-holdlock-and-deadlocks.aspx

Ответ 4

Стремясь быть немного более сухим, я не дважды записываю список значений.

begin tran
insert into persons (id)
select @p_id from persons
 where not exists (select * from persons where id = @p_id)

update persons
set [email protected]_name, [email protected]_surname
where id = @p_id

commit

Столбцы name и surname должны иметь значение NULL.

Транзакция означает, что другой пользователь никогда не увидит "пустую" запись.

Изменить: очистить

Ответ 5

Вы можете просто использовать @@RowCount, чтобы узнать, не сделало ли это обновление. Что-то вроде:

    UPDATE MyTable
       SET SomeData = 'Some Data' WHERE ID = 1
    IF @@ROWCOUNT = 0
      BEGIN
        INSERT MyTable
        SELECT 1, 'Some Data'       
      END