SQL - Вставка и обновление нескольких записей сразу
У меня есть хранимая процедура, которая ответственна за вставку или обновление нескольких записей сразу. Я хочу выполнить это в своей хранимой процедуре ради производительности.
Эта хранимая процедура берет в разграниченном запятой списке идентификаторов разрешения и состояния. Идентификаторы разрешения хранятся в переменной, названной @PermitIDs. Состояние хранится в переменной, названной @Status. У меня есть пользовательская функция, которая преобразовывает этот разграниченный запятой список идентификаторов разрешения в Таблицу. Я должен пройти каждый из этих идентификаторов и сделать или вставку или обновить в таблицу под названием PermitStatus.
Если запись с идентификатором разрешения не существует, я хочу добавить запись. Если это действительно существует, я, хотят обновить запись с данным значением @Status. Я знаю, как сделать это для единственного идентификатора, но я не знаю, как сделать это для нескольких идентификаторов. Для единственных идентификаторов я делаю следующее:
-- Determine whether to add or edit the PermitStatus
DECLARE @count int
SET @count = (SELECT Count(ID) FROM PermitStatus WHERE [PermitID]=@PermitID)
-- If no records were found, insert the record, otherwise add
IF @count = 0
BEGIN
INSERT INTO
PermitStatus
(
[PermitID],
[UpdatedOn],
[Status]
)
VALUES
(
@PermitID,
GETUTCDATE(),
1
)
END
ELSE
UPDATE
PermitStatus
SET
[UpdatedOn]=GETUTCDATE(),
[Status]=@Status
WHERE
[PermitID]=@PermitID
Как я циклично выполняюсь через записи в Таблице, возвращенной моей пользовательской функцией, чтобы динамично вставить или обновить записи по мере необходимости?
6 ответов
создайте функцию разделения и используйте ее следующим образом:
SELECT
*
FROM YourTable y
INNER JOIN dbo.splitFunction(@Parameter) s ON y.ID=s.Value
Я предпочитаю подход с числовой таблицей
Для того, чтобы этот метод работал, вам нужно выполнить настройку единой таблицы времени:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
После того, как таблица номеров настроена вверх, создайте эту функцию:
CREATE FUNCTION [dbo].[FN_ListToTableAll]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN
(
----------------
--SINGLE QUERY-- --this WILL return empty rows
----------------
SELECT
ROW_NUMBER() OVER(ORDER BY number) AS RowNumber
,LTRIM(RTRIM(SUBSTRING(ListValue, number+1, CHARINDEX(@SplitOn, ListValue, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS ListValue
) AS InnerQuery
INNER JOIN Numbers n ON n.Number < LEN(InnerQuery.ListValue)
WHERE SUBSTRING(ListValue, number, 1) = @SplitOn
);
GO
Теперь вы можете легко разделить строку CSV в таблицу и объединить ее:
select * from dbo.FN_ListToTableAll(',','1,2,3,,,4,5,6777,,,')
ВЫВОД:
RowNumber ListValue
----------- ----------
1 1
2 2
3 3
4
5
6 4
7 5
8 6777
9
10
11
(11 row(s) affected)
Чтобы заставить то, что вам нужно, работать, сделайте следующее:
--this would be the existing table
DECLARE @OldData table (RowID int, RowStatus char(1))
INSERT INTO @OldData VALUES (10,'z')
INSERT INTO @OldData VALUES (20,'z')
INSERT INTO @OldData VALUES (30,'z')
INSERT INTO @OldData VALUES (70,'z')
INSERT INTO @OldData VALUES (80,'z')
INSERT INTO @OldData VALUES (90,'z')
--these would be the stored procedure input parameters
DECLARE @IDList varchar(500)
,@StatusList varchar(500)
SELECT @IDList='10,20,30,40,50,60'
,@StatusList='A,B,C,D,E,F'
--stored procedure local variable
DECLARE @InputList table (RowID int, RowStatus char(1))
--convert input prameters into a table
INSERT INTO @InputList
(RowID,RowStatus)
SELECT
i.ListValue,s.ListValue
FROM dbo.FN_ListToTableAll(',',@IDList) i
INNER JOIN dbo.FN_ListToTableAll(',',@StatusList) s ON i.RowNumber=s.RowNumber
--update all old existing rows
UPDATE o
SET RowStatus=i.RowStatus
FROM @OldData o WITH (UPDLOCK, HOLDLOCK) --to avoid race condition when there is high concurrency as per @emtucifor
INNER JOIN @InputList i ON o.RowID=i.RowID
--insert only the new rows
INSERT INTO @OldData
(RowID, RowStatus)
SELECT
i.RowID, i.RowStatus
FROM @InputList i
LEFT OUTER JOIN @OldData o ON i.RowID=o.RowID
WHERE o.RowID IS NULL
--display the old table
SELECT * FROM @OldData order BY RowID
ВЫХОД:
RowID RowStatus
----------- ---------
10 A
20 B
30 C
40 D
50 E
60 F
70 z
80 z
90 z
(9 row(s) affected)
РЕДАКТИРОВАТЬ благодаря @Emtucifor щелкните здесь , чтобы узнать о состоянии гонки. Я включил в свой ответ подсказки по блокировке, чтобы предотвратить проблемы с условиями гонки при высоком уровне параллелизма.
Существуют различные методы выполнения тех частей, о которых вы спрашиваете.
Передача значений
Есть десятки способов сделать это. Вот несколько идей для начала:
- Передайте строку идентификаторов и проанализируйте ее в таблице, затем присоединитесь.
- SQL 2008: Присоединение к параметру, возвращающему табличное значение
- Ожидайте, что данные будут существовать в предопределенной временной таблице, и присоединитесь к ней
- Используйте постоянную таблицу с ключом сеанса
- Поместите код в триггер и присоединитесь в таблицы INSERTED и DELETED в нем.
Эрланд Соммарског дает прекрасное всестороннее обсуждение списков на сервере sql . На мой взгляд, возвращающий табличное значение параметр в SQL 2008 - наиболее элегантное решение для этого.
Upsert / Merge
- Выполните отдельные UPDATE и INSERT (два запроса, по одному для каждого набора, а не построчно).
- SQL 2008: MERGE.
Важная проблема
Однако одна вещь, о которой никто не упомянул, - это то, что почти весь код upsert, , включая SQL 2008 MERGE , страдает от проблем состояния гонки при высоком уровне параллелизма. Если вы не используете HOLDLOCK и другие подсказки блокировки в зависимости от того, что делается, вы в конечном итоге столкнетесь с конфликтами. Таким образом, вам нужно либо блокировать, либо соответствующим образом реагировать на ошибки (некоторые системы с огромными транзакциями в секунду успешно использовали метод ответа на ошибку вместо использования блокировок).
Следует понимать, что различные комбинации подсказок блокировки неявно изменяют уровень изоляции транзакции, который влияет на то, какие типы блокировок устанавливаются.Это меняет все: какие другие блокировки предоставляются (например, простое чтение), время, когда блокировка повышается до обновления из намерения обновления, и так далее.
Я настоятельно рекомендую вам прочитать более подробную информацию об этих проблемах состояния гонки. Вы должны понять это правильно.
CREATE PROCEDURE dbo.PermitStatusUpdate
@PermitIDs varchar(8000), -- or (max)
@Status int
AS
SET NOCOUNT, XACT_ABORT ON -- see note below
BEGIN TRAN
DECLARE @Permits TABLE (
PermitID int NOT NULL PRIMARY KEY CLUSTERED
)
INSERT @Permits
SELECT Value FROM dbo.Split(@PermitIDs) -- split function of your choice
UPDATE S
SET
UpdatedOn = GETUTCDATE(),
Status = @Status
FROM
PermitStatus S WITH (UPDLOCK, HOLDLOCK)
INNER JOIN @Permits P ON S.PermitID = P.PermitID
INSERT PermitStatus (
PermitID,
UpdatedOn,
Status
)
SELECT
P.PermitID,
GetUTCDate(),
@Status
FROM @Permits P
WHERE NOT EXISTS (
SELECT 1
FROM PermitStatus S
WHERE P.PermitID = S.PermitID
)
COMMIT TRAN
RETURN @@ERROR;
Примечание: XACT_ABORT помогает гарантировать закрытие явной транзакции после тайм-аута или непредвиденной ошибки.
Чтобы убедиться, что это решает проблему блокировки, откройте несколько окон запросов и выполните идентичный пакет, например:
WAITFOR TIME '11:00:00' -- use a time in the near future
EXEC dbo.PermitStatusUpdate @PermitIDs = '123,124,125,126', 1
Все эти разные сеансы будут выполнять хранимую процедуру почти в один и тот же момент. Проверяйте каждую сессию на наличие ошибок. Если таковых не существует, попробуйте повторить тот же тест еще несколько раз (поскольку состояние гонки может возникнуть не всегда, особенно с MERGE).
Записи по ссылкам, которые я дал выше, дают даже больше подробностей, чем я сделал здесь, а также описывают, что делать с оператором SQL 2008 MERGE. Пожалуйста, прочтите их внимательно, чтобы по-настоящему понять суть проблемы.
Вкратце, при MERGE явная транзакция не требуется, но вам нужно использовать SET XACT_ABORT ON и использовать подсказку блокировки:
SET NOCOUNT, XACT_ABORT ON;
MERGE dbo.Table WITH (HOLDLOCK) AS TableAlias
...
Это предотвратит условия гонки параллелизма, вызывающие ошибки.
Я также рекомендую вам выполнять обработку ошибок после каждого оператора изменения данных.
Если вы используете SQL Server 2008, вы можете использовать параметры с табличным значением - вы передаете таблицу записей в хранимую процедуру, а затем вы можете выполнить MERGE .
Передача параметра, имеющего табличное значение, устранит необходимость в синтаксическом анализе строк CSV.
Изменить:
ErikE поднял вопрос об условиях гонки, пожалуйста, обратитесь к его ответу и связанным статьям.
По сути, у вас есть хранимая процедура upsert (например, UpsertSinglePermit)
(например, код, который вы указали выше) для работы с один ряд.
Итак, шаги, которые я вижу, - это создание новой хранимой процедуры (UpsertNPermit), которая выполняет
a) Разбирает входную строку на n записей (каждая запись содержит идентификатор разрешения и статус) b) Каждая запись как показано выше, вызовите UpsertSinglePermit
C++.
Это золотой стандарт для игрового программирования AAA. Если вам нужно сделать что-то интересное, вам нужно будет сделать C++ или углубиться в неуправляемый C # (не всегда приятно).
C++ также, возможно, быстрее ( применяются обычные предостережения ).
В качестве учебного опыта C # не стоит. C++ бесспорно лучше, особенно в квази-встраиваемом мире консолей. Чтобы получить объектно-ориентированный опыт, перейдите к Java.
-121--1287528-Чтобы сказать правду... вы должны принять решение, какой язык лучше. Я знаю, что я могу сделать с C #. Я знаю, что можно сделать в C++. C # не предназначен для того, чтобы делать то, что было сделано C++... писать код на самом базовом уровне и все еще быть несколько значимым при чтении человеческими глазами.
Мы разрабатываем игровой движок с C #, DirectX... это проблема? черт возьми, да... но это то, что мы решили сделать. Мы рассматриваем некоторые уровни производительности, которые очень близки к тому, что может дать C++. Итак, я не вижу никаких проблем с этим усилием.
Для межплатформенной разработки, если бы не .Net, у нас могла бы не быть платформы Mono. Платформа Mono расширила нашу базу платформы.
Вот некоторая поддержка моих аргументов ...
-121--1287527-При наличии 2008 SQL Server можно использовать команду MERGE . Вот статья, описывающая это.
Вы должны иметь возможность выполнять вставку и обновление в виде двух запросов на основе набора.
Приведенный ниже код основан на процедуре загрузки данных, которую я написал недавно, которая берет данные из промежуточной таблицы и вставляет или обновляет их в основную таблицу.
Я попытался привести его в соответствие с вашим примером, но вам может потребоваться настроить это (и создать UDF с табличным значением, чтобы преобразовать ваш CSV в таблицу идентификаторов).
-- Update where the join on permitstatus matches
Update
PermitStatus
Set
[UpdatedOn]=GETUTCDATE(),
[Status]=staging.Status
From
PermitStatus status
Join
StagingTable staging
On
staging.PermitId = status.PermitId
-- Insert the new records, based on the Where Not Exists
Insert
PermitStatus(Updatedon, Status, PermitId)
Select (GETUTCDATE(), staging.status, staging.permitId
From
StagingTable staging
Where Not Exists
(
Select 1 from PermitStatus status
Where status.PermitId = staging.PermidId
)