Блог

Этот запрос стал моим заклятым врагом за последние несколько лет, потому что я так и не нашел способа его оптимизировать. Теперь мой заклятый враг становится вашим заклятым врагом! 🙂

Рассмотрим следующую таблицу:

 create table Sales (
  SaleId int identity(1,1) primary key,
  SalesmanId int not null,
  Amount smallmoney not null
)
  

В качестве аргумента предположим, что в этой таблице 10 ^ 100 строк (бизнес идет быстро), и поэтому о проверке таблицы не может быть и речи.

Теперь мы хотим определить SaleID самой последней продажи каждого продавца. Достаточно просто, не так ли? Вот запрос для этого:

 select
  SalesmanId,
  max(SaleId) SaleId
from Sales
group by Sales.SalesmanId
  

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

 create unique nonclustered index IX_Sales on Sales
(
  SalesmanId asc,
  SaleId asc
)
  

Теперь найти самые последние значения должно быть тривиально (во всяком случае, для человека), потому что мы используем значения первого столбца индекса для идентификации всех возможных продавцов, а последние записи второго столбца — для определения последней продажи каждого продавца. К сожалению, оптимизатор запросов по-прежнему выполняет поиск по индексу по всему индексу (все 10 ^ 100 строк) в этом случае, поэтому это занимает столько же времени.

Интересно, что если мы напишем запрос для поиска последних продаж для данного продавца,

 select max(SaleId)
from Sales
where SalesmanId = 1
  

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

 select max(SaleId)
from Sales
where SalesmanId = 1
group by SalesmanId
  

или

 select max(SaleId)
from Sales
group by SalesmanId
having SalesmanId = 1
  

мы вернулись к поиску индекса с большим количеством строк (хотя и меньшим, чем если бы вы вообще опустили фильтр, опять же, вероятно, из-за статистики).

Итак… есть идеи о том, как я мог бы победить своего врага?

Обновить

Некоторые предлагают объединить с таблицей возможных значений SalesmanId, например, так

 select Latest.*
from
(
  select 
    SalesmanId,
    max(SaleId) SaleId
  from Sales
  group by SalesmanId
) Latest
inner join Salesmen on 
  Salesmen.SalesmanId = Latest.SalesmanId
  

Я тестировал эту идею, но оптимизатор запросов по-прежнему выбирает полное сканирование таблицы.

Вот решение, которое использует подход, аналогичный вашему решению cursor.

 SELECT
   salesmanId, 
   (SELECT MAX(saleid) FROM sales WHERE salesmanid = salesmen.salesmanId) AS MaxSaleId
FROM salesmen
  

План выполнения показывает, что он использует поиск в таблице sales.

Мыслите нестандартно. Всякий раз, когда происходит продажа, обновляйте столбец в таблице salesman, чтобы ссылаться на самый последний saleid. Мы все попадаем в ловушку нормализации. Иногда лучше быть избыточным. Смотрите CQRS для доведения этого до крайности.

Надеюсь, это поможет.

Поскольку вы заявили, что это:

 select max(SaleId)
from Sales
where SalesmanId = 1
  

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

Оптимизатор работает лучше, если вы разбиваете по SalesmanID (с соответствующим индексом для каждой таблицы и ограничением ПРОВЕРКИ для таблицы)??

» создайте уникальный некластеризованный индекс IX_Sales для Sales (SalesmanId asc, SaleID asc)

Теперь найти самые последние значения должно быть тривиально (во всяком случае, для человека), потому что мы используем значения первого столбца индекса для идентификации всех возможных продавцов, а последние записи второго столбца — для определения последней продажи каждого продавца. К сожалению, оптимизатор запросов по-прежнему выполняет поиск по индексу по всему индексу (все 10 ^ 100 строк) в этом случае, поэтому это занимает столько же времени.»

Конечно, но я готов поспорить, что компьютер все еще делает это быстрее, чем мог бы человек.

В любом случае, рассмотрим это другое объявление индекса:

 create unique nonclustered index IX_Sales on Sales
    (
      SalesmanId asc,
      SaleId DESC
    )
  

Теперь MAX (SaleID) является первой строкой в индексе для каждого продавца. Это должно быть намного быстрее. Вы можете подумать, что посвящать целый индекс решению одного запроса довольно экстравагантно, но победа над своим заклятым врагом иногда требует отчаянных мер!

Я говорю о решении только одного запроса, потому что этот индекс не поможет с другими запросами, которые вы упомянули в комментарии:

«Какова последняя продажа на одного продавца на суммы, превышающие 1000 долларов?» или «Какова последняя продажа на одного продавца за каждый из последних 12 месяцев?»

Увы, у вас не может быть единого решения для всех ваших запросов, связанных с датой, в такой огромной таблице. Решение этих проблем является причиной, по которой организации создают хранилища данных с причудливыми структурами, называемыми измерениями и таблицами фактов, и большими серверами grunt, которые могут выполнять запросы параллельно.

Хорошо, я собираюсь попытаться ответить на свой собственный вопрос, рискуя оскорбить весь sql-сообщество таким подходом.

 declare @Result table (
  SalesmanId int not null primary key,
  SaleId int not null
)

declare @SalesmanId int
declare Salesman cursor local fast_forward for
  select SalesmanId 
  from Salesmen
open Salesman   
fetch next from Salesman into @SalesmanId

while @@FETCH_STATUS = 0
begin

  insert @Result (
    SalesmanId, 
    SaleId
  )
  select 
    @SalesmanId SalesmanId,
    max(SaleId) SaleId
  from Sales
  where SalesmanId = @SalesmanId

  fetch next from Salesman into @SalesmanId

end

close Salesman
deallocate Salesman

select *
from @Result
  

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