Какой самый быстрый способ для массового ввода большого количества данных в SQL Server (клиент С#)

Я сталкиваюсь с некоторыми узкими местами производительности с моим клиентом С#, вставляющим массивные данные в базу данных SQL Server 2005, и я ищу способы ускорения процесса.

Я уже использую SqlClient.SqlBulkCopy(который основан на TDS), чтобы ускорить передачу данных по проводу, что очень помогло, но я все еще ищу больше.

У меня есть простая таблица, которая выглядит так:

 CREATE TABLE [BulkData](
 [ContainerId] [int] NOT NULL,
 [BinId] [smallint] NOT NULL,
 [Sequence] [smallint] NOT NULL,
 [ItemId] [int] NOT NULL,
 [Left] [smallint] NOT NULL,
 [Top] [smallint] NOT NULL,
 [Right] [smallint] NOT NULL,
 [Bottom] [smallint] NOT NULL,
 CONSTRAINT [PKBulkData] PRIMARY KEY CLUSTERED 
 (
  [ContainerIdId] ASC,
  [BinId] ASC,
  [Sequence] ASC
))

Я вставляю данные в куски, в среднем около 300 строк, где ContainerId и BinId являются постоянными в каждом фрагменте, а значение Sequence равно 0-n, а значения предварительно сортируются на основе первичного ключа.

Счетчик производительности% Disk time тратит много времени на 100%, поэтому ясно, что проблема с IO диска является основной проблемой, но скорости, которые я получаю, на несколько порядков ниже копии необработанного файла.

Помогает ли это, если я:

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

- Основываясь на ответах, которые я получил, позвольте мне немного пояснить:

Портман: Я использую кластерный индекс, потому что когда все данные импортируются, мне нужно будет последовательно получать данные в этом порядке. Мне особенно не нужен индекс, который будет там при импорте данных. Есть ли какое-либо преимущество в отношении наличия некластеризованного индекса PK при выполнении вставок, а не для ограничения ограничения для импорта?

Chopeen: данные генерируются удаленно на многих других машинах (мой SQL-сервер может обрабатывать только около 10 в настоящее время, но я хотел бы добавить больше). Нецелесообразно запускать весь процесс на локальной машине, потому что тогда ему придется обрабатывать в 50 раз больше входных данных для генерации вывода.

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

Ответ 1

Вы уже используете SqlBulkCopy, что является хорошим началом.

Однако использование класса SqlBulkCopy не обязательно означает, что SQL будет выполнять массовую копию. В частности, для SQL Server необходимо выполнить несколько требований для выполнения эффективной объемной вставки.

Дальнейшее чтение:

Из любопытства, почему ваш индекс настроен так? Кажется, что ContainerId/BinId/Sequence намного лучше подходит для некластеризованного индекса. Есть ли какая-то конкретная причина, по которой вы хотите, чтобы этот индекс был сгруппирован?

Ответ 2

Здесь вы можете отключить/включить индексы в SQL Server:

--Disable Index ALTER INDEX [IX_Users_UserID] SalesDB.Users DISABLE
GO
--Enable Index ALTER INDEX [IX_Users_UserID] SalesDB.Users REBUILD

Вот некоторые ресурсы, которые помогут вам найти решение:

Сравнение скорости загрузки нескольких партий

Используйте SqlBulkCopy для быстрой загрузки данных с вашего клиента на SQL Server

Оптимизация производительности объемного копирования

Определенно посмотрите в опции NOCHECK и TABLOCK:

Табличные подсказки (Transact-SQL)

INSERT (Transact-SQL)

Ответ 3

Я предполагаю, что вы увидите резкое улучшение, если вы измените этот индекс на некластеризованный. Это дает вам два варианта:

Измените индекс на некластерный и оставьте его в виде таблицы кучи без кластеризованного индекса
Измените индекс на некластерный, но затем добавьте суррогатный ключ (например, "id" ) и сделайте его идентификационным, первичным и кластеризованным индексом

Либо один ускорит ваши вставки без, заметно замедляя ваши чтения.

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

Ответ 4

Вы пытались использовать транзакции?

Из того, что вы описали, имея сервер, выполняющий 100% времени на диск, кажется, вы отправляете каждую строку данных в атомарное предложение SQL, тем самым заставляя сервер фиксировать (записывать на диск) каждую строку.

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

За дополнительной помощью: какой метод вы используете для вставки данных на сервер? Обновление DataTable с помощью DataAdapter или выполнение каждого предложения с помощью строки?

Ответ 5

BCP - это боль для настройки, но она была вокруг с самого начала БД, и это очень быстро.

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

Смежные клавиши в Sql всегда довольно медленные, чем больше клавиша, тем медленнее.

Ответ 6

Я не очень яркий парень, и у меня нет большого опыта в методе SqlClient.SqlBulkCopy, но здесь мои 2 цента за то, что это стоит. Я надеюсь, что это поможет вам и другим (или, по крайней мере, заставляет людей вызывать мое невежество;).

Вы никогда не сравните скорость копирования исходного файла, если ваш файл данных базы данных (mdf) не находится на отдельном физическом диске из файла журнала транзакций (ldf). Кроме того, любые кластерные индексы также должны быть на отдельном физическом диске для более справедливого сравнения.

Ваша необработанная копия не регистрирует и не поддерживает порядок сортировки выбранных полей (столбцов) для целей индексирования.

Я согласен с Portman в создании некластеризованного семени и изменении существующего некластеризованного индекса на кластеризованный индекс.

Что касается конструкции, которую вы используете на клиентах... (адаптер данных, набор данных, данные и т.д.). Если ваш диск io на сервере составляет 100%, я не думаю, что ваше время лучше всего анализировать клиентские конструкции, поскольку они кажутся быстрее, чем может обрабатывать сервер.

Если вы будете следовать ссылкам Portman о минимальном протоколировании, я бы не подумал, что окружающие ваши массовые копии в транзакциях помогут много, если они есть, но я ошибался много раз в жизни;)

Это не обязательно поможет вам прямо сейчас, но если вы выясните свою текущую проблему, следующий комментарий может помочь в следующем узком месте (пропускная способность сети) - особенно если это происходит через Интернет...

Шопен задал интересный вопрос. Как вы определили использование 300 блоков записей для вставки? SQL Server имеет размер пакета по умолчанию (я считаю, это 4096 байт), и мне было бы разумно получить размер ваших записей и обеспечить эффективное использование пакетов, передающих между клиентом и сервером. (Обратите внимание: вы можете изменить размер вашего пакета на свой клиентский код, а не на серверный вариант, который, очевидно, изменит его для всех коммуникаций сервера - возможно, это не очень хорошая идея.) Например, если ваш размер записи приводит к 300 партиям записей, требующим 4500 байтов, вы отправите 2 пакета со вторым пакетом в основном впустую. Если количество записей партии было произвольно назначено, может иметь смысл сделать некоторую быструю математику.

Из того, что я могу сказать (и помню о размерах типа данных), у вас есть ровно 20 байт для каждой записи (если int = 4 байта и smallint = 2 байта). Если вы используете 300 партий записей, то вы пытаетесь отправить 300 x 20 = 6 000 байт (плюс я угадываю небольшие накладные расходы для подключения и т.д.). Вы могли бы более эффективно отправлять их в 200 партий записей (200 x 20 = 4000 + место для накладных расходов) = 1 пакет. Опять же, ваше узкое место по-прежнему является серверным диском io.

Я понимаю, что вы сравниваете необработанную передачу данных с SqlBulkCopy с тем же оборудованием/конфигурацией, но здесь, куда бы я пошел, если бы вызов был моим:

Это сообщение, вероятно, вам больше не поможет, поскольку оно довольно старое, но я бы спросил, что такое конфигурация вашего дискового RAID и какая скорость диска вы используете? Попробуйте поместить файл журнала на диск, который использует RAID 10 с RAID 5 (идеально 1) в вашем файле данных. Это может помочь уменьшить много перемещений шпинделя в разные сектора на диске и привести к увеличению времени чтения/записи вместо непродуктивного "движущегося" состояния. Если вы уже разделяете свои данные и файлы журналов, у вас есть свой индекс на другом физическом диске из вашего файла данных (это можно сделать только с кластеризованными индексами). Это позволило бы не только одновременно обновлять информацию о входе в систему с вставкой данных, но позволяло одновременно вставлять индексы (и любые дорогостоящие операции с индексной страницей).

Ответ 7

Мне кажется, что это может быть сделано с помощью пакетов SSIS. Они похожи на пакеты SQL 2000 DTS. Я использовал их, чтобы успешно преобразовывать все из текстовых CSV файлов, из существующих таблиц SQL и даже из файлов XLS с 6-значными строками, расположенными на нескольких листах. Вы можете использовать С# для преобразования данных в импортируемый формат (CSV, XLS и т.д.), А затем ваш SQL-сервер запустил запланированное задание SSIS для импорта данных.

Очень легко создать пакет SSIS, там есть мастер, встроенный в инструмент SQL Server Enterprise Manager (помеченный как "Импорт данных", я думаю), и в конце мастера он дает вам возможность сохранить его как Пакет SSIS. Там есть еще больше на Technet.

Ответ 8

Да, ваши идеи помогут.
Положите на вариант 1, если во время загрузки нет прочтений.
Положите на вариант 2, если во время обработки запрашивается таблица назначения.

@Андрей
Вопрос. Ваша вставка в куски 300. Какова общая сумма вашей вставки? SQL-сервер должен иметь возможность обрабатывать 300 простых старых вставок очень быстро.

Ответ 9

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