ВЫБРАТЬ ТОП медленно, независимо от ЗАКАЗА ПО

Question

У меня есть довольно сложный запрос в 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 index, поиск кластеризованного индекса в главной таблице и вложенный цикл между ними, все до объединений и фильтров.

Как я могу заставить 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 колонка.

Answer 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 - это просто способ скрыть то, что мы на самом деле делаем - создать временную таблицу, использовать один запрос для ее заполнения (на основе фильтров WHERE), а затем другой запрос, чтобы найти TOP x из временной таблицы.