Блог

Главная - Вопросы по программированию - TSQL большое обновление в пакетах — стоит ли Join дороже, потому что оно выполняется каждый раз в цикле

TSQL большое обновление в пакетах — стоит ли Join дороже, потому что оно выполняется каждый раз в цикле

#sql-server #tsql

#sql-server #tsql

Вопрос:

Я пытаюсь архивировать много записей в пакетах, а не за один снимок.

Будет ли TSQL объединять две таблицы, TeamRoster и @teamIdsToDelete для каждого цикла в пакете? Меня беспокоит то, что если моя временная таблица огромна, и я не удаляю записи из временной таблицы по ходу работы, объединение может быть излишне дорогостоящим. С другой стороны, насколько дорого удалять из временной таблицы по ходу работы? Компенсируется ли это (? реальными / гипотетическими?) меньшими объединениями, которые мне придется выполнять в каждом пакете?

(Могу предоставить более подробную информацию / мысли, но сделаю это, если это будет полезно.)

 DECLARE @teamIdsToDelete Table
    (
        RosterID int PRIMARY KEY
    )   

--collect the list of active teamIds. we will rely on the modified date to age them out.
INSERT INTO @teamIdsToDelete
SELECT  DISTINCT tr.RosterID FROM 
        rosterload.TeamRoster tr WITH (NOLOCK)
            WHERE tr.IsArchive=0 and tr.Loaded=1 


--ageout out remaining rosters. (no cap - proved we can update more than 50k by modifying test case:
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION

    UPDATE TOP (1000) r
    SET [Status] = 'Delete', IsArchive = 1, ModifiedDate = GETDATE(), ModifiedBy = 'abc'
    FROM rosterload.TeamRoster r with(rowlock)
    JOIN @teamIdsToDelete ttd ON ttd.rosterID = r.RosterID
    WHERE  r.[Status] != 'Delete' AND r.IsArchive != 1 AND r.ModifiedBy != 'abc' -- predicate for filtering;

    IF @@ROWCOUNT = 0 -- terminating condition;
        BEGIN
        COMMIT TRANSACTION
        BREAK
        END

    COMMIT TRANSACTION
END

Комментарии:

1. конечно, он будет повторять join — это именно то, что вы ему говорите!

2. да, это выглядело бы так на первый взгляд, но я мало что знаю о механизме выполнения запросов и о том, что происходит в фоновом режиме. кажется, что часто такие вещи оптимизируются. даже если нет, дороже ли делать большее объединение? может быть, операция постоянна?

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

4. Я тоже задаюсь этим вопросом — полное удаление пакета. Особенно мне интересно, не является ли это кучкой разработчиков, недостаточно хорошо разбирающихся в реляционных базах данных, чтобы думать, что пакеты — это хорошая идея. Я вижу, что это потребует меньше работы, но как насчет других проблем, таких как блокировка или зависание большой таблицы и ничего не удаляется?

5. Хороший момент — если параллелизм является узким местом, то вам, возможно, лучше делать это пакетно. Выполнение этого «кусками» абсолютно всегда будет требовать того же или большего объема работы (обычно большего), но обмен некоторых дополнительных операций ввода-вывода на то, что не удерживает блокировки в течение длительного времени, вполне может стоить того.

Ответ №1:

Насколько я понимаю, целью этого запроса является архивирование огромного количества строк без одновременной блокировки других запросов. Временная таблица помогает вам сузить подмножество записей для удаления. Поскольку у него есть один столбец, который является кластеризованным первичным ключом, соединение с другим ПК будет невероятно быстрым. Вы потратите больше усилий на вычисление и удаление обновленных записей из временной таблицы.

Кроме того, нет причин использовать транзакцию и выполнять пакеты. Вместо этого вы могли бы просто выполнить одно большое обновление. Результат тот же — таблица будет заблокирована после получения первых блокировок строк 5k (~ после обновления первых пяти пакетов) до инструкции COMMIT. С помощью подсказки rowlock не предотвращает эскалацию блокировки. С другой стороны, выполнение транзакции без вывода даст другим запросам возможность продолжения после каждого пакета из 1000 строк. Если вам нужно убедиться, что все записи архивируются за один раз — добавьте некоторую логику повторных попыток в свой запрос или код вашего приложения для таких ошибок, как взаимоблокировки или прерывание процесса. И вам действительно нужна подсказка NOLOCK?

Метки: