Почему производительность увеличивается при переходе от производной таблицы к решению с временными таблицами?

Я читаю «Анализ планов выполнения SQL Server» от Гранта Фритчи, и это очень помогает мне понять, почему некоторые запросы выполняются медленно.

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

Это моя первая попытка, она занимает 21 секунду. Он использует производную таблицу:

-- 21 secs
SELECT *
  FROM Table1 AS o JOIN( 
    SELECT col1
    FROM    Table1
    GROUP BY    col1
    HAVING  COUNT( * ) > 1
) AS i ON ON i.col1= o.col1

Моя вторая попытка была в 3 раза быстрее и просто перемещала производную таблицу во временную таблицу. Теперь это в 3 раза быстрее:

-- 7 secs
SELECT col1
INTO    #doubles
FROM    Table1
GROUP BY    col1
HAVING  COUNT( * ) > 1

SELECT *
FROM Table1 AS o JOIN #doubles AS i ON i.col1= o.col1

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

Я был бы признателен, если бы кто-нибудь показал мне, как я могу диагностировать эту проблему с помощью (графического) плана выполнения.

Xml План выполнения: https://www.sugarsync.com/pf/D6486369_1701716_16980

Редактировать 1

Когда я создал статистику по столбцам 2 , которые были указаны в группе, и оптимизатор начал делать "Правильно", после отказа от кеша процедур (не забывайте об этом, если вы новичок!). Я упростил запрос в вопросе, что в ретроспективе не было хорошим упрощением. Прилагаемый sqlplan показывает 2 столбца, но это не было очевидно.

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

Индекс (некластеризованный) для этих двух столбцов заставлял запрос выполнять то же самое, но в этом случае статистика так же хороша и не страдает недостатком обслуживания индекса. Я перейду к статистике в 2 столбца и посмотрю, как он работает. @Grant Знаете ли вы, является ли статистика индекса более надежной, чем статистика столбца?

Редактировать 2

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

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

Некоторые инструменты, которые могут помочь:

1. УСТАНОВИТЬ ПРОФИЛЬ СТАТИСТИКИ ВКЛ.

Я слышал, что этот станет устаревшим и будет заменен его вариантом XML, но мне все еще нравится вывод в формате сетки. Здесь большая разница между столбцами "Rows" и "EstimateRows" показала бы проблему.

2. Внешний инструмент: SQL Sentry Plan Explorer http://www.sqlsentry.net/

Это хороший инструмент, особенно если вы новичок. Выделяются проблемы

3. Внешний инструмент: пакет инструментов SSMS http://www.ssmstoolspack.com/

Инструмент более общего назначения, но снова направляет пользователя к потенциальным проблемам.

С уважением, Том