SQL Server / MySQL / Access - ускорение неэффективной вставки большого количества строк
НАСТРАИВАТЬ
Мне нужно вставить пару миллионов строк в SQL Server 2000/2005, MySQL или Access. К сожалению, у меня нет простого способа использовать массовую вставку, BCP или какие-либо другие способы, которые сделал бы нормальный человек. Вставки будут выполняться в одной конкретной базе данных, но этот код должен быть независимым от db, поэтому я не могу выполнять массовое копирование, SELECT INTO или BCP. Однако я могу выполнять определенные запросы до и после вставок, в зависимости от того, в какую базу данных я импортирую.
например.
If IsSqlServer() Then
DisableTransactionLogging();
ElseIf IsMySQL() Then
DisableMySQLIndices();
End If
... do inserts ...
If IsSqlServer() Then
EnableTransactionLogging();
ElseIf IsMySQL() Then
EnableMySQLIndices();
End If
ВОПРОС
Могу ли я сделать с SQL Server какие-нибудь интересные вещи, которые могут ускорить эти вставки?
Например, есть ли команда, которую я мог бы выполнить, чтобы сказать SQL Server: «Эй, не беспокойтесь о записи этих транзакций в журнал транзакций».
Или, может быть, я мог бы сказать: «Эй, мне приходит миллион строк, поэтому не обновляйте свой индекс, пока я полностью не закончу».
ALTER INDEX [IX_TableIndex] ON Table DISABLE
... inserts
ALTER INDEX [IX_TableIndex] ON Table REBUILD
(Примечание: указанное выше отключение индекса работает только в 2005 году, а не в 2000 году. Бонусные баллы, если вы знаете, как это сделать в 2000 году).
А как насчет MySQL и Access?
Оказывается, мои INSERTS должны быть независимыми от db, поэтому он должен работать с MySQL, SQL Server 2000 и Access. Но я могу сделать некоторые настройки базы данных до и после вставки, чтобы ускорить процесс. Поэтому я не могу полностью переписать вставку для выполнения массовой вставки.
Если он должен быть независимым от db, то, возможно, вам следует обновить заголовок и теги, чтобы они были более точными в отношении вашей конкретной ситуации.
Когда вы говорите «должен работать с ... Access», вы имеете в виду, что вы программирование в Access, или что вы хотите добавить данные в базу данных Jet (которая является хранилищем данных по умолчанию для Access)? Конечно, есть разница, поскольку Access - это среда разработки, а Jet - ядро базы данных.
Ответы 11
Для SQL Server:
- Вы можете установить модель восстановления «Простая», чтобы ваш журнал транзакций был небольшим. Не забудьте потом вернуться назад.
На самом деле, отключение индексов - хорошая идея. Это будет работать на SQL 2005, а не на SQL Server 2000.
изменить индекс [INDEX_NAME] в [TABLE_NAME] отключить
И чтобы включить
alter index [INDEX_NAME] on [TABLE_NAME] rebuild
А затем просто вставляйте строки одну за другой. Вы должны набраться терпения, но, по крайней мере, это несколько быстрее.
Если вы не выполняете доставку журналов, и в этом случае вы, вероятно, не захотите устанавливать простую модель восстановления, если вы не хотите потерять некоторые данные.
Если это одноразовая вещь (или это происходит достаточно часто, чтобы оправдать автоматизацию), также подумайте об удалении / отключении всех индексов, а затем добавлении / повторном включении их снова, когда вставка будет выполнена.
Разве в его вопросе не упоминалось, что он не может использовать объемную вставку?
Об этом не было сказано в его вопросе в то время, когда я писал этот ответ. Он добавил, что после того, как увидел это.
Вы можете рассмотреть возможность использования SQL-модели восстановления с неполным протоколированием во время массовой вставки.
http://msdn.microsoft.com/en-us/library/ms190422(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms190203(SQL.90).aspx
Вы также можете отключить индексы в целевой таблице во время вставки.
это обычный процесс или разовое мероприятие?
Раньше я просто создавал сценарии для текущих индексов, отбрасывал их, вставлял строки, а затем просто повторно добавлял индексы.
SQL Management Studio может создавать сценарии индексов из контекстного меню ...
Проблема с установкой простой модели восстановления заключается в том, что она влияет на всех других пользователей, вводящих данные одновременно, и, таким образом, вносит в них изменения, которые невозможно восстановить.
Samre с отключением индексов, это отключает для всех и может заставить базу данных работать медленнее, чем slug.
Предлагаем запускать импорт партиями.
Я фактически контролирую это, поэтому никто больше не будет использовать базу данных в это время.
Если это не то, что нужно очень быстро читать, вы можете сделать «Вставить с задержкой» в таблице MySQL. Это позволяет вашему коду продолжать работу, не дожидаясь, пока действительно произойдет вставка. У этого есть некоторые ограничения, но если ваша основная задача - быстро завершить программу, это может помочь. Имейте в виду, что существует длинный список ситуаций, в которых это может не сработать, как ожидалось. Отметьте документы.
Однако я не знаю, работает ли эта функция для Access или MS SQL.
Устарело и больше не работает в версии 5.7.
Думали ли вы об использовании шаблона Factory? Я предполагаю, что вы пишете код для этого, поэтому, используя шаблон factory, вы можете создать код factory, который возвращает конкретный класс типа «IDataInserter», который будет выполнять эту работу.
Это по-прежнему позволит вам не зависеть от данных и получить самый быстрый метод для каждого типа базы данных.
SQL Server 2000/2005, MySQL и Access могут загружаться непосредственно из текстового файла tab / cr, у них просто есть разные команды для этого. Если у вас есть оператор case, чтобы определить, в какую базу данных вы импортируете, просто выясните, как они предпочитают импортировать текстовый файл.
Использование SSIS или DTS в SQL стандартизировало бы процесс загрузки текстового файла и исключило бы эту переменную среди целевых систем.
Самая большая вещь, которая убивает здесь производительность, - это тот факт, что (похоже) вы выполняете миллион различных INSERT для БД. Каждая операция INSERT рассматривается как отдельная операция. Если вы можете сделать это как одну операцию, то вы почти наверняка получите огромное улучшение производительности.
И MySQL, и SQL Server поддерживают «выборку» константных выражений без имени таблицы, поэтому это должно работать как один оператор:
INSERT INTO MyTable(ID, name)
SELECT 1, 'Fred'
UNION ALL SELECT 2, 'Wilma'
UNION ALL SELECT 3, 'Barney'
UNION ALL SELECT 4, 'Betty'
Мне не ясно, поддерживает ли Access это, не имея доступа. ОДНАКО, Access поддерживает константы в SELECT, насколько я могу судить, и вы можете принудительно преобразовать вышеуказанное в ANSI SQL-92 (который должен поддерживаться всеми тремя механизмами; это примерно так же близко к «агностику БД», как и вы » я получу) просто добавив
FROM OneRowTable
до конца каждого отдельного SELECT, где OneRowTable - это таблица с одной строкой фиктивных данных.
Это должно позволить вам вставить миллион строк данных гораздо меньше, чем миллион операторов INSERT - и такие вещи, как перетасовка индекса, будут выполняться один раз, а не миллион раз. После этого у вас может быть гораздо меньше необходимости в других оптимизациях.
Можете ли вы использовать DTS (2000) или SSIS (2005) для создания пакета для этого? DTS и SSIS могут извлекать из одного источника и передавать по разным потенциальным адресатам. Если можете, переходите на SSIS. Там есть много хороших, быстрых технологий, а также функциональность для встраивания логики IsSQLServer, IsMySQL и т. д.
Стоит учесть разбивать вставки на более мелкие партии; одна транзакция с большим количеством запросов будет медленной.
Вы используете API или инструмент командной строки, что ли? Почему или каким образом у вас «нет простого способа использовать массовую вставку, BCP или какие-либо другие способы, которые сделал бы нормальный человек»?