Почему пакетные вставки/обновления быстрее? Как работают пакетные обновления?

Ответ 1

Почему пакетные вставки быстрее?

По многочисленным причинам, но основными тремя являются следующие:

• Запрос не нужно перерисовывать.
• Значения передаются в одном обратном направлении на сервер
• Команды находятся внутри одной транзакции

Это потому, что накладные расходы на подключение и настройку для вставки одной строки одинаковы для набора строк?

Частично да, см. выше.

Как работают пакетные обновления?

Это зависит от RDBMS.

В Oracle вы можете передать все значения в виде коллекции и использовать эту коллекцию в качестве таблицы в JOIN.

В PostgreSQL и MySQL вы можете использовать следующий синтаксис:

INSERT
INTO    mytable
VALUES 
        (value1),
        (value2),
        …

Вы также можете подготовить запрос один раз и вызвать его в каком-то цикле. Обычно есть способы сделать это в клиентской библиотеке.

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

Да, и вы можете или не можете воспользоваться этим поведением.

Я знаю, что запросы пакетной вставки имеют синтаксис, где у вас есть все значения вставки в одном большом запросе. Как выглядят запросы пакетного обновления?

В Oracle вы используете коллекцию в соединении:

MERGE
INTO    mytable
USING   TABLE(:mycol)
ON      …
WHEN MATCHED THEN
UPDATE
SET     …

В PostgreSQL:

UPDATE  mytable
SET     s.s_start = 1
FROM    (
        VALUES
        (value1),
        (value2),
        …
        ) q
WHERE   …

Ответ 2

Я искал ответ на ту же тему, о "массовом/пакетном" обновлении. Люди часто описывают проблему, сравнивая ее с предложением insert с несколькими наборами значений ( "объемная" часть).

INSERT INTO mytable (mykey, mytext, myint)
VALUES 
  (1, 'text1', 11),
  (2, 'text2', 22),
  ...

Ясный ответ все еще избегал меня, но я нашел решение здесь: http://www.postgresql.org/docs/9.1/static/sql-values.html

Чтобы было ясно:

UPDATE mytable
SET 
  mytext = myvalues.mytext,
  myint = myvalues.myint
FROM (
  VALUES
    (1, 'textA', 99),
    (2, 'textB', 88),
    ...
) AS myvalues (mykey, mytext, myint)
WHERE mytable.mykey = myvalues.mykey

Он обладает тем же свойством быть "объемным", который содержит много данных с одним утверждением.

Ответ 3

Другие сообщения объясняют, почему массовые операторы быстрее и как это делать с литеральными значениями.

Я думаю, что важно знать, как это сделать с заполнителями. Не использование заполнителей может привести к гигантским командам, к цитированию/экранированию ошибок и тем самым к приложениям, которые склонны к SQL-инъекции.

Массовая вставка с заполнителями в PostgreSQL >= 9.1

Чтобы вставить произвольные числа строк в таблицу "mytable", состоящую из столбцов "col1", "col2" и "col3", все в одном полученном (одно утверждение, одна транзакция):

INSERT INTO mytable (col1, col2, col3)
 VALUES (unnest(?), unnest(?), unnest(?))

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

Массовое обновление с заполнителями в PostgreSQL >= 9.1

Скажем, ваша таблица называется "mytable". Он состоит из столбцов "ключ" и "значение".

update mytable 
  set value = data_table.new_value
  from 
    (select unnest(?) as key, unnest(?) as new_value) as data_table
  where mytable.key = data_table.key

Я знаю, это непросто понять. Это похоже на запутанный SQL. С другой стороны: он работает, он масштабируется, он работает без какой-либо конкатенации строк, он безопасен, и он невероятно быстро.

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

Если вы нажмете ограничения по размеру, вам может потребоваться изучить COPY INTO ... FROM STDIN (PostgreSQL).

Ответ 4

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

пример обновления на основе набора в SQL-сервере:

update mytable
set myfield = 'test'
where myfield is null

Это обновит все 1 миллион записей, которые являются нулевыми за один шаг. Обновление курсора (как вы могли бы обновить миллион строк в не-пакетном режиме) будет проходить по каждой строке один раз и обновлять его.

Проблема с пакетной вставкой - размер партии. Если вы попытаетесь обновить слишком много записей сразу, база данных может заблокировать таблицу в течение всего процесса, заблокировав всех остальных пользователей. Таким образом, вам может понадобиться цикл, который принимает только часть партии за раз (но почти любое число, большее одной строки, будет быстрее, чем одна строка за раз). Это медленнее, чем обновление или вставка или удаление целую партию, но быстрее, чем операции строки за строкой, и может потребоваться в производственной среде со многими пользователями и с небольшим количеством времени простоя, когда пользователи не пытаются увидеть и обновить другие записи в одной таблице. Размер партии в значительной степени зависит от структуры базы данных и того, что происходит (таблицы с триггерами и множеством ограничений медленнее, как и таблицы с большим количеством полей, и поэтому требуют меньших партий).