Когда речь идет об улучшении производительности запроса в SQL Server, доступны несколько вариантов, таких как индексы, статистика и настройки конфигурации. Однако в некоторых случаях эти функции могут быть недоступны, особенно в базах данных поставщиков. В таких сценариях переписывание запросов становится единственным вариантом для повышения производительности.

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

Для демонстрации мы будем использовать дамп данных StackOverflow 2014. Давайте начнем с рассмотрения конкретного сценария запроса: определение первого человека, получившего каждую награду на StackOverflow.

Для достижения этой цели мы можем использовать оконные функции в SQL Server. Следующий запрос демонстрирует, как получить первого пользователя, награжденного каждой наградой:

SELECT DISTINCT Name, FIRST_VALUE(UserId) OVER (PARTITION BY Name ORDER BY Date, UserId) AS UserId
FROM dbo.Badges b
ORDER BY Name, UserId

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

При дальнейшем анализе мы можем выявить причину медленной производительности. Включив опцию STATISTICS IO, мы видим, что SQL Server считывает значительное количество страниц из некластеризованного индекса. Поскольку мы не фильтруем данные, ограниченные возможности для улучшения операции чтения.

Кроме того, запрос включает несколько операторов, таких как Segment, Window Spool, Stream Aggregate, Compute Scalar и Hash Match. Эти операторы способствуют медленной производительности путем записи и чтения большого объема данных из tempdb.

Для решения этих проблем производительности мы можем переписать запрос, используя более традиционные агрегатные функции и оператор GROUP BY:

SELECT b.Name, MIN(b.UserId) AS UserId
FROM dbo.Badges b
INNER JOIN (
    SELECT Name, MIN(Date) AS Date
    FROM dbo.Badges
    GROUP BY Name
) m ON b.Name = m.Name AND b.Date = m.Date
GROUP BY b.Name
ORDER BY Name, UserId

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

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

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