По материалам статьи Umachandar Jayachandran на sqlmag.com: Speeding Up the Query

Поздравляем Кена Хали (Ken Haley), менеджера по разработкам в SPECTRUM Human Resource Systems (Денвер, Колорадо) и Ивена Стюарта (Ewen Stewart), программного архитектора из Active Media Australia (Мельбурн, Австралия). Кен выиграл первый приз в размере $100 за лучшее решение задачи "Ускорение запроса" (августовское соревнование среди читателей). Ивен получил второй приз в размере $50.
В данной статье приводится резюме найденного решения задачи.

Постановка задачи

Решая проблему низкой производительности распределенного запроса в коде приложения, Дональд обнаружил, что SQL Server выбирает неэффективный план выполнения. Распределенный запрос выполняет операцию join между большой таблицей, расположенной на удаленном сервере Prod1, и маленькой таблицей, расположенной на локальном сервере Prod2, причем получает большее количество столбцов из удаленной таблицы. Однако SQL Server в начале получает все строки удаленной таблицы, после чего производит операцию join на локальном сервере.

Для отладки запроса Дональд создал тестовый сценарий, используя таблицы Orders и Order Details базы данных Northwind. На удаленном сервере он создал следующую тестовую таблицу:

-- На удаленном сервере Prod1

USE Northwind

SELECT od.OrderID, od.ProductID, od.UnitPrice, od.Quantity, 

 od.Discount

 INTO [New Order Details]

 FROM (

 SELECT TOP 500000 od1.OrderID, od1.ProductID, 

 od1.UnitPrice, od1.Quantity, od1.Discount

 FROM [Order Details] AS od1

 CROSS JOIN [Order Details] AS od2

 ORDER BY od1.OrderID

 ) AS od

CREATE CLUSTERED INDEX IX_NewOrderDetail_ID ON 

 [New Order Details]( OrderID )

Затем на локальном сервере он написал следующий тестовый запрос, который похож на его распределенный запрос:

-- На локальном сервере Prod2

SELECT o.OrderID, od.ProductID, od.UnitPrice, od.Quantity, 

 od.Discount

 FROM Northwind.dbo.Orders AS o

 JOIN Prod1.Northwind.dbo.[New Order Details] AS od

 ON od.OrderID = o.OrderID

Необходимо помочь Дональду оптимизировать запрос (уменьшить время выполнения запроса и улучшить производительность ввода/вывода).

Решение

Дональд решил использовать опцию REMOTE JOIN, так как она заставляет SQL Server 2000 (и SQL Server 7.0) выполнять операцию join не на локальном, а на удаленном сервере. Поскольку удаленная таблица содержит значительно больше строк, чем локальная таблица, использование опции REMOTE JOIN увеличивает скорость выполнения распределенного запроса Дональда. Опция REMOTE JOIN полезна, когда левая таблица в запросе локальна, а правая является удаленной.

Дональд изменил тестовый запрос, добавив опцию REMOTE JOIN как показано в приведенном коде:

SELECT o.OrderID, od.ProductID, od.UnitPrice, od.Quantity, 

 od.Discount

 FROM Northwind.dbo.Orders AS o

 INNER REMOTE JOIN Prod1.Northwind.dbo.[New Order Details] 

 AS od

 ON od.OrderID = o.OrderID

В результате анализа результата исполнения запроса в SQL Server Profiler Дональд убедился, что использование REMOTE JOIN увеличивает производительность запроса (он мог также выполнить команду SET STATISTICS TIME ON в Query Analyzer и посмотреть статистику исполнения запроса после его исполнения). Следующий пример содержит статистику, которую отображает Profiler для двух запросов:

Test Run 1

* CPU: 28581, Reads: 500302, Writes: 3, Duration: 90550 

 (without hint)

* CPU: 12708, Reads: 50, Writes: 0, Duration: 77593 

 (with REMOTE JOIN hint)

Test Run 2

* CPU: 33057, Reads: 500321, Writes: 0, Duration: 123840 

 (without hint)

* CPU: 14010, Reads: 50, Writes: 0, Duration: 97840 

 (with REMOTE JOIN hint)

Test Run 3

* CPU: 33057, Reads: 500321, Writes: 0, Duration: 125483 

 (without hint)

* CPU: 14391, Reads: 50, Writes: 0, Duration: 102446 

 (with REMOTE JOIN hint).