SELECT TOP медленный, независимо от ORDER BY

У меня довольно сложный запрос в SQL Server, запущенный против представления, в форме:

SELECT *
   FROM myview, foo, bar 
   WHERE shared=1 AND [joins and other stuff]
   ORDER BY sortcode;

План запроса, как показано выше, показывает операцию Sort непосредственно перед окончательным SELECT, что я и ожидал. Есть только 35 совпадающих записей, и запрос занимает менее 2 секунд.

Но если я добавлю TOP 30, запрос займет почти 3 минуты! Использование SET ROWCOUNT так же медленно.

Если посмотреть на план запроса, теперь он сортирует все 2+ миллиона записей в myview до объединения и фильтрации.

Эта "сортировка" отображается в плане запроса как индексное сканирование в индексе sortcode, кластеризованный индексный поиск в главной таблице и вложенный шлейф между ними, все перед объединениями и фильтрами.

Как заставить SQL Server Sort непосредственно перед TOP, например, когда TOP не указан?

Я не думаю, что конструкция myview является проблемой, но на всякий случай это что-то вроде этого:

CREATE VIEW myview AS
   SELECT columns..., sortcode, 0 as shared FROM mytable
   UNION ALL
   SELECT columns..., sortcode, 1 as shared FROM [anotherdb].dbo.mytable

Локальный mytable имеет несколько тысяч записей, а mytable в другой базе данных в том же экземпляре MSSQL имеет несколько миллионов записей. Обе таблицы имеют индексы в соответствующем столбце sortcode.

Ответ 1

И так начинается неудачная игра "попытка перехитрить оптимизатора (потому что он не всегда знает лучше" ).

Вы можете попробовать помещать части фильтрации в подзапрос или CTE:

SELECT TOP 30 *
FROM
   (SELECT *
   FROM myview, foo, bar 
   WHERE shared=1 AND [joins and other stuff]) t
ORDER BY sortcode;

Этого может быть достаточно, чтобы заставить его сначала фильтровать (но оптимизатор получает "умнее" с каждой версией и иногда может видеть через такие махинации). Или вам, возможно, придется довести этот код до UDF. Если вы пишете UDF как функцию с множеством таблиц, с фильтрацией внутри, а затем запрашиваете этот UDF с помощью TOP x/ORDER BY, вы довольно хорошо вынудили порядок запросов (поскольку SQL Server в настоящее время не может оптимизировать вокруг многоуровневых UDF).

Конечно, думая об этом, введение UDF - это просто способ скрыть то, что мы действительно делаем - создать временную таблицу, использовать один запрос для ее заполнения (на основе WHERE-фильтров), затем еще один запрос, чтобы найти TOP x из таблицы temp.