Доброго коллеги.

Не имел раньше дел с большими БД.
Надо перенести данные из файлового представления в MSSQL.
Не знаю нормально или нет если 1200 записей инсертится в БД 6 секунд?
файл в 1200 строк парсится 50 мС а запись столько же в БД 6 сек кажется мне многовато.
В инсерте 16 колонок 3 real, 3 char, 4 int, остальные varchar.
инсертятся построчно, т.е. на одну запись один инсерт.
В таблице сейчас под 150 миллионов записей.

Посмотри в сторону Bulk insert.
Быстрее способа нет.

1. CHECKи. Например, контроль уникальности. Пока 150 млн записей не проверится на уникальность - никакого инсерта. Естественно CHECK с индексом быстрее;
2. TRIGGERы. То же самое, триггер выстреливает, возможно что-то распихивает по таблицам, те в свою очередь тоже попадают на CHECKи;
3. Обновление индексов. Немного, но время подъедает.

1. Определение таблицы куда идёт Insert, приведи.

2. Плюс реальный план в xml-виде.

3. Но скорее всего надо обновить статистику.





Исправлено 1 раз(а). Последнее : PaulWist, 31.03.21 13:59

Таблица простая. Тригеров нет. Индекс по полю ID. Уникальность не проверяется.

[attachment 35203 TD1.zip]

ssa
Посмотри в сторону Bulk insert.
Быстрее способа нет.

Я правильно понял что Bulk insert только из файла данных может делатся?
Т.е. мне надо данные снова выгрузить в текстовый файл и потом уже из него делать Bulk insert?
Выгружать получается лучше на сервер на котором крутиться MSSQL?

1. План TRIVIAL, те вставка быстрее не будет.

2. Выложи таблицу и индекс как Create.

3. ID - это автоинкриментное поле?

_vit
Таблица простая. Тригеров нет. Индекс по полю ID. Уникальность не проверяется.

Уникальность проверяется. Вставка в примере идёт с указанием ID. Из плана я не совсем понял, подобрался индекс или нет, но статистику для такого случая обновить следует.

_vit

ssa
Посмотри в сторону Bulk insert.
Быстрее способа нет.

Я правильно понял что Bulk insert только из файла данных может делатся?

Да. Из csv или собственного bulk-формата.

Цитата:

Т.е. мне надо данные снова выгрузить в текстовый файл и потом уже из него делать Bulk insert?

Да.

Цитата:

Выгружать получается лучше на сервер на котором крутиться MSSQL?

Да.
В любом случае перед вставкой все индексы желательно отключать и обратно включать уже после вставки.

ID - не автоинкрементное.

CREATE TABLE

Если таблица создаётся под загрузку (а не дополняется), то чуть-чуть ускорит процесс если индекс НЕ создавать заранее, а создать после загрузки. Конечно же тогда контроль уникальности данных уж целиком на твоей стороне будет.
bulk операции как раз и предназначены для сценариев массовой загрузки. "обычные" Insert, тем более одиночные, по определению будут медленнее.

pasha_usue

_vit
Таблица простая. Тригеров нет. Индекс по полю ID. Уникальность не проверяется.

Уникальность проверяется. Вставка в примере идёт с указанием ID. Из плана я не совсем понял, подобрался индекс или нет, но статистику для такого случая обновить следует.

Проверяется чтобы было значение. Уникальность нет.

Igor Korolyov
Если таблица создаётся под загрузку (а не дополняется), то чуть-чуть ускорит процесс если индекс НЕ создавать заранее, а создать после загрузки. Конечно же тогда контроль уникальности данных уж целиком на твоей стороне будет.
bulk операции как раз и предназначены для сценариев массовой загрузки. "обычные" Insert, тем более одиночные, по определению будут медленнее.

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

Контроль данных производиться программно перед инсертом.



Исправлено 2 раз(а). Последнее : _vit, 01.04.21 14:16

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

Да, staging таблицы это, не побоюсь этого слова, ключевая часть ETL процессов. "сырые" данные туда помещаем без валидаций и прочего, потом либо в другое временное место перегоняем, либо, если уже и так достаточно "хорошо", в собственно конечное хранилище.
Надо глянуть в доке MSSQL, там вероятно есть опции для оптимизации такого рода "массовых перегонов данных", если скорости просто insert ... select ... будет недостаточно.

_vit
ID - не автоинкрементное.

Я не точно выразился, ID при вставке монотонно возрастает-убывает или же ID принимает значения от 1 до 150 млн?

Я перегонял так,

1. Ручная транзакция
2. Перегон данных (обычно во временную таблицу - потом merge)
3. Commit

те получаешь одну транзакцию, а не 1200 шт.





Исправлено 1 раз(а). Последнее : PaulWist, 01.04.21 16:02

_vit
Таблица уже в использовании. Паралельно ведется загрузка в ретроспективе.
Наверно будет быстрее грузить ретроспективу в отдельную таблицу (может даже на другой сервер) а потом объединять по частям.(?)

Если количество новых записей существенно больше уже существующих, то использовал такой прием

1. Создается новая таблица куда заливаются новые данные
2. Из используемой таблицы все существующие записи также копируются в эту новую таблицу
3. Старая таблица удаляется (или переименовывается)
4. Новая таблица переименовывается в используемую таблицу

С другой стороны, если Вы просто архив загружаете, то чего торопиться-то? Грузите порциями, скажем, по 100 тысяч записей. Пусть неделю займет, и что? Архив же... Тут, кстати, таблица в той же базе будет удобнее. Можно будет при заливке очередной порции проверять, что повторно ранее загруженные записи не заливаются


Да. Отключение индекса на время загрузки и пересоздание индекса после загрузки несколько ускоряет процесс. Не сильно, но несколько процентов так можно выиграть. Катастрофически тормозит загрузку кластерный индекс. Но у Вас его нет, а обычные индексы - не особо сильно влияют...

PaulWist

_vit
ID - не автоинкрементное.

Я не точно выразился, ID при вставке монотонно возрастает-убывает или же ID принимает значения от 1 до 150 млн?

Я перегонял так,

1. Ручная транзакция
2. Перегон данных (обычно во временную таблицу - потом merge)
3. Commit

те получаешь одну транзакцию, а не 1200 шт.

ВАУ!

С использованием транзакции скорость увеличилась почти на порядок!
Спасибо! Ты настоящий друг!

А попозже попробую Bulk insert если админы разрешат класть файлы на сервер БД.

Лена или Сергей подправте пожалуйста карму PaulWist это я промахнулся.

_vit
файл в 1200 строк парсится 50 мС а запись столько же в БД 6 сек кажется мне многовато.
инсертятся построчно, т.е. на одну запись один инсерт.
В таблице сейчас под 150 миллионов записей.

_vit
А попозже попробую Bulk insert если админы разрешат класть файлы на сервер БД.

Хм...
А ты откуда грузишь ? Я подумал из файла...

_vit
А попозже попробую Bulk insert если админы разрешат класть файлы на сервер БД.

Не обязательно на сервер. В место, доступное серверу.

ВладимирС

_vit
файл в 1200 строк парсится 50 мС а запись столько же в БД 6 сек кажется мне многовато.
инсертятся построчно, т.е. на одну запись один инсерт.
В таблице сейчас под 150 миллионов записей.

_vit
А попозже попробую Bulk insert если админы разрешат класть файлы на сервер БД.

Хм...
А ты откуда грузишь ? Я подумал из файла...

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