Мне нужно вставить 1,3 миллиона записей из одной таблицы в другую, и это занимает очень много времени (более 13 минут). После некоторых исследований я обнаружил, что эту операцию лучше выполнять в пакетном режиме, поэтому я собрал что-то вроде этого (фактический запрос сложнее, здесь он упрощен для краткости):
DECLARE @key INT; SET @key = 0;
CREATE TABLE #CURRENT_KEYS(KEY INT)
WHILE 1=1
BEGIN
-- Getting subset of keys
INSERT INTO #CURRENT_KEYS(KEY)
SELECT TOP 100000 KEY FROM #ALL_KEYS WHERE KEY > @key
IF @@ROWCOUNT = 0 BREAK
-- Main Insert
INSERT INTO #RESULT(KEY, VALUE)
SELECT MAIN_TABLE.KEY, MAIN_TABLE.VALUE
FROM MAIN_TABLE INNER_JOIN #CURRENT_KEYS
ON MAIN_TABLE.KEY = #CURRENT_KEYS.KEY
SELECT @key = MAX(KEY ) FROM #CURRENT_KEYS
TRUNCATE TABLE #CURRENT_KEYS
END
У меня уже есть проиндексированный список из 1,3 миллиона ключей в таблице #ALL_KEYS, поэтому идея заключается в том, чтобы в цикле создать меньшее подмножество ключей для JOIN и INSERT. Приведенный выше цикл выполняется 13 раз (1 300 000 записей / 100 000 записей в пакете). Если поставить паузу всего через одну итерацию - время выполнения 9 секунд. Я предполагал, что общее время выполнения будет 9*13 секунд, но это те же самые 13 минут!
Есть идеи, почему?
ПРИМЕЧАНИЕ. Вместо временной таблицы #CURRENT_KEYS я попытался использовать CTE, но с тем же результатом.
ОБНОВЛЕНИЕ Немного статистики ожидания.
Я показываю для этого процесса PAGEIOLATCH_SH, а иногда и PREEMPTIVE_OS_WRITEFILEGATHER в статистике ожидания, иногда более 500 мс, но часто ‹ 100 мс. Также SP_WHO показывает пользователя как suspended на время запроса.
