Блог

Главная - Вопросы по программированию - Ускорить разделение ROW_NUMBER()

Ускорить разделение ROW_NUMBER()

sql #sql-server #tsql

#sql #sql-сервер #tsql

Вопрос:

В настоящее время у меня есть этот запрос (SQL Server):

 DECLARE @PageNum int = 1;
DECLARE @PageSize int = 2;
SELECT *
FROM (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY Color ORDER BY Name) AS Row
  FROM Orders
) t1
WHERE Row BETWEEN ((@PageNum-1)*@PageSize 1) AND (@PageNum*@PageSize)
ORDER BY Color,Name

Что делает этот запрос, так это выбирает «страницы» с 2 записями для каждого Color . Значение, если таблица содержит:

ID Цвет Имя
1 Красный Статья №1
2 Красный Статья №2
3 Красный Статья №3
4 Зеленый Статья №4
5 Зеленый Статья №5
6 Зеленый Статья №6
7 Зеленый Статья №7

Тогда приведенный выше запрос приведет к:

ID Цвет Имя
1 Красный Статья №1
2 Красный Статья №2
4 Зеленый Статья №4
5 Зеленый Статья №5

На странице 1 и

ID Цвет Имя
3 Красный Статья №3
6 Зеленый Статья №6
7 Зеленый Статья №7

На странице 2.

Это работает. Однако в моем реальном примере выполнение этого запроса занимает очень много времени — фактически около 10 минут. У меня есть статическая таблица с более чем 800 тыс. записей, которая никогда не меняется. Поскольку эта таблица никогда не меняется, я создал индекс для каждого столбца, по которому я бы фильтровал / сортировал. В этой таблице также есть столбец RowId , который практически является просто номером строки, и он имеет уникальный индекс.

План выполнения можно увидеть здесь: https://www.brentozar.com/pastetheplan/?id=BkeWfVCSY

Что я могу сделать, чтобы ускорить этот запрос?

Комментарии:

1. Также похоже, что вы изобретаете LIMIT и OFFSET . Пока вы используете 2012 или более позднюю версию, вам не нужно этого делать. Я видел, что некоторые очень плохие планы улучшаются с LIMIT помощью and OFFSET .

2. @DaleK Я обновил свой вопрос. Спасибо, что указали на это

3. @Mitch Согласно плану выполнения, похоже, что проблема не в этом. Даже если я оставлю предложение WHERE, ничего не изменится.

4. Вам нужен индекс для [OrderType] desc, [From] asc или [OrderType] asc, [From] DESC , чтобы избавиться от операции сортировки.

5. вы пробовали OFFSET/FETCH ? Возможно, вы захотите посмотреть разбивку на страницы набора ключей , которая намного эффективнее, чем разбивка на страницы набора строк, которую вы выполняете.

Ответ №1:

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

Кроме того, поскольку это статическая таблица, которая никогда не изменяется, согласно описанию, фрагментация не должна быть проблемой.

У меня на этом компьютере не установлен SQL Server, поэтому я не могу сравнить планы выполнения до и после, но я считаю, что для вашего запроса мы можем увидеть некоторые улучшения с кластеризованным индексом, упорядочивая ключи, как предлагает Мартин Смит в своем комментарии.

 CREATE CLUSTERED INDEX IX_Order ON dbo.Order(OrderType ASC, From DESC)

Пожалуйста, дайте мне знать, если это улучшит ваш запрос. Если нет, я могу создать аналогичный сценарий для тестирования в течение дня.

Метки: