My<cod/>.net
Каков самый быстрый способ массовой вставки большого количества данных в SQL сервер (C# клиент)
Я сталкиваюсь с некоторыми узкими местами производительности, когда мой клиент C# вставляет массовые данные в базу данных 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 являются постоянными в каждом блоке, а значение последовательности равно 0-n, и значения предварительно сортируются на основе первичного ключа.
Счетчик производительности %Disk time тратит много времени на 100%, поэтому ясно, что диск IO является главной проблемой, но скорость, которую я получаю, на несколько порядков ниже, чем у необработанной копии файла.
Поможет ли это кому-нибудь, если я:
- Отбросьте первичный ключ, пока я выполняю вставку, и воссоздайте его позже
- Сделайте вставки во временную таблицу с той же схемой и периодически переносите их в основную таблицу, чтобы сохранить размер таблицы, в которой происходят вставки, небольшим
- Что-нибудь еще? --
Основываясь на полученных ответах, позвольте мне немного прояснить ситуацию:
Портман: я использую кластеризованный индекс, потому что когда все данные будут импортированы, мне нужно будет обращаться к данным последовательно в этом порядке. Мне не особенно нужно, чтобы индекс был там при импорте данных. Есть ли какое-либо преимущество в том, чтобы иметь некластеризованный индекс PK при выполнении вставок, а не полностью отбрасывать ограничение для импорта?
Chopeen: данные генерируются удаленно на многих других машинах (мой сервер SQL в настоящее время может обрабатывать только около 10, но я хотел бы иметь возможность добавить еще). Нецелесообразно запускать весь процесс на локальном компьютере, потому что тогда ему придется обрабатывать в 50 раз больше входных данных, чтобы генерировать выходные данные.
Джейсон: я не делаю никаких параллельных запросов к таблице во время процесса импорта, я попробую удалить первичный ключ и посмотрю, поможет ли это.
Вот как вы можете отключить / включить индексы в SQL сервере:
--Disable Index ALTER INDEX [IX_Users_UserID] SalesDB.Users DISABLE
GO
--Enable Index ALTER INDEX [IX_Users_UserID] SalesDB.Users REBUILDВот некоторые ресурсы, которые помогут вам найти решение:
Некоторые сравнения скорости объемной загрузки
Используйте SqlBulkCopy для быстрой загрузки данных с Вашего клиента на сервер SQL
Оптимизация Производительности Массового Копирования
Определенно посмотрите на варианты NOCHECK и TABLOCK:
Табличные Подсказки (Transact-SQL)
Вы уже используете SqlBulkCopy, что является хорошим началом.
Однако простое использование класса SqlBulkCopy не обязательно означает, что SQL будет выполнять массовое копирование. В частности, существует несколько требований, которые должны быть выполнены для SQL сервера для выполнения эффективной массовой вставки.
Дальнейшее чтение:
- Необходимые условия для минимального ведения журнала при массовом импорте
- Оптимизация Производительности Массового Импорта
Из любопытства, почему ваш индекс так настроен? Похоже, что ContainerId/BinId/Sequence гораздо лучше подходит для того, чтобы быть некластеризованным индексом. Есть ли какая-то особая причина, по которой вы хотите, чтобы этот индекс был кластеризован?
Я предполагаю, что вы увидите резкое улучшение, если измените этот индекс на некластеризованный . Это оставляет вам два варианта:
- Измените индекс на некластеризованный и оставьте его в виде таблицы кучи без кластеризованного индекса
- Измените индекс на некластеризованный, но затем добавьте суррогатный ключ (например, "id") и сделайте его идентификатором, первичным ключом и кластеризованным индексом
Любой из них ускорит ваши вставки, не заметно замедляя ваши чтения.
Подумайте об этом таким образом-прямо сейчас вы говорите SQL сделать массовую вставку, но затем вы просите SQL изменить порядок всей таблицы каждый раз, когда вы что-то добавляете. С некластеризованным индексом вы добавите записи в любом порядке, в каком они появятся, а затем построите отдельный индекс, указывающий желаемый порядок.
Вы пробовали использовать транзакции?
Из того, что вы описываете, имея сервер, фиксирующий 100% времени на диске, кажется, что вы посылаете каждую строку данных в атомарном предложении SQL, таким образом заставляя сервер фиксировать (записывать на диск) каждую отдельную строку.
Если вы используете транзакции вместо этого, сервер будет фиксировать только один раз в конце транзакции.
Дополнительная справка: какой метод вы используете для вставки данных на сервер? Обновление DataTable с помощью DataAdapter или выполнение каждого предложения с помощью строки?
BCP -это боль, чтобы установить, но это было вокруг с самого рассвета DBs, и это очень быстро.
Если вы не вставляете данные в таком порядке, то трехкомпонентный индекс действительно замедлит процесс. Применение его позже действительно замедлит процесс, но будет на втором этапе.
Составные ключи в Sql всегда довольно медленные, чем больше ключ, тем медленнее.
Я не очень умный парень, и у меня нет большого опыта работы с методом SqlClient.SqlBulkCopy, но вот мои 2 цента за то, что он стоит. Я надеюсь, что это поможет вам и другим (или, по крайней мере, заставит людей назвать мое невежество ;).
Скорость копирования необработанных файлов никогда не будет соответствовать скорости копирования необработанных файлов, если только файл данных базы данных (mdf) не находится на отдельном физическом диске от файла журнала транзакций (ldf). Кроме того, любые кластеризованные индексы также должны находиться на отдельном физическом диске для более справедливого сравнения.
Ваша необработанная копия не регистрирует и не поддерживает порядок сортировки выбранных полей (столбцов) для целей индексирования.
Я согласен с Портманом в создании некластеризованного идентификационного семени и изменении существующего некластеризованного индекса на кластеризованный индекс.
Что касается конструкции, которую вы используете на клиентах...(адаптер данных, dataset, datatable и т. д.). Если ваш диск io на сервере находится на 100%,, я не думаю, что ваше время лучше потратить на анализ клиентских конструкций, поскольку они кажутся более быстрыми, чем сервер в настоящее время может обрабатывать.
Если вы перейдете по ссылкам Портмана о минимальном протоколировании, я не думаю, что окружение ваших массовых копий в транзакциях сильно поможет, если таковые имеются, но я ошибался много раз в своей жизни ;)
Это не обязательно поможет вам прямо сейчас, но если вы выясните свою текущую проблему, этот следующий комментарий может помочь со следующим узким местом (пропускной способностью сети) - особенно если это происходит через Интернет...
Шопен тоже задал интересный вопрос. Как вы решили использовать для вставки 300 блоков подсчета записей? SQL сервер имеет размер пакета по умолчанию (я считаю, что он составляет 4096 байт), и мне было бы целесообразно получить размер ваших записей и убедиться, что вы эффективно используете пакеты, передаваемые между клиентом и сервером. (Обратите внимание, что вы можете изменить размер пакета в своем клиентском коде в отличие от опции сервера, которая, очевидно, изменит его для всех серверных коммуникаций - вероятно, это не очень хорошая идея.) Например, если размер вашей записи составляет 300 пакетов записей, требующих 4500 байт, вы отправите 2 пакета, причем второй пакет будет в основном потрачен впустую. Если количество записей пакета было произвольно назначено, то, возможно, имеет смысл сделать некоторые быстрые простые вычисления.
Из того, что я могу сказать (и помните о размерах типов данных), у вас есть ровно 20 байт для каждой записи (если int=4 байта и smallint=2 байта). Если вы используете пакеты с количеством записей 300, то вы пытаетесь отправить 300 x 20 = 6000 байт (плюс я предполагаю, что немного накладных расходов для соединения и т. д.). Вы могли бы быть более эффективными, чтобы отправить их в 200 партиях подсчета записей (200 x 20 = 4000 + место для накладных расходов) = 1 пакет. Опять же, ваше узкое место все еще кажется диском сервера io.
Я понимаю, что вы сравниваете передачу необработанных данных с SqlBulkCopy с тем же оборудованием / конфигурацией, но вот куда бы я пошел, если бы проблема была моей:
Этот пост, вероятно, не поможет вам больше, так как он довольно старый, но я бы хотел спросить, какова конфигурация вашего диска RAID и какую скорость диска вы используете? Попробуйте поместить файл журнала на диск, который использует RAID 10 с RAID 5 (в идеале 1) в файле данных. Это может помочь уменьшить большое количество перемещений шпинделя в различные сектора диска и привести к увеличению времени чтения/записи вместо непродуктивного состояния "moving". Если вы уже отделили свои файлы данных и журналов, есть ли у вас индекс на другом физическом диске от файла данных (это можно сделать только с кластеризованными индексами). Это позволило бы не только одновременно обновлять информацию журнала с вставкой данных, но и позволяло бы вставлять индексы (и любые дорогостоящие операции с индексными страницами) одновременно.
Я думаю, что это может быть сделано с помощью пакетов SSIS . Они похожи на пакеты SQL 2000-х годов DTS. Я использовал их для успешного преобразования всего из обычного текста CSV файлов, из существующих SQL таблиц и даже из XLS файлов с 6-значными строками, расположенными на нескольких листах. Вы можете использовать C# для преобразования данных в импортируемый формат (CSV, XLS и т. д.), а затем ваш сервер SQL выполнит запланированное задание SSIS для импорта данных.
Довольно легко создать пакет SSIS, есть мастер, встроенный в инструмент Enterprise Manager сервера SQL (по-моему, с надписью "Import Data"), и в конце мастера он дает вам возможность сохранить его как пакет SSIS. Есть куча более подробную информацию, а также на веб-сайте TechNet .
Да, ваши идеи помогут.
Опирайтесь на Вариант 1, Если во время загрузки не происходит никаких считываний.
Используйте вариант 2, Если во время обработки запроса к целевой таблице выполняется запрос.
@Andrew
Вопрос. Ваша вставка кусками по 300. Какова общая сумма вашей вставки? SQL сервер должен быть в состоянии обрабатывать 300 простых старых вставок очень быстро.