Имеется БД, управляемая 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>Реально ускоряется, исследовали?

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

_d_m_ пишет:

> MZ>Да какой же будет bulk insert в триггере-то ?
> MZ>Или bulk delete.
>
> Ты лучше на вопрос ответь — альтернатива моему решению. А я скажу, чем
> она хуже.

Я не понимаю, в чём решение.

Вот оно:

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

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


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


Т.е.
-- мы создаём View, ничего не возвращающий, содержащий только PK таблицы
(можно и другие поля, но не обязательно)
-- Вместо того, чтобы удалять данные из основной таблицы, мы вставляем
данные во VIEW
-- На VIEW висит триггер INSTEAD OF INSERT, который вставленные в эту таблицу
записи удаляет из основной таблицы.
(тут надо сказать, что эта операция уже не bulk, потому что серверу для
формирования INSERTED/DELETED надо физически данные записать куда-то.
Это куда-то -- лог и/или tempdb)

(правильно ли я понял предлагаемое решение ?)

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

Так в чём выигрыш-то ? Чем это лучше обычного DELETE на
ту же таблицу, выполненный непосредственно с клиента ?
или вызова хранимой процедуры ?

Операция BULK INSERT сама разбивает все записи на пакеты, размер которых задается параметром BATCHSIZE (или KILOBYTES_PER_BATCH). Для каждого пакета создается своя транзакция, поэтому для оптимального быстродействия нужно указать такой размер пакета, чтобы транзакция помещалась в оперативную память без особых напрягов. ПО УМОЛЧАНИЮ BULK INSERT выполняет всю вставку в одном пакете, что приводит к записи на диск в случае нехватки памяти.

Аноним это я

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

MZ>Так в чём выигрыш-то ? Чем это лучше обычного DELETE на
MZ>ту же таблицу, выполненный непосредственно с клиента ?
MZ>или вызова хранимой процедуры ?

На вход delete подается множество ключей строк, которые надо удалить. Как выполнить такой delete с клиента?

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

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

Один апдейт, обновляющий 1000 записей, выполняется быстрее , чем 1000 отдельный апдейтов.
Закачав bulk insert-ом новые значения во временную таблицу можно будет обновить все записи за одну операцию.

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

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

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

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

Имею опыт оптимизации данной ситуации.
Первое, что нужно сделать — "пересадить" инстанс из аппаратного узла "NODE 0", допустим на "NODE 1".
Второе — включить режим AWE. Это делается с помощью следующего кода:

sp_configure 'awe enabled', 1
RECONFIGURE
GO

Третье, что нужно сделать — обеспечить привязку потоков ввода-вывода SQL Server'. Это делается с помощью параметра affinity I/O mask. Его надо установить в 1.

_d_m_ пишет:

> На вход delete подается множество ключей строк, которые надо удалить.
> Как выполнить такой delete с клиента?

А, я понял, ты знаешь MSSQL, но не знаешь SQL.

delete [from] MyTable t
where t.pk in ( 1,2,34,45,6454,2000 )


или

delete [from] MyTable t
where t.pkfld1 = 'a' and t.pkfld2 = 1
or t.pkfld1 = 'b' and t.pkfld2 = 2
or t.pkfld1 = 'c' and t.pkfld2 = 3
...

Аноним 315 пишет:

> Операция BULK INSERT сама разбивает все записи на пакеты, размер которых
> задается параметром BATCHSIZE (или KILOBYTES_PER_BATCH). Для каждого
> пакета создается своя транзакция, поэтому для оптимального

Ну и ?

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

MZ>_d_m_ пишет:

>> На вход delete подается множество ключей строк, которые надо удалить.
>> Как выполнить такой delete с клиента?

MZ>А, я понял, ты знаешь MSSQL, но не знаешь SQL.

MZ>delete [from] MyTable t
MZ>where t.pk in ( 1,2,34,45,6454,2000 )

MZ>или

MZ>delete [from] MyTable t
MZ>where t.pkfld1 = 'a' and t.pkfld2 = 1
MZ> or t.pkfld1 = 'b' and t.pkfld2 = 2
MZ> or t.pkfld1 = 'c' and t.pkfld2 = 3
MZ>...



Ключей 100000 — как быть?
1) Сразу напарываемся на ограничение размера батча.
2) Мне этот оператор надо в памяти конструировать в виде строки.

В случае использования .Net класса SqlBulkCopy я просто открываю DataReader на одном сервере и передаю его на выполнение в SqlBulkCopy на другом сервере.

_d_m_ пишет:

> Ключей 100000 — как быть?

А ты поглянь на спецификации ограничений, там кол-во
элементов в IN очень-очень большое. Вполне возможно, что
все 100000 и влезут.

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

Ну и остаётся вариант с OR -ами.

И потом, такие большие пачки данных так и так плохо удалять
одним махом.

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

MZ>_d_m_ пишет:

>> Ключей 100000 — как быть?

MZ>А ты поглянь на спецификации ограничений, там кол-во
MZ>элементов в IN очень-очень большое. Вполне возможно, что
MZ>все 100000 и влезут.

Я же говорю, на ограничение размера батча напарываемся: макс размер батча = размер сетевого пакета * 8. Да и преобразование всего этого хозяйства в строку тоже небесплатно.

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

MZ>Ну и остаётся вариант с OR -ами.

Я же говорю, на ограничение размера батча напарываемся: макс размер батча = размер сетевого пакета * 8. Да и преобразование всего этого хозяйства в строку тоже небесплатно.

MZ>И потом, такие большие пачки данных так и так плохо удалять
MZ>одним махом.

Ну тем не менее — на все-провсе один оператор: delete ... from ... inner join inserted

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

___>Ключей 100000 — как быть?
___>1) Сразу напарываемся на ограничение размера батча.
Дробить на куски которые входят.
___>2) Мне этот оператор надо в памяти конструировать в виде строки.
Параметры отменили? Генерация "delete from T where id in (@p1, @p2, ....., @pN ) настолько дешевая операция, что ей можно пренебречь (хинт: генерируем всю строку заранее, потом берем начало с нужным числом параметров и добавляем закрывающую скобку).

___>В случае использования .Net класса SqlBulkCopy я просто открываю DataReader на одном сервере и передаю его на выполнение в SqlBulkCopy на другом сервере.

Как обычно есть плюсы и минусы:

+ быстрее (возможно) доставляем данные к серверу, это преимущество возможно сыграет при плохих каналах
+ проще код клиента
— сложнее код на стороне сервера
— сервер может сделать поиск и удаление нескольких записей сразу более оптимальным чем удаление каждой из них по одному

Хотя насчет массового insert or update идея BULK INSERT + INSTEAD OF INSERT TRIGGER интересная.

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

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

___>>Ключей 100000 — как быть?
___>>1) Сразу напарываемся на ограничение размера батча.
Z>Дробить на куски которые входят.

Усложнение кода.

___>>2) Мне этот оператор надо в памяти конструировать в виде строки.
Z>Параметры отменили? Генерация "delete from T where id in (@p1, @p2, ....., @pN ) настолько дешевая операция, что ей можно пренебречь (хинт: генерируем всю строку заранее, потом берем начало с нужным числом параметров и добавляем закрывающую скобку).

Ага, а потом эти параметры надо еще присвоить. Перебрать эти записи из рекордсета... Нафига? Когда есть простой вариант SqlBulkCopy.

___>>В случае использования .Net класса SqlBulkCopy я просто открываю DataReader на одном сервере и передаю его на выполнение в SqlBulkCopy на другом сервере.

Z>Как обычно есть плюсы и минусы:

Z>+ быстрее (возможно) доставляем данные к серверу, это преимущество возможно сыграет при плохих каналах
Z>+ проще код клиента
Z>- сложнее код на стороне сервера

Какие сложности то? В коде замещающего триггера:
1) для новых и обновленных данных: два оператора — update/insert или один merge для MS SQL 2008;
2) для удаленных: один delete

Z>- сервер может сделать поиск и удаление нескольких записей сразу более оптимальным чем удаление каждой из них по одному

Эээ... Чего?

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

___>Какие сложности то? В коде замещающего триггера:
___>1) для новых и обновленных данных: два оператора — update/insert или один merge для MS SQL 2008;
___>2) для удаленных: один delete

Для каждой таблицы. По мне так это совсем не проще, десятка строк в коде репликации для генерации делита.

Z>>- сервер может сделать поиск и удаление нескольких записей сразу более оптимальным чем удаление каждой из них по одному

___>Эээ... Чего?

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

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

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

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

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

S>Посоветуйте plz!!!
Даже интересно как с этим делом обстоит в 2008 SQL Server, но попробуй удалить индексы, а после вставки/обновления их пересоздать.

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

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

___>>Какие сложности то? В коде замещающего триггера:
___>>1) для новых и обновленных данных: два оператора — update/insert или один merge для MS SQL 2008;
___>>2) для удаленных: один delete

Z>Для каждой таблицы. По мне так это совсем не проще, десятка строк в коде репликации для генерации делита.

Ну естественно мы рассматриваем таблицу. Но эту проблему мы решаем кодогенерацией.

Z>>>- сервер может сделать поиск и удаление нескольких записей сразу более оптимальным чем удаление каждой из них по одному

___>>Эээ... Чего?

Z>Страница на которой мы уже удаляли запись и будем удалять еще одну может быть сброшена на диск, так как серверу неоткуда узнать, что в этом же контексте мы удалим еще одну запись с нее же. Если же сервер знает про обе записи — он удалит сразу обе, повторное обращение к странице не потребуется.

Спасибо Капитан Очевидность. Ну и к чему это ты вообще?

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

Z>>Для каждой таблицы. По мне так это совсем не проще, десятка строк в коде репликации для генерации делита.

___>Ну естественно мы рассматриваем таблицу. Но эту проблему мы решаем кодогенерацией.

Ага, т.е. чтобы не писать 10 строк мы напишем тыщу, но автоматизируем это кодогенерацией, интересный ход мысли.

Z>>>>- сервер может сделать поиск и удаление нескольких записей сразу более оптимальным чем удаление каждой из них по одному

___>>>Эээ... Чего?

Z>>Страница на которой мы уже удаляли запись и будем удалять еще одну может быть сброшена на диск, так как серверу неоткуда узнать, что в этом же контексте мы удалим еще одну запись с нее же. Если же сервер знает про обе записи — он удалит сразу обе, повторное обращение к странице не потребуется.

___>Спасибо Капитан Очевидность. Ну и к чему это ты вообще?

К выделенному естественно. Как видно по истории, я тоже считал это очевидным и не стал разжевывать сразу.

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

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

Z>>>Для каждой таблицы. По мне так это совсем не проще, десятка строк в коде репликации для генерации делита.

___>>Ну естественно мы рассматриваем таблицу. Но эту проблему мы решаем кодогенерацией.

Z>Ага, т.е. чтобы не писать 10 строк мы напишем тыщу, но автоматизируем это кодогенерацией, интересный ход мысли.

Не надо передергивать. Какую нафиг тыщу? Пара представлений на таблицу с тригерами из одного/двух операторов каждый.

Z>>>>>- сервер может сделать поиск и удаление нескольких записей сразу более оптимальным чем удаление каждой из них по одному

___>>>>Эээ... Чего?

Z>>>Страница на которой мы уже удаляли запись и будем удалять еще одну может быть сброшена на диск, так как серверу неоткуда узнать, что в этом же контексте мы удалим еще одну запись с нее же. Если же сервер знает про обе записи — он удалит сразу обе, повторное обращение к странице не потребуется.

___>>Спасибо Капитан Очевидность. Ну и к чему это ты вообще?

Z>К выделенному естественно. Как видно по истории, я тоже считал это очевидным и не стал разжевывать сразу.

Собственно какое отношение это имеет к предложенному мною подходу? Где там удаляется по одной записи? Донеси до нас свою мысль.

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

___>Не надо передергивать. Какую нафиг тыщу? Пара представлений на таблицу с тригерами из одного/двух операторов каждый.

Итого 3 строки на таблицу. Таблиц сколько?

Z>>К выделенному естественно. Как видно по истории, я тоже считал это очевидным и не стал разжевывать сразу.

___>Собственно какое отношение это имеет к предложенному мною подходу? Где там удаляется по одной записи? Донеси до нас свою мысль.

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

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

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

___>>Не надо передергивать. Какую нафиг тыщу? Пара представлений на таблицу с тригерами из одного/двух операторов каждый.

Z>Итого 3 строки на таблицу. Таблиц сколько?

А какая разница? Я же сказал — кодогенерация.

Z>>>К выделенному естественно. Как видно по истории, я тоже считал это очевидным и не стал разжевывать сразу.

___>>Собственно какое отношение это имеет к предложенному мною подходу? Где там удаляется по одной записи? Донеси до нас свою мысль.

Z>Опять вызываем Капитана Очевидность? Сколько записей удаляет триггер за одну операцию? Он выполняет операцию поиска и записи удаления для каждого PK. По одной записи.

Ах вот она где собака порылась! Ты оказывацо не знаешь как работает триггер. Так вот триггер выполняется ровно один раз на ровно один оператор.

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

___>А какая разница? Я же сказал — кодогенерация.

Опять по кругу? Приплетать кодогенреацию вместо того чтобы написать 10 строк кода руками?

___>Ах вот она где собака порылась! Ты оказывацо не знаешь как работает триггер. Так вот триггер выполняется ровно один раз на ровно один оператор.

Код триггера в студию.

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

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

___>>А какая разница? Я же сказал — кодогенерация.

Z>Опять по кругу? Приплетать кодогенреацию вместо того чтобы написать 10 строк кода руками?

Да что ты нас стращаешь? Каких еще нафиг 10 строк. Проходили — нет проблем. В нашем случае стандартная репликация не подходит. Так что для кодогенерации нарисовал один класс — используются шаблоны с параметрами.

___>>Ах вот она где собака порылась! Ты оказывацо не знаешь как работает триггер. Так вот триггер выполняется ровно один раз на ровно один оператор.

Z>Код триггера в студию.

Тебе надо не код триггера смотреть, а документацию

Например:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[пРепликация_Товары_Удал_ЗамВст] on [dbo].[пРепликация_Товары_Удал]
--# with encryption
instead of insert
as begin
    set nocount on;

    delete
        dbo.Товары
    from
        dbo.Товары т
            inner join
        inserted вст
                on
                    т.Код=вст.Код
    ;

end

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

___>Да что ты нас стращаешь? Каких еще нафиг 10 строк. Проходили — нет проблем. В нашем случае стандартная репликация не подходит. Так что для кодогенерации нарисовал один класс — используются шаблоны с параметрами.

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

___>

___>SET ANSI_NULLS ON
___>GO
___>SET QUOTED_IDENTIFIER ON
___>GO
___>ALTER trigger [dbo].[пРепликация_Товары_Удал_ЗамВст] on [dbo].[пРепликация_Товары_Удал]
___>--# with encryption
___>instead of insert
___>as begin
___>    set nocount on;

___>    delete
___>        dbo.Товары
___>    from
___>        dbo.Товары т
___>            inner join
___>        inserted вст
___>                on
___>                    т.Код=вст.Код
___>    ;

___>end
___>

Ага, statement level trigger.
Тут еще лучше, сначала мы вставим данные куда-то. Операция недешевая, мы либо выкинем из памяти нужные данные, либо вообще не влезем в память и будем скидывать вставляемые данные на диск, а потом читать их оттуда. Очень здорово. Надеюсь вы понимаете, что это решение не масштабируется. Оно жрет ограниченные ресурсы O(N).

Это все ради того, чтобы не генерировать delete from?

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

Z>Ага, statement level trigger.
Z>Тут еще лучше, сначала мы вставим данные куда-то. Операция недешевая, мы либо выкинем из памяти нужные данные, либо вообще не влезем в память и будем скидывать вставляемые данные на диск, а потом читать их оттуда. Очень здорово. Надеюсь вы понимаете, что это решение не масштабируется. Оно жрет ограниченные ресурсы O(N).


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

Z>Это все ради того, чтобы не генерировать delete from?

Заааачеееем? Нафиг мне его генерировать. Есть решение проще и легковесней — я про него как раз и говорю. Если следовать твоей логике — то и поддержка версионных уровней изоляций "не масштабируется и жрет ограниченные ресурсы".

PS: Кстати, удаление как и обновление может проводится по частям, например по 50 тысяч строк. Оператор TOP рулит.