Главная » Microsoft SQL Server, Базы данных » Сложные логические решения

0

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

. Сценарий Ch2 о Kill The Curs or. sql содержит инструкции DDL, предназна- ‘Щц/у В ченные для создания учебной базы данных и ее таблиц. Этот сценарий генери- Сети рует случайные данные любой величины, а затем тестирует на них все методы, ^ ‘      предлагаемые в настоящей главе, на предмет производительности. Текущую

версию этого файла можно загрузить с сайта www. SQLServerBible . com.

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

Пример сложной логической задачи

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

Переменные формулы:

^1 — обычная ситуация. БазоваяСтавка*Количество*БазовыйМножительДействия;

^2— прогрессивная ставка. БазоваяСтавка*Количество*ПеременныйПрогрессив- ныйКоэффициент;

^3 — прототип. Количество*БазовыйМножительДействия.

Исключения:

-Ф- если по заказу существует Executive OverRide, то игнорировать БазовыйМножительДей- ствия;

I                 ^ если транзакция выполняется в выходные, то умножить сумму на 2,5;

I

| -Ф- постоянные клиенты получают скидку в 20% на базовую ставку;

I                 ^ базовая ставка равна нулю, если выполняется благотворительный заказ.

Проанализируем, что мы имеем: три формулы и четыре исключения. Обычно этого достаточно, чтобы приступить к написанию курсора… но стоит ли?

Программирование логики

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

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

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

CREATE PROC CalcAdjAmount (

@DetailID INT,

@AdjustedAmount NUMERIC(7,2) OUTPUT )

AS

SET NoCount ON

– Получение идентификатора

DECLARE

@Formula SMALLINT,

@AccRate NUMERIC (7,4),

@IgnoreBaseMultiplier BIT,

@TransDate INT,

@ClientTypeID INT SELECT @Formula = Formula FROM Detail JOIN ActionCode

ON Detail.ActionCode = ActionCode.ActionCode WHERE DetaillD = @DetailID SET @IgnoreBaseMultiplier = 0 SELECT @IgnoreBaseMultiplier = ExecOverRide FROM [Order]

JOIN Detail

ON [Order].OrderlD = Detail.OrderlD WHERE DetaillD = @DetailID — Обычная формула IF ©Formula = 1 BEGIN

IF @IgnoreBaseMultiplier = 1

SELECT @AdjustedAmount = BaseRate * Amount FROM Detail JOIN ActionCode

ON Detail.ActionCode = ActionCode.ActionCode WHERE DetaillD = @DetailID

ELSE

SELECT @AdjustedAmount = BaseRate * Amount * BaseMultiplier FROM Detail JOIN ActionCode

ON Detail.ActionCode = ActionCode.ActionCode WHERE DetaillD = @DetailID

END

– 2-Прогрессивная ставка: BaseRate * Amount * Acceleration Rate IF ©Formula = 2 BEGIN

SELECT @AccRate = Value FROM dbo.Variable WHERE Name = ‘AccRate’

SELECT ©AdjustedAmount = BaseRate * Amount * @AccRate FROM Detail JOIN ActionCode

ON Detail.ActionCode = ActionCode.ActionCode WHERE DetaillD = ©DetaillD

END

– 3-Прототип: Amount * ActionCode’s BaseMultiplier IF @Formula = 3 BEGIN

IF @IgnoreBaseMultiplier = 1

SELECT @AdjustedAmount = Amount FROM Detail JOIN ActionCode

ON Detail.ActionCode = ActionCode.ActionCode WHERE DetaillD = @DetailID

ELSE

SELECT @AdjustedAmount = Amount * BaseMultiplier FROM Detail JOIN ActionCode

ON Detail.ActionCode = ActionCode.ActionCode WHERE DetaillD = @DetailID

END

– Исключение: Надбавка за выходные дни SELECT @TransDate = DatePart(dw,TransDate),

@ClientTypeID =       ClientTypelD

FROM [Order]

JOIN Detail

ON [Order].OrderlD = Detail.OrderlD JOIN Client

ON Client.ClientID = [Order] .OrderlD WHERE DetaillD = @DetailID IF @TransDate = 1 OR @TransDate = 7

SET ©AdjustedAmount = @AdjustedAmount * 2.5 — Исключение: Скидка постоянным клиентам IF ©ClientTypelD = 1

SET @AdjustedAmount = @AdjustedAmount * .8 IF ©ClientTypelD = 2

SET @AdjustedAmount = 0 RETURN

Курсор SQL-92 с хранимой процедурой

Исходное решение использует традиционный метод — последовательный проход по всем строкам с вызовом для каждой из них хранимой процедуры и обновлением значений. Именно такой метод программирования должен был заменить SQL:

– Этап 1

DECLARE cDetail CURSOR FOR SELECT DetaillD FROM Detail

WHERE AdjAmount IS NULL FOR READ ONLY — Этап 2 OPEN cDetail

— Этап 3

FETCH cDetail INTO @cDetailID — prime the cursor EXEC CalcAdjAmount

@DetailID = @cDetailID,

@AdjustedAmount = @SprocResult OUTPUT UPDATE Detail

SET AdjAmount = @SprocResult WHERE DetaillD = @cDetailID WHILE @@Fetch_Status = 0 BEGIN

BEGIN

EXEC CalcAdjAmount

@DetailID = @cDetailID,

@AdjustedAmount = @SprocResult OUTPUT UPDATE Detail

SET AdjAmount = @SprocResult WHERE DetaillD = @cDetailID

END

FETCH cDetail INTO @cDetailID — fetch next END — Этап 4 CLOSE cDetail — Этап 5

DEALLOCATE cDetail

Курсор прямого доступа с хранимой процедурой

Второе итеративное решение использует так называемый “высокопроизводительный” курсор Т-SQL. Во всем остальном это решение совпадает с предложенным в предыдущем разделе: — Этап 1

DECLARE cDetail CURSOR FAST_FORWARD READ_ONLY FOR SELECT DetaillD FROM Detail WHERE AdjAmount IS NULL — Этап 2 OPEN cDetail — Этап 3

FETCH cDetail INTO @cDetailID – подготовка курсора EXEC CalcAdjAmount

@DetailID = @cDetailID,

@AdjustedAmount = @SprocResult OUTPUT UPDATE Detail

SET AdjAmount = @SprocResult WHERE DetaillD = @cDetailID WHILE @@Fetch_Status = 0 BEGIN BEGIN

EXEC CalcAdjAmount

@DetailID = @cDetailID,

@AdjustedAmount = @SprocResult OUTPUT UPDATE Detail

SET AdjAmount = @SprocResult WHERE DetaillD = @cDetailID

END

– Этап 3

FETCH cDetail INTO @cDetailID – переход к следующей строке

END — Этап 4 CLOSE cDetail — Этап 5

DEALLOCATE cDetail

Курсор прямого действия и пользовательская функция

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

– Этап 1

DECLARE cDetail CURSOR FAST_FORWARD READ_ONLY FOR SELECT DetaillD FROM Detail

WHERE AdjAmount IS NULL — Этап 2 OPEN cDetail — Этап 3

FETCH cDetail INTO @cDetailID – подготовка курсора UPDATE Detail

SET AdjAmount = dbo.fCalcAdjAmount(@cDetailID)

WHERE DetaillD = @cDetailID WHILE @@Fetch_Status = 0 BEGIN

UPDATE Detail

SET AdjAmount = dbo.fCalcAdjAmount(@cDetailID)

WHERE DetaillD = @cDetailID

END

– Этап 3

FETCH cDetail INTO @cDetailID – переход к следующей строке — Этап 4 CLOSE cDetail — Этап 5

DEALLOCATE cDetail

Курсор обновления с хранимой процедурой

Решение, использующее курсор обновления, реализует ту же логику, что и предыдущее. Основное отличие состоит в том, что сам курсор используется для выбора корректной строки для инструкции UPDATE. Этот курсор также вызывает хранимую процедуру для каждой строки: — Этап 1

DECLARE cDetail CURSOR FAST_FORWARD READ_ONLY FOR SELECT DetaillD FROM Detail WHERE AdjAmount IS NULL FOR Update of AdjAmount — Этап 2 OPEN cDetail — Этап 3

FETCH cDetail INTO @cDetailID – подготовка курсора EXEC CalcAdjAmount

@DetailID = @cDetailID,

@AdjustedAmount = @SprocResult OUTPUT UPDATE Detail

SET AdjAmount = ©SprocResult WHERE Current of cDetail WHILE @@Fetch_Status = 0 BEGIN BEGIN

EXEC CalcAdjAmount

@DetailID = @cDetailID,

@AdjustedAmount = @SprocResult OUTPUT UPDATE Detail

SET AdjAmount = @SprocResult WHERE Current of cDetail

END

FETCH cDetail INTO @cDetailID – переход к следующей строке END — Этап 4 CLOSE cDetail — Этап 5

DEALLOCATE cDetail

Запрос обновления с пользовательской функцией

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

UPDATE dbo.Detail

SET AdjAmount = dbo.fCalcAdjAmount(DetaillD)

WHERE AdjAmount IS NULL

Использование множества запросов

Шестое по счету решение использует отдельные запросы для каждой формулы и исключения. Предложение WHERE в запросе отсекает только те строки, которые соответствуют требованиям формулы или исключения.

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

UPDATE dbo.Detail

SET AdjAmount = BaseRate * Amount FROM Detail

JOIN ActionCode

ON Detail.ActionCode = ActionCode.ActionCode JOIN [Order]

ON [Order].OrderlD = Detail.OrderlD WHERE (Formula = 1 OR Formula = 3 )AND ExecOverRide = 1 AND AdjAmount IS NULL

UPDATE dbo.Detail

SET AdjAmount = BaseRate * Amount * BaseMultiplier FROM Detail

JOIN ActionCode

ON Detail.ActionCode = ActionCode.ActionCode JOIN [Order]

ON [Order] .OrderlD = Detail.OrderlD WHERE Formula = 1 AND ExecOverRide = 0 AND AdjAmount IS NULL

– 2-Accelerated BaseRate * Amount * Acceleration Rate UPDATE dbo.Detail

SET AdjAmount = BaseRate * Amount * (SELECT Value

FROM dbo.Variable WHERE Name = ’AccRate1)

FROM Detail

JOIN ActionCode

ON Detail.ActionCode = ActionCode.ActionCode JOIN [Order]

ON [Order].OrderlD = Detail.OrderlD WHERE Formula = 2

AND AdjAmount IS NULL

– 3-Prototype Amount * ActionCode’s BaseMultiplier UPDATE dbo.Detail

SET AdjAmount = Amount * BaseMultiplier FROM Detail JOIN ActionCode ON Detail.ActionCode = ActionCode.ActionCode JOIN [Order]

ON [Order].OrderlD = Detail.OrderlD WHERE Formula = 3 AND ExecOverRide = 0 AND AdjAmount IS NULL

– Исключения

– Корректировка выходных дней UPDATE dbo.Detail

SET AdjAmount = AdjAmount * Multiplier FROM Detail JOIN [Order]

ON [Order].OrderlD = Detail.OrderlD JOIN DayOfWeekMultipiier DWM

ON CAST(DatePart(dw,[Order].TransDate) as SMALLINT) DWM.DayOfWeek

– Корректировка по клиентам UPDATE dbo.Detail

SET AdjAmount = AdjAmount * Multiplier FROM Detail JOIN [Order]

ON [Order].OrderlD = Detail.OrderlD JOIN Client

ON [Order].ClientID = Client.ClientID Join ClientType

ON Client.ClientTypelD = ClientType.ClientTypelD

Запросы с выражением case

Последнее решение использует выражение CASE и управляемые данными значения для разрешения сложности с помощью всего одного запроса. Главенствующая роль выражения CASE сводится к тому, что оно способно внедрить гибкую логику в один запрос.

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

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

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

UPDATE dbo.Detail

SET AdjAmount = DWM.Multiplier * ClientType.Multiplier *

CASE

WHEN ActionCode.Formula = 1 AND ExecOverRide = 0 THEN BaseRate * Amount * BaseMultiplier WHEN (ActionCode.Formula = 1 OR ActionCode.Formula = 3 )

AND ExecOverRide = 1 THEN BaseRate * Amount WHEN ActionCode.Formula = 2

THEN BaseRate * Amount * (SELECT Value

FROM dbo.Variable WHERE Name = ‘AccRate’)

WHEN (Formula = 3 AND ExecOverRide = 0)

THEN Amount * BaseMultiplier

END FROM Detail

JOIN ActionCode

ON Detail.ActionCode = ActionCode.ActionCode JOIN [Order]

ON [Order].OrderlD = Detail.OrderlD JOIN Client

ON [Order].ClientID = Client.ClientID Join ClientType

ON Client.ClientTypeID = ClientType.ClientTypeID JOIN DayOfWeekMultiplier DWM

ON CAST(DatePart(dw,[Order].TransDate) as SMALLINT) = DWM.DayOfWeek WHERE AdjAmount IS NULL

Анализ производительности

Для тестирования производительности предложенных семи методов с помощью сценария KilltheCursor. sql таблицы были заполнены данными, и каждый метод был выполнен три раза. Запуск десяти итераций позволил оценить производительность и масштабируемость решений (рис. 20.1).

Самую низкую производительность показало решение, использующее курсор обновления; ненамного его опередили остальные три решения, использующие курсоры. Кривые их графиков резко устремляются вверх. Это значит, что время выполнения растет быстрее, чем объем данных, что означает плохую масштабируемость.

Рис. 20.1. Как видно по результатам тестирования производительности, курсоры практически не масштабируемы

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

Особо выделяется на графике пунктирная линия, соответствующая запросу с выражением CASE. Несмотря на то что программный код кажется громоздким и медленным, он показал наилучшую производительность и масштабируемость — это решение даже обогнало все остальные, использующие запросы. Спрашивается, почему? Да потому, что из всех решений именно запросы с выражениями CASE передают наибольшее управление оптимизатору запросов SQL Server.

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

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