SQL TOP 5000 быстрее обычного запроса с менее чем 5000 строк результатов?

Я заметил странное поведение:

Запуск этого запроса:

SELECT TOP 5000  t1.f1,t1.f2,t1.f3 
FROM t1
JOIN t2 on t1.f1 = t2.f1
WHERE t2.f1 IS NOT NULL AND (t1.f5 != t2.f3)

Результаты в 3447 строк за 2 секунды.

Запуск этого:

SELECT t1.f1,t1.f2,t1.f3 
FROM t1
JOIN t2 on t1.f1 = t2.f1
WHERE t2.f1 IS NOT NULL AND (t1.f5 != t2.f3)

Работает вечно, пока я не остановлю его (не менее 120 минут!).

Таблица t1 и t2 содержит около 500 тыс. записей.

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

EDIT:

В соответствии с запросом:

t1:

CREATE TABLE [dbo].[t1](
    [f1] [int] NOT NULL,
    [f2] [varchar](10) NULL,
    [f3] [varchar](4) NULL,
    [f4] [int] NOT NULL,
    [f5] [varchar](max) NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED 
(
    [f1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

f2:

CREATE TABLE [dbo].[t2](
    [f1] [nchar](10) NOT NULL,
    [f2] [nchar](10) NOT NULL,
    [f3] [varchar](max) NOT NULL,
    [f4] [nchar](10) NULL,
    [f5] [date] NULL,
    [f6] [date] NULL,
    [f7] [nchar](1) NULL,
 CONSTRAINT [PK_t2] PRIMARY KEY CLUSTERED 
(
    [f1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Планы выполнения:

С вершиной:

Без верха:

Глядя на это, я должен был бы сделать вывод, что sorting (ПОЧЕМУ он это делает?) вызывает задержку... Согласитесь ли вы?

Edit2: в соответствии с запросом, план выполнения с опцией цикла без верхнего:

Ответ 1

Проблема состоит в том, что ваши две таблицы [t1] и [t2] имеют совершенно разные (и в значительной степени несовместимые) типы данных для столбца JOIN, f1.

Это делает невозможным, чтобы Оптимизатор запросов генерировал точную оценку того, сколько строк будет совпадать между этими двумя 500 000 таблицами строк. Похоже, что используется "предположение" по умолчанию, которое в данном случае является грубой переоценкой фактического числа (3477). Из-за этого, когда вы не используете TOP, он считает, что будет более эффективно сортировать, а затем объединить строки (O (NLogN)), чем делать вложенные циклы (O (N ^ 2)), потому что это не осознавайте, что (объединение) JOIN фактически уничтожит почти все строки.

Когда у вас есть TOP 5000, он понимает, что вложенные петли лучше, потому что он будет отключен не более чем на 5000 (гораздо меньше 500k ^ 2 и даже меньше 500k * Log (500k))., Но в отличие от вложенных циклов, Merge-Sort не может выполняться постепенно, он должен иметь все строки для Сортировки сначала. Таким образом, отрезав выход на 5000, не сэкономит вам вообще, тем самым сделав Nested Loops лучшим вариантом (даже при плохой оценке JOIN).

Коренная проблема заключается в том, что столбец T2.f1 является NCHAR (10), который является действительно плохим выбором для того, что похоже на то, что оно должно содержать целое число. Лучшим решением было бы изменить этот тип данных столбца на INT.

Если по какой-то причине вы не можете этого сделать, то в зависимости от вашей версии SQL Server вы можете завершить ее, добавив постоянный вычисленный столбец, который вычисляет преобразованное значение INT [f1], а затем бросает совместимый индекс на этом. Это позволит как индексированию, так и статистике снова работать для таких запросов.

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

Ответ 2

SQL-запросы могут быть оптимизированы различными способами. Два общих способа - это "самая быстрая первая строка" и "самая быстрая последняя строка". То есть, вы хотите свести к минимуму время, чтобы получить какой-либо результат или время, чтобы получить полный набор результатов.

Я бы предположил, что эти две версии оптимизируются по-разному. Вы можете проверить это, как предлагает Аарон, взглянув на планы исполнения. Моя обычная ставка заключается в том, что медленная версия использует вложенные объединения циклов. Вы можете исправить это с помощью подсказки оптимизатора, например:

<your query>
option (MERGE JOIN, HASH JOIN)

Существуют и другие возможности. Возможно, эти таблицы обновляются, и при запуске второго запроса таблицы столкнулись с полными блокировками таблиц. Вы можете проверить это, используя sp_who2.