Повышается ли производительность при размещении некластеризованного индекса в поле, которое уже имеет кластеризованный индекс?

Question

Вчера я выполнил заявление об обновлении по следующим направлениям:

UPDATE MainTable
Set SomeField = SubsetTable.SomeField
where MainTable.MainTableKey = SubsetTable.MainTableKey

где SubsetTable это подмножество MainTable и имеет то же поле первичного ключа. MainTable имеет около 200 м записей, SubsetTable имеет 5м записей. MainTableKey это GUID.

Обе эти таблицы имеют кластерный индекс на MainTableKey,

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

Затем я добавил некластеризованный индекс в MainTableKey на обоих столах. Теперь это занимает 30 минут.

У кого-нибудь есть идеи о том, почему прирост производительности был бы таким драматичным?

Answer 1

Бьюсь об заклад, если вы посмотрите на планы выполнения:

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

200M rows + 5M rows = 205M rows.
205M rows / 14 hours = 4067 rows per second.

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

5M + 5M rows = 10M rows.
10M rows / 40 minutes = 4166rows per second.

То, что эти показатели примерно равны, подтверждает мою теорию о том, какие строки читаются.

Вам не нужно угадывать: запустите запросы с SET STATISTICS IO ONи / или посмотрите на планы выполнения.