Все о курсорах

Tsql теория > Все о курсорах
26.02.2013 11:44:30


Наиболее часто встречающиеся слова в статье:

[ActionCode] [cDetail] [AdjAmount] [курсора] [cDetailID] [DetaillD] [OrderlD] [AdjustedAmount] [решение] [DetailID]


Статья:

По своей сути курсор является указателем на одну строку данных. Для прохождения по набору данных до его конца обычно используют цикл while. SQL Server поддерживает стандартный синтаксис ANSI SQL-92 и расширенный синтаксис Т-SQL, предлагающий дополнительные возможности.

Пять этапов жизни курсора

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

1. Объявление курсора определяет тип и режим его работы, а также описывает инструкцию SELECT, поставляющую ему данные. При объявлении курсора никакие данные не извлекаются. Это единственный случай, когда инструкция declare не требует амперсанда. Курсор SQL-92 объявляется с помощью инструкции cursor for:

DECLARE имя_курсора CURSOR FOR инструкция_БЕЬЕСТ FOR параметры_курсора

Расширенный курсор T-SQL объявляется аналогичным образом:

DECLARE имя_курсopa CURSOR параметры_курсора FOR инструкция_5ЕЬЕСТ

2. При открытии курсора извлекаются данные, которыми он и заполняется:

OPEN имя_курсора

3. Курсор перемещается к следующей строке и заполняет значениями ее столбцов локальные переменные (эти переменные должны быть предварительно объявлены): FETCH [направление] имя_курсора INTO @переменная1, @переменная2

По умолчанию команда FETCH перемещает курсор к следующей строке (направление NEXT), однако при желании можно переместить курсор к предыдущей (PRIOR), первой (FIRST) и последней (LAST) строке. Также с помощью этой команды можно переместить курсор к строке с некоторым абсолютным номером (ABSOLUTE п) или сместить относительно текущей позиции на определенное расстояние (RELATIVE п). Проблема последнего подхода состоит в том, что в реляционной базе данных номер строки не имеет определенного смысла. Если в коде требуется перейти к конкретной строке, чтобы получить логический результат, то это должно быть заранее предусмотрено в модели базы данных.

4. Закрытие курсора снимает блокировку данных, но сохраняет инструкцию SELECT. Курсор впоследствии может быть открыт в той же точке:

CLOSE имя_курсора

5. Демонтаж курсора высвобождает отведенную под него память и аннулирует его определение:

DEALLOCATE имя_курсора

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

Управление курсором

Так как курсор проходит по набору данных построчно, код Т-SQL требует команд постоянного смещения курсора. Для управления этим циклическим процессом язык Т-SQL предлагает две глобальные переменные, содержащие информацию о состоянии курсора.

Переменная @@cursor_rows возвращает общее количество строк в курсоре. Если курсор заполняется асинхронно, то в этой переменной будет содержаться отрицательное значение.

Существенной для управления курсором является глобальная переменная @@fetch_ status. Она отчитывается о состоянии курсора после последней команды FETCH. Это состояние важно для управления перемещением курсора и оценки, достиг ли он одного из концов набора данных. Возможные значения переменной @@f etch_status свидетельствуют о следующем.

¦ 0 — последняя операция FETCH успешно извлекла строку.

¦ 1 — последняя операция FETCH достигла конца набора данных.

¦ 2 — строка, к которой переместился курсор, оказалась недоступной; она была удалена.

Комбинирование переменной @@f etch_status с оператором while позволяет создать циклы, позволяющие успешно перемещаться по строкам набора данных.

Обычно в пакетах создается курсор, после чего выполняется первая команда FETCH и начинается цикл while, который продолжается до тех пор, пока не будет достигнут конец набора данных. В верхней части цикла проверяется значение переменной @@fetch_status для определения, создан ли курсор. В следующем примере продемонстрированы все пять этапов жизни курсора и управление итеративным процессом его перемещения с помощью цикла while и переменной @@f etch_status:

-- Этап 1

DECLARE cDetail CURSOR FAST_FORWARD

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 = <>1 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 Server знает, какая строка является текущей. Указатель курсора может использоваться в предложениях WHERE инструкций SQL DML (SELECT, INSERT, UPDATE и DELETE), чтобы манипулировать корректными данными.

Параметр FOR UPDATE инструкции DECLARE позволяет выполнять обновления с помощью курсора. При этом если явно указаны какие-либо столбцы, именно они будут обновлены; если никаких столбцов не указано, будут обновлены все.

DECLARE cDetail CURSOR FOR SELECT DetaillD FROM Detail WHERE AdjAmount IS NULL FOR UPDATE OF AdjAmount

В теле цикла, после того, как было выполнено перемещение к нужной строке, инструкция DML может включить курсор в предложение WHERE, используя синтаксис CURRENT OF. В следующем примере, взятом из сценария KilltheCursor. sql, выполняется ссылка на курсор cDetail:

UPDATE Detail

SET AdjAmount = @SprocResult WHERE CURRENT OF cDetail

Область определения курсора

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

DECLARE имя_курсора CURSOR Local или Global FOR инструкция SELECT

По умолчанию областью определения курсора является вся база данных. Это указывается в параметре CURSOR_DEFAULT:

ALTER DATABASE Family SET CURSOR_DEFAULT LOCAL

Для успешного выполнения процедур важно знать текущую область определения курсора. Это выполняется с помощью функции DATABASEPROPERTYEX () :

SELECT DATABASEPROPERTYEX('Family',    'IsLocalCursorsDefault')

Результат выполнения функции следующий:

1

Кроме параметров global и for update курсоры имеют еще несколько, управляющих возможностью перемещения и обновления данных, — static, keyset, dynamic и optimistic. Мне не хочется тратить место в книге на описание этих параметров, так как лучшей стратегией все-таки остается максимальное Проверено ограничение сферы действия курсоров и переход на пакетные решения.


Курсоры и транзакции

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

Компромисс заключается в использовании функции RowNumber () с пакетным решением для обновления больших групп строк.

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

Стратегии курсора

Ключевым моментом работы с курсорами является понимание того, когда использовать их, а когда искать решение с помощью пакетов. Если выполнить поиск совета на портале Google, он ответит примерно следующее: “Не используйте курсор, если хотя бы две недели безуспешно не поискали пакетное решение у знающих друзей и в группах новостей. Если по крайней мере три оппонента бросили от отчаяния поиск решения с помощью запросов, то можете приступать к написанию курсора”.

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

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

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

¦ Динамические программные итерации, такие как генератор инструкций DDL. Для данного случая я не вижу лучшего решения, нежели курсор.

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

¦ Создание перекрестного запроса. Это одна из задач, которые традиционно в SQL Server относили к разряду особо сложных. Создание такого запроса требует использования целого набора выражений CASE. Для создания динамического перекрестного запроса рекомендуется использовать курсор.

Дополнительная    В главе 15 продемонстрирован ряд методов создания перекрестных запросов,

информация    Среди этих методов использование традиционных выражений case, метод кур-

.. . .. -¦    сора и использование нового в SQL Server 2005 ключевого слова pivot.

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

Дополнительная    О запросах СТЕ и прочих высокопроизводительных решениях задачи обхода

информация    иерархического дерева см. в главе 12.

Сложные логические решения

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

. Сценарий 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.

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

Пример денормализации списка

Второй пример курсора в действии решает задачу денормализации списка. Нам требуется получить из дат туров Outer Banks Lighthouses разделенный запятыми список значений в виде одной строки.

Курсор проходит по всем датам туров, при этом в цикле WHILE все даты добавляются в локальную переменную @EventDates. Битовая локальная переменная @SemiColon определяет, нужен ли между датами разделитель в виде точки с запятой. В конце пакета инструкция SELECT возвращает денормализованный список дат.

USE СНА2 DECLARE

@EventDates VARCHAR(1024),

@EventDate DATETIME,

©Semicolon BIT SET @Semicoion = 0 SET @EventDates = ''

DECLARE cEvent CURSOR FAST_FORWARD FOR SELECT DateBegin FROM Event JOIN Tour

ON Event.TourlD = Tour.TourlD WHERE Tour.[Name] = 'Outer Banks Lighthouses'

OPEN cEvent

FETCH cEvent INTO @EventDate - Подготовка курсора WHILE @@Fetch_Status = 0 BEGIN

IF @Semicolon = 1 SET @EventDates

= @EventDates + ' ;    '

+ Convert(VARCHAR(15), @EventDate, 107 )

ELSE

BEGIN

SET ©EventDates

= Convert(VARCHAR(15), ©EventDate,107 )

SET @SEMICOLON = 1 END

FETCH cEvent INTO @EventDate - к следующей строке END

CLOSE cEvent DEALLOCATE cEvent

SELECT @EventDates

Будет получен следующий результат:

Feb 02, 2001; Jun Об, 2001; Jul 03, 2001; Aug 17, 2001;

Oct 03, 2001; Nov 16, 2001

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

Дополнительная Подробно о переменных с множеством присвоений см. в главе 18.

информация

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

USE СНА2 DECLARE

@EventDates VARCHAR(1024)

SET @EventDates = ''

SELECT @EventDates = @EventDates

+ CONVERT(VARCHAR(15), a.d,107 ) + ';'

FROM (select DateBegin as [d] from Event join Tour

on Event.TourlD = Tour.TourlD

WHERE Tour.[Name] = 'Outer Banks Lighthouses') as a SELECT Left(@EventDates, Len(@EventDates)-1)

AS 'Outer Banks Lighthouses Events’

Будет получен следующий результат:

Outer Banks Lighthouses Events

Feb 02, 2001; Jun 06, 2001; Jul 03, 2001; Aug 17, 2001;

Oct 03, 2001; Nov 16, 2001

Резюме

Когда оптимизация увеличивает производительность в десятки раз (от часов выполнения — в минуты, от минут — в секунды), тогда выполненная работа приносит удовлетворение. Не существует лучшего способа оптимизации хранимой процедуры, чем заменить ненужный курсор. Если вы ищете какой-то очень низко производительный продукт, тогда курсор — именно то, что вам нужно.

Разрешите процитировать моего друга Билла Вона: “Курсор придумал дьявол!” К этому мне нечего добавить. Любой курсор работает до безумия медленно. Ненужные курсоры входят в мой список пяти самых важных проблем производительности SQL Server. Лучший способ настроить курсор — это полностью заменить его элегантным пакетным запросом.

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