Почему SQL Server работает быстрее, если вы индексируете таблицу после ее заполнения?

это связано с тем, что если данные, которые вы вставляете, находятся не в порядке индекса, SQL придется разделить страницы, чтобы освободить место для дополнительных строк, чтобы они логически оставались вместе

Вам НИКОГДА не следует создавать индекс для пустой таблицы, если вы собираетесь сразу после этого массово загрузить его. Индексы должны поддерживаться по мере изменения данных в таблице, поэтому представьте, что для каждой вставки в таблицу индекс пересчитывается (что является дорогостоящей операцией). Сначала загрузите таблицу и создайте индекс после завершения загрузки. Вот где и идет разница в производительности.

После выполнения больших операций по манипулированию данными вам часто приходится обновлять базовые индексы. Это можно сделать с помощью оператора UPDATE STATISTICS [table].

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

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

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

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

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

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

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

Вы должны использовать ОБНОВЛЕНИЕ СТАТИСТИКИ для пересчета распределения значений после вставки всех данных. После этого не должно наблюдаться разницы в производительности.

Подумайте об этом так.

Given
unorderedList = {5, 1,3}
orderedList = {1,3,5}

add 2 to both lists.
unorderedList = {5, 1,3,2}
orderedList = {1,2,3,5}

Как вы думаете, какой список легче добавить?

Кстати, упорядочивание ввода перед загрузкой даст вам импульс.

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

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

Помимо накладных расходов на индекс, выполнение каждого запроса как транзакции - плохая идея по той же причине. Если вы запускаете фрагменты вставок (скажем, 100) в 1 явной транзакции, вы также должны увидеть увеличение производительности.