Что выгоднее: выполнять много запросов на вставку в рамках одной транзакции или сделать один запрос для вставки всех значений сразу? 

Какой метод вставки строк является более оптимальным: использование единой транзакции с одним запросом вставки, как в примере ниже? 

<pre><code class="sql">INSERT INTO
    Person (Id, Name)
VALUES
    (1, 'Amir' ),
    (2, 'Sofia'),
    (3, 'Aya');</code></pre> 

или последовательное выполнение нескольких отдельных запросов, как представлено здесь? 

<pre><code class="sql">INSERT INTO Person VALUES (1, "Amir");
INSERT INTO Person VALUES (2, "Sofia");
INSERT INTO Person VALUES (3, "Aya");</code></pre>

Как использование оператора onConflict влияет на производительность? 

Кроме того, какие best practices существуют для выполнения массовых вставок строк, если не использовать оператор copy?
  • 28 августа 2024 г. 14:04
Ответы на вопрос 3 
При сравнении методов вставки данных в базу данных, выполнение одной операции вставки с несколькими значениями (например, через один запрос с несколькими строками, как в вашем первом примере) обычно является более оптимальным, чем выполнение нескольких отдельных запросов на вставку.

### Причины, почему группировка вставок предпочтительнее:
1. **Сетевые накладные расходы**: Использование одного запроса вместо нескольких сильно уменьшает количество сетевых операций. Каждый отдельный запрос требует отдельного сетевого обращения к базе данных, что увеличивает время ожидания и накладные расходы.

2. **Транзакционные накладные расходы**: В большинстве случаев, базовые системы управления базами данных (СУБД) обрабатывают транзакции более эффективно при групповых операциях. Выполнение всех вставок в одной транзакции позволяет сократить накладные расходы на управление транзакциями.

3. **Оптимизация на стороне СУБД**: Многие СУБД оптимизируют выполнение запросов, когда они могут обработать несколько строк за одну операцию. Это может снизить затраты на блокировки и логирование.

### Использование `ON CONFLICT`
Оператор `ON CONFLICT` (или его эквиваленты, в зависимости от СУБД) позволяет обрабатывать случаи, когда при вставке возникает конфликт (например, уникальное ограничение на столбец). Использование `ON CONFLICT` также работает в рамках одной транзакции и может существенно упростить управление ошибками. Однако стоит отметить, что использование этого оператора может немного добавить показатели накладных расходов при обработке конфликтов, в зависимости от конкретной логики обработки.

### Best Practices для массовых вставок, если не используете оператор `COPY`
1. **Пакетные вставки (Batch Inserts)**: Подобно вашему первому примеру, старайтесь объединять вставки в одном запросе, чтобы уменьшить количество обращений к базе данных.

2. **Параметризованные запросы**: Если требуется вставлять динамические данные, используйте параметризованные запросы, чтобы предотвратить SQL-инъекции и улучшить производительность (поскольку это позволяет СУБД кэшировать план выполнения).

3. **Использование транзакций**: Оборачивайте массовые вставки в транзакции, чтобы обеспечить атомарность и повысить производительность.

4. **Избегать триггеров и индексов во время вставок**: Если возможно, временно отключайте триггеры и индексы перед массовыми вставками, а затем включайте их обратно и выполняйте обновление индексов.

5. **Асинхронные вставки**: Если ваша СУБД и приложение это поддерживают, подумайте об асинхронных вставках для повышения производительности.

6. **Тестирование и мониторинг**: Проводите тесты для оценки производительности различных методов вставки и отслеживайте показатели производительности в реальном времени.

При использовании этих методов можно значительно повысить эффективность операций вставки в базе данных.
В реальной жизни ты должен использовать второй варинт . Потому что - транзакции. 
 Ими надо управлять. И приложение типа CRUD тоже должно всегда использовать второй вариант. 
 
 Первый вариант - скорее всего применим при массовых загрузках. Например при создании БД. При миграции. 
 И еще может быть при выполнении каких-то maintenance задач. 
 Обычно в этом случае - отключают триггеры, индексы и грузят на максималках. А уже потом 
 последовательно включают и смотрят что валидация прошла успешно.
Эти два метода вставки не идентичны, в ситуациях с ошибками вставки данных (например ошибка уникальности индекса или любой другой constrainit) до окончания транзакции, в первом случае никакие данные не будут записаны, а во втором, будут записаны данные до возникновения ошибки. Понятно что если все завернуть в единую транзакцию и при возникновении ошибки эту транзакцию отменять, то тогда содержимое базы будет идентично. Но когда речь идет об нестандартных способах обработки ошибок, второй вариант может оказаться удобнее, ведь можно пожелать продолжить вставку данных, с пропуском ошибочных... когда как первый вариант такого не позволит (сообщение об ошибке может даже не сообщить идентификатор, с которым произошла проблема, а если и будет такая информация, то только по одной записи) 
 
 В общем первый вариант идеален, если контроль за целостностью данных отключен или точно известно, что данные не противоречивы. Тут и скорость и синтаксическая простота, и меньше места sql занимает. 
 
 Иначе, лучше второй вариант.
Похожие вопросы
    Показать ещё