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

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

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

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

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

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

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

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

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

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

INSERT
INTO    mytable
VALUES 
        (value1),
        (value2),
        a€¦

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

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

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

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

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

MERGE
INTO    mytable
USING   TABLE(:mycol)
ON      a€¦
WHEN MATCHED THEN
UPDATE
SET     a€¦

В PostgreSQL:

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

Я искал ответ на тот же вопрос, о «массовом / пакетном» обновлении. Люди часто описывают проблему, сравнивая ее с предложением 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

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

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

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

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

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

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

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

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