Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вставка большого количества записей с множеством колонок  [new]
tomasnig
Member

Откуда:
Сообщений: 5
Есть таблица в MS SQL с 90+ колонками, большинство из которых строки. Проблема в медленной вставке данных в эту таблицу.

Запрос типа
INSERT INTO <table> (<column 1>, ..., <column 90>) VALUES
(<row 1>), (<row 1>), ..., (<row 1000>)

выполняется 5656 ms.

В то время как 1000 раз выполнение следующего запроса занимает 2245 ms.

INSERT INTO <table> (<column 1>, ..., <column 90>) VALUES
(<row>)

Индексов, тригеров на таблице нет, есть только 2 foreign keys.

Какие есть способы улучшить производительность?

Заранее спасибо за помощь.
26 июн 17, 10:42    [20589897]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
tomasnig,

INSERT INTO ... WITH (TABLOCK)

читайте о минимальном протоколировании.
26 июн 17, 11:22    [20590030]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
tomasnig
Member

Откуда:
Сообщений: 5
WITH (TABLOCK) не помог, уже пробовали.
26 июн 17, 11:51    [20590133]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints
ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL

До и после. А что, 5с на вставку 100 записей неизвестного размера в неизвестного размера таблицу без индексов и тригеров - это очень долго?
26 июн 17, 11:55    [20590141]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
tomasnig
WITH (TABLOCK) не помог, уже пробовали.


При полной модели восстановления этим вы врят ли добьетесь существенных успехов.
26 июн 17, 12:00    [20590158]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
Alexander Us
Member

Откуда:
Сообщений: 1093
tomasnig,

Т.е. что является источником данных: вставки идут из приложения или из прямо из SSMS или SQL-Джоба?
Возможна ли вставка группами строк?
26 июн 17, 12:25    [20590231]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
Alexander Us
Member

Откуда:
Сообщений: 1093
tomasnig,

Какая примерно длинна у 2-3 самых длинных текстовых полей?
26 июн 17, 12:28    [20590244]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
tomasnig
Member

Откуда:
Сообщений: 5
Alexander Us,

я вставляю из приложения через Dapper. Но в SMSS выполнение занимает не намного меньше времени.

Самые длинные поля 255 символов (их 2 штуки), одно поле на 128 символов. Но большинство меньше 10 символов.
26 июн 17, 14:02    [20590630]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Если SQL Server 2014+

https://habrahabr.ru/post/303156/

Если 2012 и ниже, формируйте строки в батчи. Да и DDL таблицы можно показать для интереса.
26 июн 17, 14:07    [20590650]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
Alexander Us
Member

Откуда:
Сообщений: 1093
tomasnig,

к сож. не знаю Dapper.

хотел сказать, что:

- вставка группами строк с использованием bulk insert (в .NET класс SqlBulkInsert) ускорит вставки значительно. Начиная с N строк в группе. Чем больше группа, тем меньше врени на каждую строку.

- проверьте не используете ли Вы тип данных text или varchar(max).
С ними вставки могут быьт медленее.

- данные, которые Вы вставляете используются реляционно?
Или это только какой-то лог, который пишется и затем перекопируется куда нибудь?
26 июн 17, 14:43    [20590804]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
tomasnig
Member

Откуда:
Сообщений: 5
Alexander Us,

Спасибо, я сейчас попробую SqlBulkInsert.

В таблице нет ни text, ни varchar(max).

В этой таблице хранится основная бизнес сущность.
26 июн 17, 14:49    [20590828]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
Alexander Us
Member

Откуда:
Сообщений: 1093
tomasnig,

попробуйте.

SqlBulkInsert вещь замечательная, но требует некоторой привычки.

Как источник используйте либо DataTable, либо имплементируйте IDataReader (как правило, там достаточно имплементировать лишь пару функций)

Будьте осторожны с ColumnMapping - он CaseSensetive. Я прошел к тому, что обычно задаю явно, с именами полей.

Учтите, он не обрезает излишне длинные поля, а выкидывает ошибку уже при вставке.
26 июн 17, 15:11    [20590910]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
tomasnig
Member

Откуда:
Сообщений: 5
Alexander Us,

Спасибо большое. Используя SqlBulkCopy удалось добиться результата в 459 ms на 1000, а на 10 000 записей - 2050 ms.
26 июн 17, 18:26    [20591764]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества записей с множеством колонок  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
tomasnig,

SqlBulkInsert - ровно тоже самое делает пакет SSIS.
26 июн 17, 19:14    [20591914]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить