Нужно ли отправлять изменения в БД одной транзакцией, или каждый INSERT, UPDATE, DELETE лучше выполнять отдельной транзакцией? Что лучше при выполнении 10 несвязанных запросов (не нужно откатывать предыдущий запрос при ошибке последующего), отправить их одной транзакцией, или каждый по отдельности? Какой вариант лучше для производительности приложения и субд?
В общем случае ни первый ни второй вариант не являются оптимальными с точки зрения производительности.
Если нужно удалить(вставить/поменять) действительно большое количество строк, в первом случае мы получаем большие расходы на открытие, закрытие большого количества транзакций.
Во втором случае имеем слишком огромную транзакцию, которая тоже под конец работы будет долго закрываться, да ещё и рискуем упасть, т.к. размеры транзакции ограничены(видел такую ошибку всего на нескольких миллионах строк).
Чтобы наглядно продемонстрируем, написал небольшой скрипт. MS SQL Server.
Кратко что он делает:
▪T_TABLE - таблица с данными
▪T_RESULT - таблица с результатами эксперимента
▪P_INSERT_ROWS @Count - вставляет в таблицу T_TABLE @Count строк
▪P_DALATE_ROWS @Count, @Pack - удаляет из таблицы
T_TABLE все строки в транзакциях по @Pack штук, и записывает затраченное время в таблицу T_RESULT.
Финальный скрипт запускает в цикле вставку 1 000 000 строк, и их удаления пачками по 1, 10 .. 1 000 000 штук. И затем выводит содержимое T_RESULT.
Скрипт:
USE tempdb;
GO
IF OBJECT_ID('P_INSERT_ROWS', 'P') IS NOT NULL DROP PROC P_INSERT_ROWS;
IF OBJECT_ID('P_DELETE_ROWS', 'P') IS NOT NULL DROP PROC P_DELETE_ROWS;
IF OBJECT_ID('T_TABLE', 'U') IS NOT NULL DROP TABLE T_TABLE;
IF OBJECT_ID('T_RESULT', 'U') IS NOT NULL DROP TABLE T_RESULT;
GO
CREATE TABLE T_TABLE (
id INT IDENTITY(1,1),
Number INT,
String NVARCHAR(4000)
)
CREATE INDEX IN_T_TABLE_NUMBER ON T_TABLE(Number ASC)
CREATE TABLE T_RESULT (
execute_time DATETIME,
Cnt INT,
Pack INT
)
GO
CREATE PROC P_INSERT_ROWS
@Count INT
AS
;WITH CTE AS(
SELECT 1 N UNION ALL SELECT N+1 FROM CTE WHERE N<@Count
)
INSERT T_TABLE
SELECT N, LEFT(REPLICATE(N, 100),4000)
FROM CTE
OPTION(MAXRECURSION 0)
GO
CREATE PROC P_DELETE_ROWS
@Count INT,
@Pack INT
AS
DECLARE @TTT DATETIME = GETDATE();
DECLARE @NumberStart INT = 1
WHILE @NumberStart < @Count
BEGIN
BEGIN TRAN;
DELETE FROM T_TABLE WHERE Number BETWEEN @NumberStart AND @NumberStart + @Pack - 1 OPTION(RECOMPILE);
COMMIT TRAN;
SET @NumberStart += @Pack;
END;
INSERT T_RESULT
SELECT GETDATE()-@TTT execute_time, @Count Cnt, @Pack Pack
GO
SET NOCOUNT ON;
DECLARE
@Count INT = 1000000,
@Pack INT = 1
WHILE @Pack <= @Count
BEGIN
EXEC P_INSERT_ROWS @Count
EXEC P_DELETE_ROWS @Count, @Pack
SET @Pack *= 10;
END
SELECT * FROM T_RESULT
GO
Содержимое T_RESULT:
execute_time Cnt Pack
1900-01-01 00:15:05.183 1000000 1
1900-01-01 00:01:56.003 1000000 10
1900-01-01 00:00:32.200 1000000 100
1900-01-01 00:00:22.370 1000000 1000
1900-01-01 00:00:21.940 1000000 10000
1900-01-01 00:00:22.663 1000000 100000
1900-01-01 00:01:10.327 1000000 1000000Как видим, лучший результат по времени выполнения имеем, когда удаляется в одной транзакции пачками по 10000 штук, т.е. это намного эффективнее, чем удалять по одной записи в транзакции, и эффективнее, чем удалить все строки в одной транзакции. Эксперимент не строгий, запуски влияют друг на друга, но всё равно показательно.
UPD: На практике, транзакции в реальной жизни не часто бывают большими. Кроме запросов вида DELETE FROM T. В этом случае да, чаще всего если есть возможность обернуть несколько действий в одну транзакцию - лучше обернуть.
@sqlhub