Имеется БД, управляемая SQL Server 2008..
Периодически в ней необходимо INSERT'ить и UPDATE'ить
приличное кол-во данных. Это происходит разово и массово. Десятки миллионов записей в ~ 100 таблицах.

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

Для INSERT'а рассматривается вариант BULK INSERT.
Есть мысль про одну или несколько больших транзакций. Это будет быстрее чем транзакция на каждый запрос?

Посоветуйте plz!!!

Здравствуйте, sergunok, Вы писали:

S>Имеется БД, управляемая SQL Server 2008..
S>Периодически в ней необходимо INSERT'ить и UPDATE'ить
S>приличное кол-во данных. Это происходит разово и массово. Десятки миллионов записей в ~ 100 таблицах.

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

S>Для INSERT'а рассматривается вариант BULK INSERT.
S>Есть мысль про одну или несколько больших транзакций. Это будет быстрее чем транзакция на каждый запрос?

Как вариант:
INSERT — bulk insert большими блоками
UPDATE — INSERT в промежуточную таблицу и оттуда уже UPDATE

Если базу можно держать тормозящей во время апдейта — то можно вливать сразу в боевые таблицы.
Если база в это время активно используется — то влить всё во временные таблицы и оттуда сравнительно небольшими порциями (скажем, мегабайт по пять) быстро апдейтить боевые.

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

В общем, bulk insert — правильная идея

Здравствуйте, sergunok, Вы писали:

S>Посоветуйте plz!!!

Вставлять — решение простое — bulk insert.
С удалением обычно сложнее (bulk delete нету), потому что данные смешаны на страницах и экстентах. Можно использовать partitioning для физической группировки пакетов данных (например, по партиции на месяц исторических данных). Тогда удаление (или переключение в другую таблицу) целого пакета осуществляется простым DDL запросом, почти моментально.

Здравствуйте, Vaso, Вы писали:

V>Вставлять — решение простое — bulk insert.
V>С удалением обычно сложнее (bulk delete нету),

Зато есть представления и замещающие триггеры.
Создать представление, например так:

create view vTabDelete
as
select
    *
from
    Tab
where
    1 = 0

Вешаем на него замещающий триггер на вставку, который будет удалять данные — вот и получился bulk delete

Здравствуйте, _d_m_, Вы писали:

___>Здравствуйте, Vaso, Вы писали:

V>>Вставлять — решение простое — bulk insert.
V>>С удалением обычно сложнее (bulk delete нету),

___>Зато есть представления и замещающие триггеры.
___>Создать представление, например так:
___>

___>create view vTabDelete
___>as
___>select
___>    *
___>from
___>    Tab
___>where
___>    1 = 0
___>

___>Вешаем на него замещающий триггер на вставку, который будет удалять данные — вот и получился bulk delete

точно, сейчас же начну переписывать код!

sergunok пишет:
> Для INSERT'а рассматривается вариант BULK INSERT.

Правильный путь.

> Есть мысль про одну или несколько больших транзакций. Это будет быстрее
> чем транзакция на каждый запрос?
Однозначно быстрее. При этом лучше набивать не все записи
в одну транзакцию большую, а пакетами по скажем 1000-10000 записей.
Если это возможно, конечно.

vmpire пишет:

> UPDATE — INSERT в промежуточную таблицу и оттуда уже UPDATE

А разница-то какая ? По сети данные как передавались, так и
будут передаваться. UPDATE каким был, таким и останется.
А вот время на INSERT в промежуточную таблицу да потом DELETE из
неё -- добавится. Что выигрываем-то ?

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

Ага, а на временную таблицу лог не распространяется как будно ...

Vaso пишет:

> ___>Вешаем на него замещающий триггер на вставку, который будет удалять
> данные — вот и получился bulk delete
>
> точно, сейчас же начну переписывать код!

Не торопись. BULK-операции выигрывают из-за того, что используют
упрощённое логирование, а вот что ты выиграешь от такого подхода -- не
понятно.

Вообще, bulk delete есть, это truncate. Но он всю таблицу удаляет.

Здравствуйте, MasterZiv, Вы писали:

MZ>Vaso пишет:

>> ___>Вешаем на него замещающий триггер на вставку, который будет удалять
>> данные — вот и получился bulk delete
>>
>> точно, сейчас же начну переписывать код!

MZ>Не торопись. BULK-операции выигрывают из-за того, что используют
MZ>упрощённое логирование, а вот что ты выиграешь от такого подхода -- не
MZ>понятно.

Не совсем так. BOL:

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

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

Это раз.
Ну если для тебя неочевиден выигрыш — тогда может надо просто подумать головой? У меня самописная репликация. Для обмена данных мы используем bulk API. На входе два набора данных:
— добавленные и обновленные — льем через замещающий триггер и представление;
— второй набор удаленные строки — опять же через замещающий триггер и представление;

Какую реальную альтернативу ты можешь предложить здесь вместо bulk API? Естественно модель восстановления full без всяких переключений.

MZ>Вообще, bulk delete есть, это truncate. Но он всю таблицу удаляет.

Ну это вобще перл. Чушь.

Здравствуйте, MasterZiv, Вы писали:

>> UPDATE — INSERT в промежуточную таблицу и оттуда уже UPDATE
MZ>А разница-то какая ? По сети данные как передавались, так и
MZ>будут передаваться. UPDATE каким был, таким и останется.
MZ>А вот время на INSERT в промежуточную таблицу да потом DELETE из
MZ>неё -- добавится. Что выигрываем-то ?
Разница такая, что данные по сети во временную таблицу уже приехали один раз и все вставки/обновления из неё будут уже локальными, то есть быстрыми. На временную таблицу не надо вешать ключей и индексов, а вместо DELETE прибивать всё быстро через TRUNCATE.

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

MZ>Ага, а на временную таблицу лог не распространяется как будно ...
В случае bulk insert нагрузка на лог гораздо меньше, чем при обычном обновлении, а в случае модели Simple или bulk logged — вообще минимальная
Поэтому так и быстрее.

vmpire пишет:

> Разница такая, что данные по сети во временную таблицу уже приехали один
> раз и все вставки/обновления из неё будут уже локальными, то есть
> быстрыми. На временную таблицу не надо вешать ключей и индексов, а
> вместо DELETE прибивать всё быстро через TRUNCATE.

Ага, только в варианте без временной таблицы ни этой вставки, ни этого
TRUNCATE-а вообще нету, какой бы он быстрый не был.

Ты посчитай внимательно, пальчики позагибай, делаем это , потом это,
потом это ... и -- в двух вариантах. Увидешь, что в одном (твоём)
пальчиков загнётся больше.

_d_m_ пишет:

> Какую реальную альтернативу ты можешь предложить здесь вместо bulk API?
> Естественно модель восстановления full без всяких переключений.

Да какой же будет bulk insert в триггере-то ?
Или bulk delete.

Здравствуйте, _d_m_, Вы писали:

___>- второй набор удаленные строки — опять же через замещающий триггер и представление;

___>Какую реальную альтернативу ты можешь предложить здесь вместо bulk API? Естественно модель восстановления full без всяких переключений.

MZ>>Вообще, bulk delete есть, это truncate. Но он всю таблицу удаляет.

___>Ну это вобще перл. Чушь.

Так ты серьезно что-ли про удаление замещающим триггером? Я думал шутим ...
Вообще <delete> всегда выполняется по-моему в обычной транзакции (в смысле сам <delete> не оптимизируется).
Или вы массивную вставку симулируете чтобы заставить сервер подхватить оптимизированную транзакцию?
Реально ускоряется, исследовали?
Вот официальный список операций которые можно ускорить: <http://msdn.microsoft.com/ru-ru/library/ms191244.aspx&gt;, delete туда точно не входит.
ПС
А <truncate> можно назвать балк дилитом, почему нет, только слишком уж балк.

S>Периодически в ней необходимо INSERT'ить и UPDATE'ить
S>приличное кол-во данных. Это происходит разово и массово. Десятки миллионов записей в ~ 100 таблицах.
S>Каким образом обеспечить наибольшую производительность перечисленных запросов?
S>Для INSERT'а рассматривается вариант BULK INSERT.
S>Есть мысль про одну или несколько больших транзакций. Это будет быстрее чем транзакция на каждый запрос?

Каждая транзакция связана с блокировкой записей и логированием изменений. Это собственно и создает тормоза. Даже одиночная команда — это неявная транзакция. Массивная вставка работает быстро, т.к. не требует дополнительных затрат, кроме записи в базу. Чтобы ускорить UPDATE необходимо ускорить или упростить операции, которые входят в UPDATE. Например, можно с помощью хинта TABLOCK блокировать целиком таблицу, с помощью индексов можно ускорить поиск нужных записей для UPDATE.

Здравствуйте, MasterZiv, Вы писали:

>> Разница такая, что данные по сети во временную таблицу уже приехали один
>> раз и все вставки/обновления из неё будут уже локальными, то есть
>> быстрыми. На временную таблицу не надо вешать ключей и индексов, а
>> вместо DELETE прибивать всё быстро через TRUNCATE.

MZ>Ага, только в варианте без временной таблицы ни этой вставки, ни этого
MZ>TRUNCATE-а вообще нету, какой бы он быстрый не был.

MZ>Ты посчитай внимательно, пальчики позагибай, делаем это , потом это,
MZ>потом это ... и -- в двух вариантах. Увидешь, что в одном (твоём)
MZ>пальчиков загнётся больше.
А мы оптимизируем пальчики, время вставки или что-то ещё?
В случае одного инсерта быстрее по общему времени будет, конечно, вливать всё сразу в боевые таблицы. Только они при этом будут сильно полочены всё это время, что при нагруженной базе не есть хорошо.
Но если ты подумаешь немного шире и рассмотришь проблему целиком, то увидишь, что при прямых операциях с боевыми таблицами update будет умирать в смысле скорости.
Потому что без временной таблицы запросов на сервер будет (загибай пальчики) 1. bulk insert 2. update первой строки, 3. update второй строки, ... в общем, пальцев не хватит.
В случае замещающего триггера для update запрос на сервер будет один, но при этом всё будет тормозить уже на триггере и таблица будет лочится ещё сильнее и дольше. Попробуй, вызови триггер хотя бв пару миллионов раз.
А с временной таблицей общее время будет, естественно, больше но при этом рабочая таблица будет лочится на минимальное время. Конечно, если к этой базе обращаются пару раз в сутки, то можно не парится с оптимизацией и вливать всё напрямую.
Но всё это имеет смысл только если вставка происходит извне.

В случае если источник это тоже база MSSQL и она на том же сервере или на linked сервере, то проще и быстрее всего всосать данные оттуда оператором merge с доступом по полному имени таблицы.

vmpire пишет:

> В случае одного инсерта быстрее по общему времени будет, конечно,
> вливать всё сразу в боевые таблицы. Только они при этом будут сильно
> полочены всё это время, что при нагруженной базе не есть хорошо.

INSERT-ами ? Что в MSSQL 2008 INSEERT-ы ещё кого-то лочат ?
Это было уже в 6.5 -- pseudo row level locking for INSERT.
Что, отменили ?

> Потому что без временной таблицы запросов на сервер будет (загибай
> пальчики) 1. bulk insert 2. update первой строки, 3. update второй
> строки, ... в общем, пальцев не хватит.

Это куда bulk insert БЕЗ временной таблицы ?

Виктор Юров пишет:
Массивная вставка работает быстро, т.к. не
> требует дополнительных затрат, кроме записи в базу.

Я хочу уточнить. Записть при массовом INSERT или UPDATE
ведётся точно так же, как и при одиночном, как в БД,
так и в лог. Только при этом запись в лог как правило
всегда физическая, а в БД -- логическая, т.е. только
в памяти, а в физическую перерастает только когда
кэш кончается.

Но транзакция состоит из трёх основных фаз

-- начало транзакции
-- операции транзакции
-- конец транзакции.

При массовых операциях начало и конец
транзакции участвуют в операции только один
раз, для всей транзакции (хотя часть работы
конца транзакции частично переносится в операции
транзакции). Вот на этом и происходит экономия.
При чём существенная.

Поясню. Конец транзакции -- это завершение
записи в лог транзакций. При этом durability
как правило требует, чтобы последняя страница
лога физически записалась на диск. Некоторые
алгоритмы предполагают даже, что это будет
сделано несколько раз. Если у нас 5 операций
по одной записи, то у нас будет 5 commit-ов
и n*5 сбросов последней страницы лога на диск.
Если будет 1 транзакция по 5 записей, то
будет 5 записей в последнюю страницу лога,
которые скорей всего влезут в одну страницу лога,
и потом 1 commit и n*1 сборосов этой страницы лога
на диск. В итоге -- в 5 раз меньше IO.

Если будет 1 тарнзакция по 10000 записей,
то записи, записываясь в лог, естественно,
в одну страницу не влезут, и по мере заполнения
лога страницы будут записываться на диск.
Но -- только по переполнению последней страницы
лога. И потом последняя активная страница
лога будет сброшена на диск по commit-у.

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

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

Здравствуйте, MasterZiv, Вы писали:

MZ>vmpire пишет:

>> В случае одного инсерта быстрее по общему времени будет, конечно,
>> вливать всё сразу в боевые таблицы. Только они при этом будут сильно
>> полочены всё это время, что при нагруженной базе не есть хорошо.

MZ>INSERT-ами ? Что в MSSQL 2008 INSEERT-ы ещё кого-то лочат ?
Представьте себе. Иногда они лочат страницы, иногда extentы, иногда и всю таблицу. Вы же в курсе, надеюсь, что новые записи при кластерном индексе могут вставляться в самые разные места таблицы?

MZ>Это было уже в 6.5 -- pseudo row level locking for INSERT.
MZ>Что, отменили ?
pseudo row level locking отменили давно. Речь не об этом.

>> Потому что без временной таблицы запросов на сервер будет (загибай
>> пальчики) 1. bulk insert 2. update первой строки, 3. update второй
>> строки, ... в общем, пальцев не хватит.

MZ>Это куда bulk insert БЕЗ временной таблицы ?
Вы таки не поверите, но bulk insert отлично умеет вставлять в постоянные таблицы

Здравствуйте, MasterZiv, Вы писали:

MZ>_d_m_ пишет:

>> Какую реальную альтернативу ты можешь предложить здесь вместо bulk API?
>> Естественно модель восстановления full без всяких переключений.

MZ>Да какой же будет bulk insert в триггере-то ?
MZ>Или bulk delete.

Ты лучше на вопрос ответь — альтернатива моему решению. А я скажу, чем она хуже.

Здравствуйте, Vaso, Вы писали:

V>Так ты серьезно что-ли про удаление замещающим триггером? Я думал шутим ...
V>Вообще <delete> всегда выполняется по-моему в обычной транзакции (в смысле сам <delete> не оптимизируется).
V>Или вы массивную вставку симулируете чтобы заставить сервер подхватить оптимизированную транзакцию?
V>Реально ускоряется, исследовали?

Скорость здесь не на первом месте. См. выше.