My<cod/>.net
Почему сервер SQL работает быстрее, когда вы индексируете таблицу после ее заполнения?
У меня есть sproc, который помещает 750k записей во временную таблицу через запрос в качестве одного из своих первых действий. Если я создаю индексы для временной таблицы до ее заполнения, то выполнение элемента занимает примерно вдвое больше времени, чем при индексации После заполнения таблицы. (Индекс-это целое число в одном столбце, индексируемая таблица - это всего лишь два столбца, каждый из которых является одним целым числом.)
Это кажется мне немного странным, но тогда у меня нет самого твердого понимания того, что происходит под капотом. У кого-нибудь есть ответ на этот вопрос?
Если вы создадите кластеризованный индекс, это повлияет на способ физического упорядочения данных на диске. Лучше добавить индекс постфактум и позволить компоненту database engine переупорядочить строки, когда он знает, как распределяются данные.
Например, предположим, что вам нужно построить кирпичную стену с пронумерованными кирпичами так, чтобы те, у кого наибольшее число, находились в нижней части стены. Это было бы трудной задачей, если бы вам просто раздавали кирпичи в случайном порядке, по одному за раз - вы не знали бы, какие кирпичи окажутся самыми высокими, и вам пришлось бы сносить стену и перестраивать ее снова и снова. Было бы намного легче справиться с этой задачей, если бы перед вами были выстроены все кирпичи и вы могли бы организовать свою работу.
Именно так обстоит дело с ядром базы данных - если вы дадите ему знать обо всем задании, оно может быть гораздо более эффективным, чем если вы просто передадите ему строку за раз.
Это потому, что сервер базы данных должен выполнять вычисления каждый раз, когда вы вставляете новую строку. В принципе, вы в конечном итоге переиндексацию таблицы каждый раз. Это не кажется очень дорогой операцией, и это не так, но когда вы делаете так много из них вместе, вы начинаете видеть эффект. Вот почему вы обычно хотите индексировать после заполнения строк, так как это будет просто одноразовая стоимость.
Вам никогда не следует создавать индекс на пустой таблице, если вы собираетесь массово загружать его сразу после этого. Индексы должны поддерживаться по мере изменения данных в таблице, поэтому представьте, что для каждой вставки в таблице индекс пересчитывается (что является дорогостоящей операцией). Сначала загрузите таблицу и создайте индекс после завершения загрузки. Вот так и происходит разница в производительности.
После выполнения больших операций по обработке данных часто приходится обновлять базовые индексы. Это можно сделать с помощью инструкции UPDATE STATISTICS [table].
Другой вариант-удалить и воссоздать индекс, который, если вы делаете большие вставки данных, скорее всего, выполнит вставки намного быстрее. Вы даже можете включить это в свою хранимую процедуру.
Это связано с тем, что когда SQL сервер индексирует таблицу с данными, он может выдавать точную статистику значений в индексируемом столбце. В некоторые моменты SQL сервер будет пересчитывать статистику, но при выполнении массивных вставок распределение значений может измениться после того, как статистика была рассчитана в последний раз.
Тот факт, что статистика устарела, можно обнаружить на анализаторе запросов. Когда вы видите, что в определенной таблице количество ожидаемых строк сильно отличается от фактического количества обработанных строк.
Для пересчета распределения значений после вставки всех данных следует использовать статистику обновления . После этого никакой разницы в производительности не должно наблюдаться.
Если у вас есть индекс в таблице, то при добавлении данных в таблицу SQL серверу придется заново упорядочить таблицу, чтобы освободить место в соответствующем месте для новых записей. Если вы добавляете много данных, ему придется переупорядочивать их снова и снова. При создании индекса только после загрузки данных повторный заказ должен произойти только один раз.
Конечно, если вы импортируете записи в порядке индекса, это не должно иметь такого большого значения.