SQL Server/MySQL/Access — неэффективное ускорение вставки многих строк

НАСТРОЙКА

Мне нужно вставить пару миллионов строк либо в SQL Server 2000/2005, либо в MySQL, либо в Access. К сожалению, у меня нет простого способа использовать массовую вставку или BCP или любой другой способ, который мог бы использовать обычный человек. Вставки будут происходить в одной конкретной базе данных, но этот код должен быть независимым от базы данных, поэтому я не могу выполнять массовое копирование, SELECT INTO или BCP. Однако я могу выполнять определенные запросы до и после вставок, в зависимости от того, в какую базу данных я импортирую.

eg.

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?


mysql sql-server ms-access
person Michael Pryor    schedule 07.01.2009    source источник
comment
Вы используете API или инструмент командной строки или что? Почему или каким образом у вас нет простого способа использовать объемную вставку или BCP или любой другой способ, который мог бы использовать обычный человек?   -  person ChrisW    schedule 07.01.2009
comment
Оказывается, мои INSERTS должны быть независимыми от базы данных, поэтому они должны работать с MySQL, SQL Server 2000 и Access. Но я могу сделать некоторые настройки базы данных до и после вставок, чтобы ускорить процесс. Поэтому я не могу полностью переписать вставку для массовой вставки.   -  person Michael Pryor    schedule 07.01.2009
comment
Если он должен быть независимым от БД, то, возможно, вам следует обновить заголовок и теги, чтобы они были более точными в отношении вашей конкретной ситуации.   -  person Yaakov Ellis    schedule 07.01.2009
comment
Когда вы говорите, что должен работать с... Access, вы имеете в виду, что вы программируете в Access или что вы хотите добавить данные в базу данных Jet (которая является хранилищем данных по умолчанию для Access) ? Конечно, есть разница, поскольку Access — это среда разработки, а Jet — ядро ​​базы данных.   -  person David-W-Fenton    schedule 09.01.2009

Ответы (11)


arrow_upward
4
arrow_downward

Единственная самая большая вещь, которая убьет производительность здесь, это тот факт, что (похоже) вы выполняете миллион различных 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, и такие вещи, как перетасовка индексов, будут выполняться один раз, а не миллион раз. После этого вам может понадобиться гораздо меньше других оптимизаций.

person Cowan    schedule 08.01.2009

arrow_upward
2
arrow_downward

это регулярный процесс или разовое событие?

В прошлом я просто записывал текущие индексы, удалял их, вставлял строки, а затем просто повторно добавлял индексы.

SQL Management Studio может создавать индексы из контекстного меню...

person Jeff Martin    schedule 07.01.2009

arrow_upward
1
arrow_downward

Для SQL-сервера:

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

  2. Отключение индексов на самом деле хорошая идея. Это будет работать на SQL 2005, а не на SQL Server 2000.

    изменить индекс [INDEX_NAME] в [TABLE_NAME] отключить

И включить

alter index [INDEX_NAME] on [TABLE_NAME] rebuild

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

person GvS    schedule 07.01.2009
comment
Если вы не занимаетесь доставкой журналов, в этом случае вы, вероятно, не хотите устанавливать простую модель восстановления, если только вы не хотите потерять некоторые данные. - person Kibbee; 10.01.2009

arrow_upward
1
arrow_downward

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

person Yaakov Ellis    schedule 07.01.2009
comment
Разве в его вопросе не упоминалось, что он не может использовать объемную вставку? - person GvS; 07.01.2009
comment
Это не упоминалось в его вопросе в то время, когда я писал этот ответ. Он добавил, что после того, как увидел это. - person Yaakov Ellis; 07.01.2009

arrow_upward
0
arrow_downward

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

То же самое с отключением индексов, это отключает для всех и может заставить базу данных работать медленнее, чем слаг.

Предлагаем вам запустить импорт в пакетном режиме.

person HLGEM    schedule 07.01.2009
comment
Я на самом деле контролирую это, поэтому никто больше не будет использовать БД в это время. - person Michael Pryor; 07.01.2009

arrow_upward
0
arrow_downward

Если это не то, что нужно читать очень быстро, вы можете сделать "Insert Delayed" в таблицу MySQL. Это позволяет вашему коду продолжать работу, не дожидаясь фактической вставки. Это имеет некоторые ограничения, но если ваша главная задача — быстро завершить программу, это может помочь. Имейте в виду, что существует длинный список ситуаций, в которых это может работать не так, как ожидалось. Ознакомьтесь с документацией.

Однако я не знаю, работает ли эта функция для Access или MS SQL.

person Jack M.    schedule 07.01.2009
comment
Устарело и больше не работает в версии 5.7. - person John; 14.06.2016

arrow_upward
0
arrow_downward

Рассматривали ли вы возможность использования шаблона Factory? Я предполагаю, что вы пишете код для этого, поэтому, используя фабричный шаблон, вы могли бы закодировать фабрику, которая возвращала бы конкретный класс типа «IDataInserter», для которого выполнялась бы работа.

Это по-прежнему позволит вам быть независимым от данных и получить самый быстрый метод для каждого типа базы данных.

person Ray Booysen    schedule 07.01.2009

arrow_upward
0
arrow_downward

SQL Server 2000/2005, MySQL и Access могут загружаться непосредственно из текстового файла tab/cr, у них просто есть разные команды для этого. Если у вас есть оператор case, чтобы определить, в какую БД вы импортируете, просто выясните их предпочтения для импорта текстового файла.

person Frank Flynn    schedule 07.01.2009
comment
Использование SSIS или DTS в SQL стандартизировало бы процесс загрузки текстового файла и исключило бы эту переменную среди целевых систем. - person John Mo; 10.01.2009

arrow_upward
0
arrow_downward

Можно ли использовать DTS (2000) или SSIS (2005) для создания пакета для этого? DTS и SSIS могут извлекать данные из одного и того же источника и направляться в разные потенциальные пункты назначения. Попробуйте SSIS, если можете. Там есть много хороших, быстрых технологий, а также функциональные возможности для встраивания логики IsSQLServer, IsMySQL и т. д.

person John Mo    schedule 10.01.2009

arrow_upward
0
arrow_downward

Стоит подумать о разбиении вставок на более мелкие пакеты< /а>; одна транзакция с большим количеством запросов будет медленной.

person Vince Bowdren    schedule 02.02.2010

arrow_upward
-1
arrow_downward

Вы можете рассмотреть возможность использования модели восстановления SQL с неполным протоколированием во время массовой вставки.

http://msdn.microsoft.com/en-us/library/ms190422(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms190203(SQL.90).aspx

Вы также можете отключить индексы в целевой таблице во время вставки.

person Jeremy    schedule 07.01.2009