Какой самый быстрый способ массовой вставки большого количества данных в SQL Server (клиент 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 постоянны в каждом чанке, а значение Sequence равно 0-n, а значения предварительно отсортированы на основе первичного ключа.

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

Поможет ли это кому-нибудь, если я:

  1. Удалите первичный ключ, пока я делаю вставку, и создайте его позже
  2. Делайте вставки во временную таблицу с той же схемой и периодически переносите их в основную таблицу, чтобы размер таблицы, в которой выполняются вставки, был небольшим
  3. Что-нибудь еще?

- На основании полученных ответов позвольте мне немного уточнить:

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

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

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

23.08.2008 12:53:09
8 ОТВЕТОВ

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

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

0
23.08.2008 13:02:43

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

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

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

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

4
23.08.2008 13:10:32
Очень запоздало, но для любого, кто найдет это сейчас, это хорошая вещь. Я пишу процедуру вставки, используя общий код DbCommand из клиентского приложения, поэтому я не могу использовать конкретные вещи SqlClient или инструменты SQL Server - этот простой совет занял у меня время выполнения от полутора до пяти секунд.
Whelkaholism 25.04.2013 09:28:25

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

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

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

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

18
23.08.2008 13:27:42

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

Создать пакет служб SSIS довольно просто, есть мастер, встроенный в инструмент диспетчера предприятия SQL Server (я думаю, что он помечен как «Импорт данных»), и в конце мастера он дает вам возможность сохранить его как пакет служб SSIS. Есть еще много информации о Technet .

1
23.08.2008 15:40:00

BCP - это сложно настроить, но он существует с рассвета БД, и это очень быстро.

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

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

3
23.08.2008 18:28:03

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

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

Любой из них ускорит вставку без заметного замедления чтения.

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

8
23.08.2008 20:37:47

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

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

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

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

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

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

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

Чопин тоже задал интересный вопрос. Как вы решили использовать 300 блоков записей для вставки? SQL Server имеет размер пакета по умолчанию (я считаю, что он составляет 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) в файле данных. Это может помочь уменьшить большую часть перемещения шпинделя в различных секторах на диске и привести к большему времени чтения / записи вместо непродуктивного «движущегося» состояния. Если вы уже отделяете свои данные и файлы журналов, у вас есть индекс на другом физическом диске, чем файл данных (вы можете сделать это только с кластерными индексами). Это позволило бы не только одновременно обновлять информацию журналов со вставкой данных, но и одновременно выполнять вставку индекса (и любые дорогостоящие операции над страницей индекса).

3
28.10.2008 04:08:40

Вот как вы можете отключить / включить индексы в 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)

19
29.08.2012 05:25:27
Информация в этой теме может оказаться полезной dba.stackexchange.com/questions/30734/…
Raymond A 12.06.2014 11:15:42