Массовая стратегия вставки из c # в SQL Server
==============================================

 В нашем текущем проекте клиенты отправят коллекцию сложных / вложенных сообщений в нашу систему. Частота этих сообщений составляет ок. 1000-2000 msg / per seconds. 

 Эти сложные объекты содержат данные транзакции (добавляются), а также основные данные (которые будут добавлены, если не найдены). Но вместо передачи идентификаторов основных данных клиент передает столбец «имя». 

 Система проверяет наличие основных данных для этих имен. Если найдено, он использует идентификаторы из базы данных, сначала создайте эти основные данные, а затем используйте эти идентификаторы. 

 После устранения основных идентификаторов данных система вставляет транзакционные данные в базу данных SQL Server (с использованием идентификаторов основных данных). Количество основных объектов на сообщение составляет около 15-20. 

 Ниже приведены некоторые стратегии, которые мы можем принять. 

1. Мы можем сначала разрешить главные идентификаторы из нашего кода C # (и вставляем основные данные, если они не найдены) и хранить эти идентификаторы в кеше C #. Как только все идентификаторы будут разрешены, мы можем вставлять транзакционные данные в `SqlBulkCopy` используя класс `SqlBulkCopy` . Мы можем ударить по базе данных 15 раз, чтобы получить идентификаторы для разных объектов, а затем ударить базу данных еще раз, чтобы вставить окончательные данные. Мы можем использовать одно и то же соединение, которое закроет его после выполнения всей этой обработки.
2. Мы можем отправлять все эти сообщения, содержащие основные данные и транзакционные данные, в однократном попадании в базу данных (в виде нескольких ТВП), а затем внутри хранимой процедуры, сначала создавать основные данные для отсутствующих, а затем вставлять транзакционные данные.

 Может ли кто-нибудь предложить лучший подход в этом случае? 

 *Из-за некоторой проблемы конфиденциальности я не могу поделиться фактической структурой объекта. Но вот гипотетическая структура объекта, которая очень близка к нашему бизнес-объекту* . 

 Одно из таких сообщений будет содержать информацию об одном продукте (его основных данных) и его ценовой информации (данные транзакции) от разных поставщиков: 

 **Основные данные (которые необходимо добавить, если они не найдены)** 

 Название продукта: ABC, ProductCateory: XYZ, Производитель: XXX и некоторые другие другие детали (количество свойств находится в диапазоне 15-20). 

 **Данные транзакции (которые всегда будут добавляться)** 

 Имя поставщика: A, ListPrice: XXX, Скидка: XXX 

 Имя поставщика: B, ListPrice: XXX, Скидка: XXX 

 Имя поставщика: C, ListPrice: XXX, Скидка: XXX 

 Имя поставщика: D, ListPrice: XXX, Скидка: XXX 

 Большая часть информации об основных данных останется неизменной для сообщения, принадлежащего одному продукту (и будет меняться реже), но данные транзакций всегда будут колебаться. Таким образом, система проверит, существует ли в системе «XXX» продукт или нет. Если нет, проверьте, существует ли «Категория», упомянутая в этом продукте, нет. Если нет, он добавит новую запись для категории, а затем для продукта. Это будет сделано для производителя и других основных данных. 

 Несколько поставщиков будут отправлять данные о нескольких продуктах (2000-5000) одновременно. 

 Итак, предположим, что у нас 1000 поставщиков, каждый поставщик отправляет данные о 10-15 разных продуктах. Через каждые 2-3 секунды каждый поставщик отправляет нам обновление цены этих 10 продуктов. Он может начать отправлять данные о новых продуктах, но это будет не очень часто. 

 Вероятно, вам будет лучше с идеей №2 (т.е. отправкой всех 15-20 объектов в БД одним выстрелом с использованием нескольких ТВП и обработкой всего набора до 2000 сообщений). 

 Кэширование основных данных поиска на уровне приложения и перевод до отправки в БД звучит здорово, но пропускает что-то: 

1. Вам придется попасть в БД, чтобы получить исходный список в любом случае
2. Вам придется ударить по БД, чтобы вставить новые записи в любом случае
3. Поиск значений в словаре для замены идентификаторами - это *именно* то *,* что делает база данных (предположим, что некластеризованный указатель для каждого из этих запросов поиска по имени)
4. Часто запрашиваемые значения будут кэшироваться в пуле буферов (который *является* кешем памяти)

 Зачем дублировать на уровне приложения то, что уже предусмотрено *и происходит прямо сейчас* на уровне БД, особенно учитывая: 

* 15-20 субъектов могут иметь до 20 тыс. Записей (что является относительно небольшим числом, особенно если учитывать, что для индекса без кластеризации должно быть только два поля: `Name` и `ID` которые могут упаковывать многие строки на одну страницу данных при использовании 100% -ный коэффициент заполнения).
* Не все записи 20k являются «активными» или «текущими», поэтому вам не нужно беспокоиться о кэшировании всех них. Так что любые значения текущие будут легко идентифицироваться как те, которые запрашиваются, и *те* страницы данных (которые могут включать в себя некоторые неактивные записи, но там не большие сделки) будут теми, которые будут кэшироваться в буферном пуле.

 Следовательно, вам не нужно беспокоиться о старении старых записей или принуждении к истечению или перезагрузке ключей из-за возможного изменения значений (например, обновленного `Name` для определенного `ID` ), поскольку это обрабатывается естественным образом. 

 Да, кэширование в памяти - замечательная технология и значительно ускоряет работу с веб-сайтами, но эти сценарии / варианты использования - это когда процессы без базы данных запрашивают одни и те же данные снова и снова в чистом режиме только для чтения. Но этот конкретный сценарий - это тот, в котором данные объединяются, и список значений поиска может часто меняться (moreso из-за новых записей, чем из-за обновленных записей). 

---

 Что все сказано, Вариант № 2 - это путь. Я делал эту технику несколько раз с большим успехом, хотя не с 15 ТВП. Возможно, некоторые методы оптимизации / корректировки необходимо настроить для настройки этой конкретной ситуации, но то, что я нашел, хорошо работает: 

* Принимайте данные через TVP. Я предпочитаю это через `SqlBulkCopy` потому что:
	+ это упрощает самостоятельную сохраненную процедуру
	+ он очень хорошо вписывается в код приложения, чтобы полностью передать коллекцию (ы) в БД без необходимости копировать коллекцию (ы) в `DataTable` сначала, что дублирует коллекцию, которая тратит впустую процессор и память. Это требует, чтобы вы создавали метод для каждой коллекции, которая возвращает `IEnumerable<SqlDataRecord>` , принимает коллекцию как входную информацию и использует `yield return;` для отправки каждой записи в цикле `for` или `foreach` .
* TVPs не очень хороши для статистики и, следовательно, не подходят для JOINing (хотя это можно смягчить, используя `TOP (@RecordCount)` в запросах), но вам не нужно об этом беспокоиться, так как они используются только для заполнения реальные таблицы с любыми отсутствующими значениями
* Шаг 1. Вставьте отсутствующие имена для каждого объекта. Помните, что для каждого объекта должен быть индекс NonClustered в поле `[Name]` , и если предположить, что идентификатор является кластеризованным индексом, это значение, естественно, будет частью индекса, поэтому `[Name]` будет предоставлять индекс покрытия в кроме того, чтобы помочь в следующей операции. А также помните, что любые предыдущие исполнения для этого клиента (то есть примерно одинаковые значения сущностей) заставят страницы данных для этих индексов оставаться кэшированными в пуле буферов (т.е. памяти). 

```
;WITH cte AS
(
  SELECT DISTINCT tmp.[Name]
  FROM   @EntityNumeroUno tmp
)
INSERT INTO EntityNumeroUno ([Name])
  SELECT cte.[Name]
  FROM   cte
  WHERE  NOT EXISTS(
                 SELECT *
                 FROM   EntityNumeroUno tab
                 WHERE  tab.[Name] = cte.[Name]
                   )

```
* Шаг 2: ВСТАВЬТЕ все «сообщения» в простом `INSERT...SELECT` где страницы данных для таблиц поиска (то есть «сущности») уже кэшируются в пуле буферов из-за шага 1

---

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

 Я скажу, что если сообщения вставляются только, то идея Влада может быть быстрее. Метод, который я описываю здесь, я использовал в ситуациях, которые были более сложными и требовали полной синхронизации (обновления и удаления), а также дополнительные проверки и создание соответствующих операционных данных (не поисковых значений). Использование `SqlBulkCopy` *может* быть быстрее на прямых вставках (хотя для 2000 записей я сомневаюсь, что есть большая разница, если они вообще есть), но предполагается, что вы загружаете непосредственно в таблицы назначения (сообщения и поисковые запросы), а не в промежуточные / промежуточные таблицы ( и я считаю, что идея Влада заключается в `SqlBulkCopy` непосредственно в таблицы назначения). Однако, как указано выше, использование внешнего кеша (т. Е. Не пул буферов) также более подвержено ошибкам из-за проблемы обновления значений поиска. Это может привести к большему количеству кода, чем стоит признать недействительным внешний кеш, особенно если использование внешнего кеша только немного быстрее. Необходимо учитывать дополнительный риск / техническое обслуживание, в котором метод в целом лучше подходит для ваших нужд. 

---

 **ОБНОВИТЬ** 

 Основываясь на информации, представленной в комментариях, мы теперь знаем: 

* Существует несколько поставщиков
* Существует несколько продуктов, предлагаемых каждым поставщиком.
* Продукты не уникальны для Поставщика; Продукты продаются 1 или более поставщиками
* Свойства продукта сингулярны
* Информация о ценах имеет свойства, которые могут иметь несколько записей
* Информация о ценах - только INSERT (т.е. история за раз в истории)
* Уникальный продукт определяется SKU (или аналогичным полем)
* После создания продукта, который поставляется с существующим SKU, но с другими свойствами (например, категория, производитель и т. Д.), Будет считаться одним и *тем же продуктом* ; различия будут проигнорированы

 Учитывая все это, я по-прежнему рекомендую ТВП, но переосмыслить подход и сделать его ориентированным на поставщика, а не ориентированным на продукт. Предположение здесь заключается в том, что файлы отправителя отправляются всякий раз. Поэтому, когда вы получаете файл, импортируйте его. Единственный поиск, который вы делаете заблаговременно, - это поставщик. Вот основной макет: 

1. Кажется разумным предположить, что на данный момент у вас уже есть VendorID, потому что почему система должна импортировать файл из неизвестного источника?
2. Вы можете импортировать в пакеты
3. Создайте метод `SendRows` который:
	* принимает FileStream или что-то, что позволяет продвигаться через файл
	* принимает что-то вроде `int BatchSize`
	* возвращает `IEnumerable<SqlDataRecord>`
	* создает `SqlDataRecord` для соответствия структуре TVP
	* для циклов, хотя FileStream до тех пор, пока не будет встречен BatchSize или больше записей в файле
	* выполнить любые необходимые проверки данных
	* сопоставить данные с `SqlDataRecord`
	* `yield return;` вызова `yield return;`
4. Открыть файл
5. Хотя в файле есть данные
	* вызвать хранимую процедуру
	* пройти в VendorID
	* передать в `SendRows(FileStream, BatchSize)` для TVP
6. Закройте файл
7. Эксперимент с:
	* открытие SqlConnection перед циклом вокруг FileStream и закрытие его после завершения циклов
	* Открытие SqlConnection, выполнение хранимой процедуры и закрытие SqlConnection внутри цикла FileStream
8. Экспериментируйте с различными значениями BatchSize. Начните с 100, затем 200, 500 и т. Д.
9. Сохраненная процедура будет обрабатывать вставку новых продуктов

 Используя этот тип структуры, вы будете отправлять свойства продукта, которые не используются (то есть только SKU используется для поиска существующих продуктов). НО, он очень хорошо масштабируется, так как нет ограничений по размеру файла. Если Продавец отправляет 50 Продуктов, штраф. Если они отправят 50 тыс. Продуктов, штраф. Если они отправят 4 миллиона продуктов (это система, над которой я работал, и она обрабатывала обновление информации о продукте, которая отличалась для любого из ее свойств!), Тогда это прекрасно. Нет увеличения объема памяти на уровне приложения или уровне БД для обработки даже 10 миллионов продуктов. Время, затрачиваемое на импорт, должно увеличиваться с увеличением количества отправленных продуктов. 

---

 **ОБНОВЛЕНИЕ 2**   
 Новые данные, связанные с исходными данными: 

* происходит от Azure EventHub
* поставляется в виде объектов C # (без файлов)
* Сведения о продукте поступают через API-интерфейсы OP
* собирается в отдельной очереди (просто вытащите данные из вставки в базу данных)

 Если источником данных являются объекты C #, то я бы наиболее определенно использовал TVP, так как вы можете отправить их по методу, описанному в моем первом обновлении (т.е. метод, который возвращает `IEnumerable<SqlDataRecord>` ). Отправляйте один или несколько ТВП для цены / предложения на каждую информацию о поставщике, но регулярно вводите параметры для особых атрибутов свойства. Например: 

```
CREATE PROCEDURE dbo.ImportProduct
(
  @SKU             VARCHAR(50),
  @ProductName     NVARCHAR(100),
  @Manufacturer    NVARCHAR(100),
  @Category        NVARCHAR(300),
  @VendorPrices    dbo.VendorPrices READONLY,
  @DiscountCoupons dbo.DiscountCoupons READONLY
)
SET NOCOUNT ON;

-- Insert Product if it doesn't already exist
IF (NOT EXISTS(
         SELECT  *
         FROM    dbo.Products pr
         WHERE   pr.SKU = @SKU
              )
   )
BEGIN
  INSERT INTO dbo.Products (SKU, ProductName, Manufacturer, Category, ...)
  VALUES (@SKU, @ProductName, @Manufacturer, @Category, ...);
END;

...INSERT data from TVPs
-- might need OPTION (RECOMPILE) per each TVP query to ensure proper estimated rows

```