Повышение производительности распределенного запроса

sql.ru

По материалам статьи 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).