Складні логічні рішення

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

. Сценарій 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 DetailActionCode = ActionCodeActionCode WHERE DetaillD = @DetailID SET @IgnoreBaseMultiplier = 0 SELECT @IgnoreBaseMultiplier = ExecOverRide FROM [Order]

JOIN Detail

ON [Order] OrderlD = DetailOrderlD WHERE DetaillD = @ DetailID – Звичайна формула IF © Formula = 1 BEGIN

IF @IgnoreBaseMultiplier = 1

SELECT @AdjustedAmount = BaseRate * Amount FROM Detail JOIN ActionCode

ON DetailActionCode = ActionCodeActionCode WHERE DetaillD = @DetailID

ELSE

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

ON DetailActionCode = ActionCodeActionCode WHERE DetaillD = @DetailID

END

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

SELECT @AccRate = Value FROM dboVariable WHERE Name = AccRate

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

ON DetailActionCode = ActionCodeActionCode WHERE DetaillD = ©DetaillD

END

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

IF @IgnoreBaseMultiplier = 1

SELECT @AdjustedAmount = Amount FROM Detail JOIN ActionCode

ON DetailActionCode = ActionCodeActionCode WHERE DetaillD = @DetailID

ELSE

SELECT @AdjustedAmount = Amount * BaseMultiplier FROM Detail JOIN ActionCode

ON DetailActionCode = ActionCodeActionCode WHERE DetaillD = @DetailID

END

– Виняток: Надбавка за вихідні дні SELECT @ TransDate = DatePart (dw, TransDate),

@ClientTypeID =       ClientTypelD

FROM [Order]

JOIN Detail

ON [Order]OrderlD = DetailOrderlD JOIN Client

ON ClientClientID = [Order] OrderlD WHERE DetaillD = @DetailID IF @TransDate = 1 OR @TransDate = 7

SET © AdjustedAmount = @ AdjustedAmount * 25 – Виняток: Знижка постійним клієнтам 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 = dbofCalcAdjAmount(@cDetailID)

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

UPDATE Detail

SET AdjAmount = dbofCalcAdjAmount(@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 dboDetail

SET AdjAmount = dbofCalcAdjAmount(DetaillD)

WHERE AdjAmount IS NULL

Використання безлічі запитів

Шосте за рахунком рішення використовує окремі запити для кожної формули і винятки Пропозиція WHERE в запиті відсікає тільки ті рядки, які відповідають вимогам формули або виключення

У цьому рішенні присутній компонент, керований даними Прогресивний коефіцієнт витягується з таблиці Variable за допомогою скалярного підзапиту, а виключення обробляються за допомогою керованих даними обєднань з таблицями Client Туре і DayOfWeekMultiplyer:

UPDATE dboDetail

SET AdjAmount = BaseRate * Amount FROM Detail

JOIN ActionCode

ON DetailActionCode = ActionCodeActionCode JOIN [Order]

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

UPDATE dboDetail

SET AdjAmount = BaseRate * Amount * BaseMultiplier FROM Detail

JOIN ActionCode

ON DetailActionCode = ActionCodeActionCode JOIN [Order]

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

— 2-Accelerated BaseRate * Amount * Acceleration Rate UPDATE dboDetail

SET AdjAmount = BaseRate * Amount * (SELECT Value

FROM dboVariable WHERE Name = ’AccRate1)

FROM Detail

JOIN ActionCode

ON DetailActionCode = ActionCodeActionCode JOIN [Order]

ON [Order]OrderlD = DetailOrderlD WHERE Formula = 2

AND AdjAmount IS NULL

— 3-Prototype Amount * ActionCodes BaseMultiplier UPDATE dboDetail

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

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

– Винятки

– Коригування вихідних днів UPDATE dboDetail

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

ON [Order]OrderlD = DetailOrderlD JOIN DayOfWeekMultipiier DWM

ON CAST(DatePart(dw,[Order]TransDate) as SMALLINT) DWMDayOfWeek

– Коригування за клієнтам UPDATE dboDetail

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

ON [Order]OrderlD = DetailOrderlD JOIN Client

ON [Order]ClientID = ClientClientID Join ClientType

ON ClientClientTypelD = ClientTypeClientTypelD

Запити з виразом case

Останнє рішення використовує вираз CASE і керовані даними значення для дозволу складності за допомогою всього одного запиту Чільна роль вираження CASE зводиться до того, що воно здатне впровадити гнучку логіку в один запит

Керовані даними значення і формули також впроваджені в запит за допомогою обєднань з коректними значеннями таблиць класифікаторів Конструкції, керовані даними, також знижують витрати на експлуатацію систем, оскільки в них можна змінити значення без внесення правок в програмний код

У даному прикладі вираз CASE вибирає коректну формулу, грунтуючись на значеннях таблиці ActionCode Використання цих значень у прикладі жорстко прошито в інструкції, однак при бажанні цей процес можна зробити також керованим даними

Як і у вирішенні з безліччю запитів, прогресивний коефіцієнт і виключення управляються даними:

UPDATE dboDetail

SET AdjAmount = DWMMultiplier * ClientTypeMultiplier *

CASE

WHEN ActionCodeFormula = 1 AND ExecOverRide = 0 THEN BaseRate * Amount * BaseMultiplier WHEN (ActionCodeFormula = 1 OR ActionCodeFormula = 3 )

AND ExecOverRide = 1 THEN BaseRate * Amount WHEN ActionCodeFormula = 2

THEN BaseRate * Amount * (SELECT Value

FROM dboVariable WHERE Name = AccRate)

WHEN (Formula = 3 AND ExecOverRide = 0)

THEN Amount * BaseMultiplier

END FROM Detail

JOIN ActionCode

ON DetailActionCode = ActionCodeActionCode JOIN [Order]

ON [Order]OrderlD = DetailOrderlD JOIN Client

ON [Order]ClientID = ClientClientID Join ClientType

ON ClientClientTypeID = ClientTypeClientTypeID JOIN DayOfWeekMultiplier DWM

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

Аналіз продуктивності

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

Найнижчу продуктивність показало рішення, що використовує курсор оновлення ненабагато його випередили інші три рішення, які використовують курсори Криві їх графіків різко спрямовуються вгору Це означає, що час виконання зростає швидше, ніж обсяг даних, що означає погану масштабованість

Рис 201 Як видно за результатами тестування продуктивності, курсори практично не масштабуються

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

Особливо виділяється на графіку пунктирна лінія, відповідна запиту з виразом CASE Незважаючи на те що програмний код здається громіздким і повільним, він показав найкращу продуктивність і масштабованість – Це рішення навіть обігнало всі інші, що використовують запити Питається, чому Та тому, що з усіх рішень саме запити з виразами CASE передають найбільшу управління оптимізаторові запитів SQL Server

Всі проведені тести показали, що курсори працюють як мінімум у десять разів повільніше пакетів інструкцій, до того ж вельми погано масштабуються При деякої оптимізації різниця в продуктивності може досягти і більших величин Насправді на практиці я зустрічав курсор, який працював 7:00, в той час як перероблене в пакет рішення впоралося із завданням всього за три хвилини Впровадження складної логіки в курсор може здатися привабливим, проте платити за це доведеться колосальними втратами продуктивності

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*