Влияние кол-ва запросов в транзакции на производительность

Нужно ли отправлять изменения в БД одной транзакцией, или каждый 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, пожалуй.:) В этом случае да, чаще всего если есть возможность обернуть несколько действий в одну транзакцию - лучше обернуть.