Большая разница производительности SQL при использовании SELECT TOP x, даже когда x намного выше выбранных строк

Я выбираю некоторые строки из функции, зависящей от таблицы, но обнаружил необъяснимую значительную разницу в производительности, поставив SELECT TOP в запрос.

SELECT   col1, col2, col3 etc
FROM     dbo.some_table_function
WHERE    col1 = @parameter
--ORDER BY col1

занимает больше 5 или 6 минут.

Однако

SELECT   TOP 6000 col1, col2, col3 etc
FROM     dbo.some_table_function
WHERE    col1 = @parameter
--ORDER BY col1

завершается примерно через 4 или 5 секунд.

Это не удивило бы меня, если бы возвращаемый набор данных был огромным, , но конкретный запрос включал ~ 5000 строк из 200 000.

Итак, в обоих случаях вся таблица обрабатывается, так как SQL Server продолжается до конца в поисках 6000 строк, к которым он никогда не дойдет. Зачем тогда массивная разница? Связано ли это с тем, как SQL Server выделяет пространство в ожидании размера набора результатов (TOP 6000 тем самым дает ему низкое требование, которое легче распределяется в памяти)? Кто-нибудь еще видел что-то подобное?

Спасибо

Ответ 1

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

Рассмотрим функцию, эквивалентную для этого запроса:

SELECT  (
        SELECT  SUM(mi.value)
        FROM    mytable mi
        WHERE   mi.id <= mo.id
        )
FROM    mytable mo
ORDER BY
        mo.value

Этот запрос (который вычисляет запущенный SUM) работает быстро в начале и замедляется в конце, так как в каждой строке из mo он должен суммировать все предыдущие значения, которые требуют перемотки строки.

Время, затрачиваемое на вычисление SUM для каждой строки, увеличивается с увеличением числа строк.

Если вы сделаете mytable достаточно большим (например, 100,000 строки, как в вашем примере) и запустите этот запрос, вы увидите, что требуется значительное время.

Однако, если вы примените TOP 5000 к этому запросу, вы увидите, что он выполняется намного быстрее, чем 1/20 времени, необходимого для полной таблицы.

Скорее всего, что-то подобное происходит и в вашем случае.

Чтобы сказать что-то более определенно, мне нужно увидеть определение функции.

Update:

SQL Server может вставлять предикаты в функцию.

Например, я просто создал это TVF:

CREATE FUNCTION fn_test()
RETURNS TABLE
AS
RETURN  (
        SELECT  *
        FROM    master
        );

Эти запросы:

SELECT  *
FROM    fn_test()
WHERE   name = @name

SELECT  TOP 1000 *
FROM    fn_test()
WHERE   name = @name

дают разные планы выполнения (первый использует кластерное сканирование, второй использует поиск индекса с TOP)

Ответ 2

В вашем TOP нет ORDER BY, поэтому он просто так же, как SET ROWCOUNT 6000. ORDER BY потребовал бы, чтобы все строки были оценены первыми, и это займет намного больше времени.

Если dbo.some_table_function является встроенной таблицей, оцененной udf, то это просто макрос, который был расширен, поэтому он возвращает первые 6000 строк, как указано в каком-либо конкретном порядке.

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

Не имеет прямого отношения, но другой вопрос SO на TVF

Ответ 3

У меня была та же проблема, простой запрос, соединяющий пять таблиц, возвращающих 1000 строк, занял две минуты. Когда я добавил "TOP 10000", он завершился менее чем за одну секунду. Оказалось, что кластеризованный индекс на одной из таблиц сильно фрагментирован.

После восстановления индекса запрос завершается менее чем за секунду.

Ответ 4

Не обязательно верно, что вся таблица обрабатывается, если col1 имеет индекс.

Оптимизация SQL будет выбирать, использовать ли индекс. Возможно, ваш "TOP" заставляет его использовать индекс.

Если вы используете MSSQL Query Analyzer (имя ускользает от меня), нажмите Ctrl-K. Это покажет план выполнения запроса вместо его выполнения. Я считаю, что мышь над значками покажет использование IO/CPU.

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

Если у вас есть общий клиент: SET SHOWPLAN_ALL ON; ИДТИ Выбрать...; идти

Подробнее см. http://msdn.microsoft.com/en-us/library/ms187735.aspx.

Ответ 5

Вы можете столкнуться с чем-то таким же простым, как кэширование здесь - возможно (по какой-либо причине) запрос "TOP" кэшируется? Используя индекс, который другой не является?

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

Все реализации SQL являются изворотливыми по-своему - SQL Server не является исключением. Эти "whaaaaaa?!" моменты довольно распространены.; ^)

Ответ 6

Я думаю, что предложение Quassnois кажется очень правдоподобным. Добавляя TOP 6000, вы неявно даете оптимизатору намек на то, что будет возвращено довольно небольшое подмножество из 200 000 строк. Затем оптимизатор использует поиск индекса вместо кластерного сканирования индекса или сканирования таблицы.

Другое возможное объяснение может быть кешированием, как предлагает Джим Давис. Это довольно легко исключить, снова запустив запросы. Сначала попробуйте запустить с TOP 6000.