У цій статті наведено рішення оптимізації на Transact SQL завдання розрахунку залишків на складах. Застосовано: партикування таблиць та матеріалізованих уявлень.
Постановка задачі
Завдання необхідно вирішити на SQL Server 2014 Enterprise Edition (x64). У фірмі є багато складів. У кожному складі щодня по кілька тисяч відвантажень та приймань продуктів. Є таблиця рухів товарів складі приход/расход. Необхідно реалізувати:Розрахунок балансу на вибрану дату і час (з точністю до години) по всіх складах по кожному продукту. Для аналітики необхідно створити об'єкт (функцію, таблицю, подання) за допомогою якого за вибраний діапазон дат вивести по всіх складах та продуктах дані вихідної таблиці та додаткову розрахункову колонку – залишок на складі позиції.
Зазначені розрахунки передбачаються виконуватися за розкладом з різними діапазонами дат і повинні працювати у прийнятний час. Тобто. якщо необхідно вивести таблицю з залишками за останню годину або день, то час виконання має бути максимально швидким, так само як і якщо необхідно вивести за останні 3 роки ці дані, для подальшого завантаження в аналітичну базу даних.
Технічні подробиці. Сама таблиця:
Create table dbo.Turnover (id int identity primary key, dt datetime not null, ProductID int not null, StorehouseID int not null, Operation smallint not null check (Operation in (-1,1)), - +1 прихід на склад , -1 витрата зі складу Quantity numeric(20,2) not null, Cost money not null)
Dt - Дата час надходження/списання на/зі складу.
ProductID - Продукт
StorehouseID - склад
Operation - 2 значення прихід або витрата
Quantity – кількість продукту на складі. Може бути речовим, якщо продукт не в штуках, а, наприклад, у кілограмах.
Cost – вартість партії продукту.
Дослідження задачі
Створимо заповнену таблицю. Для того, щоб ти міг разом зі мною тестувати і дивитися результати, пропоную створити і заповнити таблицю dbo.Turnover скриптом:Якщо object_id("dbo.Turnover","U") is not null drop table dbo.Turnover; go with times as (select 1 id union all select id+1 from times where id< 10*365*24*60 -- 10 лет * 365 дней * 24 часа * 60 минут = столько минут в 10 лет)
, storehouse as
(select 1 id
union all
select id+1
from storehouse
where id < 100 -- количество складов)
select
identity(int,1,1) id,
dateadd(minute, t.id, convert(datetime,"20060101",120)) dt,
1+abs(convert(int,convert(binary(4),newid()))%1000) ProductID, -- 1000 - количество разных продуктов
s.id StorehouseID,
case when abs(convert(int,convert(binary(4),newid()))%3) in (0,1) then 1 else -1 end Operation, -- какой то приход и расход, из случайных сделаем из 3х вариантов 2 приход 1 расход
1+abs(convert(int,convert(binary(4),newid()))%100) Quantity
into dbo.Turnover
from times t cross join storehouse s
option(maxrecursion 0);
go
--- 15 min
alter table dbo.Turnover alter column id int not null
go
alter table dbo.Turnover add constraint pk_turnover primary key (id) with(data_compression=page)
go
-- 6 min
У мене цей скрипт на ПК з SSD диском виконувався близько 22 хвилин, і розмір таблиці зайняв близько 8Гб на жорсткому диску. Ти можеш зменшити кількість років, і кількість складів, щоб час створення і заповнення таблиці скоротити. Але якийсь непоганий обсяг для оцінки планів запитів рекомендую залишити хоча б 1-2 гігабайти.
Згрупуємо дані до години
Далі, нам потрібно згрупувати суми за продуктами на складі за досліджуваний період часу, в нашій постановці завдання це одна година (можна до хвилини, до 15 хвилин, дня. Але очевидно, що до мілісекунд навряд чи кому знадобиться звітність). Для порівнянь у сесії (вікні), де виконуємо наші запити, виконаємо команду - set statistics time on;. Далі виконуємо самі запити та дивимося плани запитів:
Select top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) as dt - округляємо до години ProductID, StorehouseID, sum(Operation*Quantity) as Quantity from dbo .Turnover group by convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID
Вартість запиту - 12406
(Рядок оброблено: 1000)
Час роботи SQL Server:
Час ЦП = 2096594 мс, витрачений час = 321 797 мс.
Якщо ми зробимо результуючий запит з балансом, який вважається наростаючим підсумком від нашої кількості, запит і план запиту будуть наступними:
Select top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) as dt - округляємо до години ProductID, StorehouseID, sum(Operation*Quantity) as Quantity, sum (sum(Operation*Quantity)) over (partition by StorehouseID, ProductID order by convert(datetime,convert(varchar(13),dt,120)+":00",120)) as Balance from dbo. (datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID
Вартість запиту - 19329
(Рядок оброблено: 1000)
Час роботи SQL Server:
Час ЦП = 2413155 мс, витрачений час = 344631 мс.
Оптимізація угруповання
Тут досить просто. Сам запит без наростаючого результату можна оптимізувати матеріалізованим уявленням (index view). Для побудови матеріалізованого уявлення, що сумується не повинно мати значення NULL, у нас підсумовуються sum(Operation*Quantity), або кожне поле зробити NOT NULL або додати isnull/coalesce у вираз. Пропоную створити матеріалізовану виставу.
Create view dbo.TurnoverHour with schemabinding as select convert(datetime,convert(varchar(13),dt,120)+":00",120) as dt, -- округляємо до години ProductID, StorehouseID, sum(isnull(Operation*) Quantity,0)) as Quantity, count_big(*) qty from dbo.
І збудувати за ним кластерний індекс. В індексі порядок полів вкажемо так само як і в угрупованні (для угруповання стільки порядок не важливий, важливо що всі поля угруповання були в індексі) і наростаючому підсумку (тут важливий порядок - спочатку те, що в partition by, потім те, що в order by):
Create unique clustered index uix_TurnoverHour on dbo.TurnoverHour (StorehouseID, ProductID, dt) with (data_compression=page) - 19 min
Тепер після побудови кластерного індексу ми можемо знову виконати запити, змінивши агрегацію суми як у поданні:
Select top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) as dt - округляємо до години ProductID, StorehouseID, sum(isnull(Operation*Quantity,0) ) as Quantity from dbo.Turnover group by convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID select top(1000) convert(datetime,convert(varchar(13) ),dt,120)+":00",120) as dt, - округляємо до години ProductID, StorehouseID, sum(isnull(Operation*Quantity,0)) as Quantity, sum(sum(isnull(Operation*Quantity, 0))) over (partition by StorehouseID, ProductID order by convert(datetime,convert(varchar(13),dt,120)+":00",120)) as Balance from dbo. (varchar(13),dt,120)+":00",120), ProductID, StorehouseID
Плани запитів стали:
Вартість 0.008
Вартість 0.01
Час роботи SQL Server:
Час ЦП = 31 мс, витрачений час = 116 мс.
(Рядок оброблено: 1000)
Час роботи SQL Server:
Час ЦП = 0 мс, витрачений час = 151 мс.
Отже, бачимо, що з індексованою завірюхою запит сканує не таблицю групуючи дані, а кластерний індекс, у якому вже все згруповано. І час виконання скоротилося з 321797 мілісекунд до 116 мс., тобто. у 2774 рази.
На цьому можна було б і закінчити нашу оптимізацію, якби не той факт, що нам потрібна часто не вся таблиця (завірюха), а її частина за обраний діапазон.
Проміжні баланси
У результаті нам потрібне швидке виконання наступного запиту:
Set dateformat ymd; declare @start datetime = "2015-01-02", @finish datetime = "2015-01-03" select * from (select dt, StorehouseID, ProductId, Quantity, sum(Quantity) over (partition by StorehouseID, ProductID) dt) as Balance from dbo.TurnoverHour with(noexpand) where dt<= @finish) as tmp
where dt >= @start
Вартість плану = 3103. А уяви що було б, якби не по матеріалізованому уявленню пішов а по самій таблиці.
Виведення даних матеріалізованого подання та балансу по кожному продукту на складі на дату з часом округлену до години. Що б порахувати баланс - необхідно з самого початку (з нульового балансу) підсумувати всі кількості до вказаної останньої дати (@finish), а потім вже в підсумованому резалтсеті відсікти дані пізніше параметра start .
Тут, мабуть, допоможуть проміжні розраховані баланси. Наприклад, на 1 число кожного місяця або на кожну неділю. Маючи такі баланси, завдання зводиться до того що, треба буде підсумовувати раніше розраховані баланси і розрахувати баланс немає від початку, як від останньої розрахованої дати. Для експериментів та порівнянь збудуємо додатковий не кластерний індекс за датою:
Create index ix_dt на dbo. --7 min І наш запит буде вигляду: set dateformat ymd; declare @start datetime = "2015-01-02", @finish datetime = "2015-01-03" declare @start_month datetime = convert(datetime,convert(varchar(9),@start,120)+"1", 120) select * from (select dt, StorehouseID, ProductId, Quantity, sum(Quantity) over (partition by StorehouseID, ProductID order by dt) as Balance from dbo. tmp where dt >
Взагалі цей запит маючи навіть індекс за датою, що повністю покриває всі поля, що зачіпаються в запиті, вибере кластерний наш індекс і сканування. А не пошук за датою з наступним сортуванням. Пропоную виконати наступні 2 запити та порівняти що у нас вийшло, далі проаналізуємо що ж краще:
Set dateformat ymd; declare @start datetime = "2015-01-02", @finish datetime = "2015-01-03" declare @start_month datetime = convert(datetime,convert(varchar(9),@start,120)+"1", 120) select * from (select dt, StorehouseID, ProductId, Quantity, sum(Quantity) over (partition by StorehouseID, ProductID order by dt) as Balance from dbo. tmp where dt >= @start order by StorehouseID, ProductID, dt select * from (select dt, StorehouseID, ProductId, Quantity, sum(Quantity) over (partition by StorehouseID, ProductID order by dt) as Balance from dbo.TurnoverHour with( noexpand,index=ix_dt) where dt between @start_month and @finish) як tmp where dt >= @start order by StorehouseID, ProductID, dt
Час роботи SQL Server:
Час ЦП = 33 860 мс, витрачений час = 24 247 мс.(Рядок оброблено: 145608)
(Рядок оброблено: 1)
Час роботи SQL Server:
Час ЦП = 6374 мс, витрачений час = 1718 мс.
час ЦП = 0 мс, час = 0 мс.
З часу видно, що індекс за датою виконується значно швидше. Але плани запитів у порівнянні виглядають так:
Вартість 1го запиту з автоматично вибраним кластерним індексом = 2752, а ось вартість з індексом за датою запиту = 3119.
Як би там не було, тут нам потрібно від індексу два завдання: сортування та вибірка діапазону. Одним індексом із наявних нам це завдання не вирішити. В даному прикладі діапазон даних всього за 1 день, але якщо буде період більше, але далеко не весь, наприклад, за 2 місяці, то пошук по індексу однозначно буде не ефективний через витрати на сортування.
Тут із видимих оптимальних рішень я бачу:
- Створити обчислюване поле Рік-Місяць та індекс створити (Рік-Місяць, інші поля кластерного індексу). За умови where dt between @start_month and finish замінити на Рік-Місяць=@місяць, і після цього вже накласти фільтр на потрібні дати.
- Фільтровані індекси – індекс сам як кластерний, але фільтр за датою, за потрібний місяць. І таких індексів зробити стільки, скільки у нас місяців загалом. Ідея близька до рішення, але тут якщо діапазон умов буде з 2х фільтрованих індексів, буде потрібно з'єднання і надалі все одно сортування неминуче.
- Секціонуємо кластерний індекс так, щоб у кожній секції були дані лише за один місяць.
Секціонуємо кластерний індекс завірюхи. Насамперед видалимо з в'юхи всі індекси:
Drop index ix_dt на dbo.TurnoverHour; drop index uix_TurnoverHour on dbo.TurnoverHour;
І створимо функцію та схему секціонування:
Set dateformat ymd; create partition function pf_TurnoverHour(datetime) як range right for values ("2006-01-01", "2006-02-01", "2006-03-01", "2006-04-01", "2006-05- 01", "2006-06-01", "2006-07-01", "2006-08-01", "2006-09-01", "2006-10-01", "2006-11-01" , "2006-12-01", "2007-01-01", "2007-02-01", "2007-03-01", "2007-04-01", "2007-05-01", " 2007-06-01", "2007-07-01", "2007-08-01", "2007-09-01", "2007-10-01", "2007-11-01", "2007- 12-01", "2008-01-01", "2008-02-01", "2008-03-01", "2008-04-01", "2008-05-01", "2008-06- 01", "2008-07-01", "2008-08-01", "2008-09-01", "2008-10-01", "2008-11-01", "2008-12-01" , "2009-01-01", "2009-02-01", "2009-03-01", "2009-04-01", "2009-05-01", "2009-06-01", " 2009-07-01", "2009-08-01", "2009-09-01", "2009-10-01", "2009-11-01", "2009-12-01", "2010- 01-01", "2010-02-01", "2010-03-01", "2010-04-01", "2010-05-01", "2010-06-01", "2010-07- 01", "2010-08-01", "2010-09-01", "2010-10-01", "2010-11-01", "2010-12-01", "2011-01-01" , "2011-02-01", "2011-03-01", "2011-04-01", "2011-05-01", "2011-06-01", "2011-07-01", " 2011-08-01", "2011-09-01", "2011-10-01", "2011-11-01", "2011-12-01", "2012-01-01", "2012- 02-01", "2012-03-01", "2012-04-01", "2012-05-01", "2012-06-01", "2012-07-01", "2012-08- 01", "2012-09-01", "2012-10-01", "2012-11-01", "2012-12-01", "2013-01-01", "2013-02-01" , "2013-03-01", "2013-04-01", "2013-05-01", "2013-06-01", "2013-07-01", "2013-08-01", " 2013-09-01", "2013-10-01", "2013-11-01", "2013-12-01", "2014-01-01", "2014-02-01", "2014- 03-01", "2014-04-01", "2014-05-01", "2014-06-01", "2014-07-01", "2014-08-01", "2014-09- 01", "2014-10-01", "2014-11-01", "2014-12-01", "2015-01-01", "2015-02-01", "2015-03-01" , "2015-04-01", "2015-05-01", "2015-06-01", "2015-07-01", "2015-08-01", "2015-09-01", " 2015-10-01", "2015-11-01", "2015-12-01", "2016-01-01", "2016-02-01", "2016-03-01", "2016- 04-01", "2016-05-01", "2016-06-01", "2016-07-01", "2016-08-01", "2016-09-01", "2016-10- 01", "2016-11-01", "2016-12-01", "2017-01-01", "2017-02-01", "2017-03-01", "2017-04-01" , "2017-05-01", "2017-06-01", "2017-07-01", "2017-08-01", "2017-09-01", "2017-10-01", " 2017-11-01", "2017-12-01", "2018-01-01", "2018-02-01", "2018-03-01", "2018-04-01", "2018- 05-01", "2018-06-01", "2018-07-01", "2018-08-01", "2018-09-01", "2018-10-01", "2018-11- 01", "2018-12-01", "2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01", "2019-05-01" , "2019-06-01", "2019-07-01", "2019-08-01", "2019-09-01", "2019-10-01", "2019-11-01", " 2019-12-01"); go create partition scheme ps_TurnoverHour як partition pf_TurnoverHour all to (); go Ну і вже відомий нам кластерний індекс тільки у створеній схемі секціонування: create unique clustered index uix_TurnoverHour on dbo. TurnoverHour (StorehouseID, ProductID, dt) with (data_compression=page) on ps_TurnoverHour(dt); --- 19 min І тепер подивимося, що в нас вийшло. Сам запит: set dateformat ymd; declare @start datetime = "2015-01-02", @finish datetime = "2015-01-03" declare @start_month datetime = convert(datetime,convert(varchar(9),@start,120)+"1", 120) select * from (select dt, StorehouseID, ProductId, Quantity, sum(Quantity) over (partition by StorehouseID, ProductID order by dt) as Balance from dbo. tmp where dt >= @start order by StorehouseID, ProductID, dt option(recompile);
Час роботи SQL Server:
Час ЦП = 7860 мс, витрачений час = 1725 мс.
Час синтаксичного аналізу та компіляції SQL Server:
час ЦП = 0 мс, час = 0 мс.
Вартість плану запиту = 9.4
По суті, дані в одній секції вибираються і скануються за кластерним індексом досить швидко. Тут слід додати те, що коли параметризований запит, виникає неприємний ефект parameter sniffing, лікується option (recompile).
Синтаксис SQL 2003 підтримує всі платформи.
FLOOR (вираз)
Якщо ви передаєте у функцію позитивне число, то дія функції полягатиме у видаленні всього, що стоїть після десяткової точки.
SELECT FLOOR(100.1) FROM dual;
Однак, запам'ятайте, що у разі негативних чисел округлення в меншу сторону відповідає збільшенню абсолютного значення.
SELECT FLOOR(-100.1) FROM dual;
Щоб отримати ефект, протилежний дії функції FLOOR, скористайтеся функцією CEIL.
LN
Функція LN повертає натуральний логарифм числа, тобто ступінь, в яку потрібно звести математичну константу (приблизно 2.718281), щоб в результаті отримати задане число.
Синтаксис SQL 2003
LN (вираз)
DB2, Oracle, PostgreSQL
Платформи DB2, Oracle та PostgreSQL підтримують для функції LN синтаксис SQL 2003. DB2 та PostgreSQL також підтримують функцію LOG як синонім LN.
MySQL та SQL Server
У MySQL та SQL Server є своя власна функція для обчислення натурального логарифму – LOG.
LOG (вираз)
У прикладі для Oracle обчислюється натуральний логарифм числа, приблизно рівного математичної константі.
SELECT LN(2.718281) FROM dual;
Щоб виконати протилежну операцію, використовуйте функцію ЄХР.
MOD
Функція MOD повертає залишок від ділення поділеного на дільник. Усі платформи підтримують синтаксис інструкції MOD стандарту SQL 2003.
Синтаксис SQL 2003
MOD (ділене, дільник)
Стандартна функція MOD призначена для отримання залишку від поділу на дільник. Якщо дільник дорівнює нулю, то повертається подільне.
Нижче показано, як можна використовувати функцію MOD в інструкції SELECT.
SELECT MOD(12, 5) FROM NUMBERS 2;
POSITION
Функція POSITION повертає ціле число, яке показує початкове положення рядка у рядку пошуку.
Синтаксис SQL 2003
POSITION(рядок 1 IN рядок2)
Стандартна функція POSITION призначена для отримання положення першого входження заданого рядка (рядок!) у рядку пошуку (рядок!). Функція повертає 0 якщо рядок! не зустрічається в рядку!, і NULL – якщо будь-який з аргументів дорівнює NULL.
DB2
DB2 має еквівалентну функцію POSSTR.
MySQL
Платформа MySQL підтримує функцію POSITION відповідно до стандарту SQL 2003.
POWER
Функція POWER використовується для зведення числа у вказаний ступінь.
Синтаксис SQL 2003
POWER (основа, показник)
Результатом виконання цієї функції є підстава, зведена в ступінь, що визначається показником. Якщо підстава негативна, то показник має бути цілим числом.
DB2, Oracle, PostgreSQL та SQL Server
Усі ці виробники підтримують синтаксис SQL 2003.
Oracle
У Oracle є еквівалентна функція INSTR.
PostgreSQL
Платформа PostgreSQL підтримує функцію POSITION відповідно до стандарту SQL 2003.
MySQL
Платформа MySQL підтримує цю функціональність, не цього ключове слово POW.
P0W (основа, показник)
Зведення позитивного числа до ступеня досить очевидне.
SELECT POWER(10.3) FROM dual;
Будь-яке число, зведене ступінь 0, дорівнює 1.
SELECT POWER(0.0) FROM dual;
Негативний показник зміщує десяткову точку вліво.
SELECT POWER(10, -3) FROM dual;
SORT
Функція SQRT повертає квадратний корінь числа.
Синтаксис SQL 2003
Усі платформи підтримують синтаксис SQL 2003.
SORT (вираз)
SELECT SQRT(100) FROM dual;
WIDTH BUCKET
Функція WIDTH BUCKET надає значення стовпцям рівноширинної гістограми.
Синтаксис SQL 2003
У наступному синтаксисі вираз є значення, яке присвоюється стовпцю гістограми. Як правило, вираз ґрунтується на одному або кількох стовпцях таблиці, які повертаються запитом.
WIDTH BUCKET (вираз, min, max, стовпці_гістограми)
Параметр стовпчика гістограми показує кількість створюваних стовпців гістограми в діапазоні значень від min до max. Значення параметра min вмикається в діапазон, а значення параметра max не вмикається. Значення виразу надається одному зі стовпців гістограми, після чого функція повертає номер відповідного стовпця гістограми. Якщо вираз не підпадає під вказаний діапазон стовпців, функція повертає або 0 або max + 1, залежно від того, буде вираз меншим, ніж min, або великим або рівним max.
У наступному прикладі цілочисельні значення від 1 до 10 розподіляються між двома стовпцями гістограми.
Наступний приклад цікавіший. 11 значень від 1 до 10 розподіляються між трьома стовпцями гістограми для ілюстрації різницю між значенням min, яке включається в діапазон, і значенням max, яке діапазон не включається.
SELECT x, WIDTH_BUCKET(x, 1.10.3) FROM pivot;
Особливу увагу зверніть результати сХ =, Х= 9.9 і Х- 10. Вхідне значення параметра ними, тобто у цьому прикладі - 1, потрапляє у перший стовпець, позначаючи нижню межу діапазону, оскільки стовпець № 1 визначається як х >= min. Однак вхідне значення параметра max не входить до стовпчика з максимальними значеннями. У даному прикладі число 10 потрапляє в стовпець переповнення з номером max + 1. Значення 9.9 потрапляє в стовпець № 3, і це ілюструє правило, згідно з яким верхня межа діапазону визначається як х< max.
Усі математичні функції у разі помилки повертають NULL.
Унарний мінус. Змінює знак аргументу: mysql> SELECT – 2; -> -2 Необхідно враховувати, що якщо цей оператор використовується з даними типу BIGINT , значення, що повертається, також буде мати тип BIGINT ! Це означає, що слід уникати використання оператора цілих чисел, які можуть мати величину -2^63 ! ABS(X) Повертає абсолютне значення величини X: mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32 Цю функцію можна впевнено застосовувати для величин типу BIGINT. SIGN(X) Повертає знак аргументу як -1 , 0 чи 1 , залежно від цього, чи є X негативним, нулем чи позитивним: mysql> SELECT SIGN(-32); -> -1 mysql> SELECT SIGN(0); -> 0 mysql> SELECT SIGN(234); -> 1 MOD(N,M) % Значення по модулю (подібно до оператора % в C). Повертає залишок від розподілу N на M: mysql> SELECT MOD (234, 10); -> 4 mysql> SELECT 253% 7; -> 1 mysql> SELECT MOD(29,9); -> 2 Цю функцію можна впевнено застосовувати для величин типу BIGINT. FLOOR(X) Повертає найбільше ціле число, що не перевищує X: mysql> SELECT FLOOR(1.23); -> 1 mysql> SELECT FLOOR(-1.23); -> -2 Слід враховувати, що величина, що повертається, перетворюється в BIGINT ! CEILING(X) Повертає найменше ціле число, не менше ніж X: mysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEILING(-1.23); -> -1 Слід враховувати, що величина, що повертається, перетворюється в BIGINT ! ROUND(X) Повертає аргумент X, округлений до найближчого цілого числа: mysql> SELECT ROUND(-1.23); -> -1 mysql> SELECT ROUND(-1.58); -> -2 mysql> SELECT ROUND(1.58); -> 2 Слід враховувати, що поведінка функції ROUND() при значенні аргументу, що дорівнює середині між двома цілими числами, залежить від конкретної реалізації бібліотеки C. Округлення може виконуватися: до найближчого парного числа, завжди до найближчого більшого, завжди до найближчого меншого, завжди бути спрямованим на нуль. Щоб округлення завжди відбувалося лише в одному напрямку, необхідно використовувати замість цієї добре певні функції, такі як TRUNCATE() або FLOOR() . ROUND(X,D) Повертає аргумент X, округлений до числа з D десятковими знаками. Якщо D дорівнює 0 результат буде представлений без десяткового знака або дробової частини: mysql> SELECT ROUND(1.298, 1); -> 1.3 mysql> SELECT ROUND(1.298, 0); -> 1 EXP(X) Повертає значення e (основа натуральних логарифмів), зведене до ступеня X: mysql> SELECT EXP(2); -> 7.389056 mysql> SELECT EXP(-2); -> 0.135335 LOG(X) Повертає натуральний логарифм числа X: mysql> SELECT LOG(2); -> 0. 693147 mysql> SELECT LOG(-2); -> NULL Щоб отримати логарифм числа X для довільної основи логарифмів B слід використовувати формулу LOG(X)/LOG(B) . LOG10(X) Повертає десятковий логарифм числа X: mysql> SELECT LOG10(2); -> 0.301030 mysql> SELECT LOG10 (100); -> 2.000000 mysql> SELECT LOG10(-100); -> NULL POW(X,Y) POWER(X,Y) Повертає значення аргументу X, зведене до ступеня Y: mysql> SELECT POW(2,2); -> 4.000000 mysql> SELECT POW(2,-2); -> 0.250000 SQRT(X) Повертає невід'ємний квадратний корінь числа X: mysql> SELECT SQRT(4); -> 2.000000 mysql> SELECT SQRT(20); -> 4.472136 PI() Повертає значення числа "пі". За умовчанням представлено 5 десяткових знаків, але MySQL для представлення числа "пі" при внутрішніх обчисленнях використовується повна подвійна точність. mysql> SELECT PI(); -> 3.141593 mysql> SELECT PI()+0.000000000000000000; -> 3.141592653589793116 COS(X) Повертає косинус числа X , де X задається в радіанах: mysql> SELECT COS(PI()); -> -1.000000 SIN(X) Повертає синус числа X, де X задається в радіанах: mysql> SELECT SIN(PI()); -> 0.000000 TAN(X) Повертає тангенс числа X, де X задається в радіанах: mysql> SELECT TAN(PI()+1); -> 1.557408 ACOS(X) Повертає арккосинус числа X, тобто. величину, косинус якої дорівнює X . Якщо X не знаходиться в діапазоні від -1 до 1 повертає NULL: mysql> SELECT ACOS(1); -> 0.000000 mysql> SELECT ACOS (1.0001); -> NULL mysql> SELECT ACOS(0); -> 1.570796 ASIN(X) Повертає арксинус числа X, тобто. величину, синус якої дорівнює X . Якщо X не знаходиться в діапазоні від -1 до 1 повертає NULL: mysql> SELECT ASIN(0.2); -> 0.201358 mysql> SELECT ASIN("foo"); -> 0.000000 ATAN(X) Повертає арктангенс числа X, тобто. величину, тангенс якої дорівнює X: mysql> SELECT ATAN (2); -> 1.107149 mysql> SELECT ATAN(-2); -> -1.107149 ATAN(Y,X) ATAN2(Y,X) Повертає арктангенс двох змінних X та Y . Обчислення проводиться як і, як і обчислення арктангенса Y / X , крім того, що знаки обох аргументів використовуються визначення квадранта результату: mysql> SELECT ATAN(-2,2); -> -0.785398 mysql> SELECT ATAN2(PI(),0); -> 1.570796 COT(X) Повертає котангенс числа X: mysql> SELECT COT(12); -> -1.57267341 mysql> SELECT COT(0); -> NULL RAND() RAND(N) Повертає випадкову величину з плаваючою точкою в діапазоні від 0 до 1,0. Якщо цілий аргумент N зазначений, він використовується як початкове значення цієї величини: mysql> SELECT RAND(); -> 0. 9233482386203 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(); -> 0.63553050033332 mysql> SELECT RAND(); -> 0.70100469486881 У виразах виду ORDER BY не слід використовувати стовпець з величинами RAND() , оскільки застосування оператора ORDER BY призведе до багаторазових обчислень у цьому стовпці. У версії MySQL 3.23 можна, однак, виконати наступний оператор: SELECT * FROM table_name ORDER BY RAND() : він корисний для отримання випадкового екземпляра з множини SELECT * FROM table1,table2 WHERE a=b AND c
Це ще одне завдання, що часто зустрічається. Основний принцип полягає у накопиченні значень одного атрибуту (агрегованого елемента) на основі впорядкування по іншому атрибуту або атрибутам (елемент упорядкування), можливо, за наявності секцій рядків, визначених на основі ще одного атрибуту або атрибутів (елемент секціонування). У житті є багато прикладів обчислення наростаючих підсумків, наприклад обчислення залишків на банківських рахунках, відстеження наявності товарів складі чи поточних цифр продажу тощо.
До SQL Server 2012 рішення, засновані на наборах та використовувані для обчислення наростаючих підсумків, були виключно ресурсомісткими. Тому люди зазвичай зверталися до ітеративних рішень, які працювали нешвидко, але в деяких ситуаціях таки швидше, ніж рішення на основі наборів. Завдяки розширенню підтримки віконних функцій у SQL Server 2012, наростаючі підсумки можна обчислювати, використовуючи простий базований на наборах код, продуктивність якого набагато вища, ніж у старих рішеннях на основі T-SQL - як на основі наборів, так і ітеративних. Я міг би показати нове рішення і перейти до наступного розділу; але щоб ви по-справжньому зрозуміли масштаб змін, я опишу старі способи і порівняю їхню продуктивність з новим підходом. Звичайно, ви маєте право прочитати тільки першу частину, яка описує новий підхід, і пропустити решту статті.
Для демонстрації різних рішень я скористаюся залишками на рахунках. Ось код, який створює та наповнює таблицю Transactions невеликим обсягом тестових даних:
SET NOCOUNT ON; USE TSQL2012; IF OBJECT_ID("dbo.Transactions", "U") IS NOT NULL DROP TABLE dbo.Transactions; CREATE TABLE dbo.Transactions (actid INT NOT NULL, - стовпець секціонування tranid INT NOT NULL, - стовпець упорядкування val MONEY NOT NULL, - міра CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid)); GO - невеликий набір тестових даних INSERT INTO dbo.Transactions(actid, tranid, val) 2.00), (1, 5, 1.00), (1, 6, 3.00), (1, 7, -4.00), (1, 8, -1.00), (1, 9, -2.00), (1, 10 , -3.00), (2, 1, 2.00), (2, 2, 1.00), (2, 3, 5.00), (2, 4, 1.00), (2, 5, -5.00), (2, 6 , 4.00), (2, 7, 2.00), (2, 8, -4.00), (2, 9, -5.00), (2, 10, 4.00), (3, 1, -3.00), (3, 2, 3.00), (3, 3, -2.00), (3, 4, 1.00), (3, 5, 4.00), (3, 6, -1.00), (3, 7, 5.00), (3, 8, 3.00), (3, 9, 5.00), (3, 10, -3.00);
Кожен рядок таблиці подає банківську операцію на рахунку. Депозити відзначаються як транзакції з позитивним значенням стовпці val, а зняття коштів - як негативне значення транзакції. Наше завдання - обчислити залишок на рахунку в кожний момент часу шляхом акумулювання сум операцій у рядку val при упорядкуванні по стовпцю tranid, причому це потрібно зробити для кожного рахунку окремо. Бажаний результат має виглядати так:
Для тестування обох рішень потрібний більший обсяг даних. Це можна зробити за допомогою такого запиту:
DECLARE @num_partitions AS INT = 10; @rows_per_partition AS INT = 10000; TRUNCATE TABLE dbo.Transactions; INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM( NEWID())%5)) FROM dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;
Можете задати свої вхідні дані, щоб змінити кількість секцій (рахунків) та рядків (транзакцій) у секції.
Засноване на наборах рішення з використанням віконних функцій
Я почну розповідь із рішення на основі наборів, в якому використовується віконна функція агрегування SUM. Визначення вікна тут досить наочно: потрібно секціонувати вікно по actid, упорядкувати по tranid і фільтром відібрати рядки в кадрі з нижньою (UNBOUNDED PRECEDING) до поточної. Ось відповідний запит:
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance FROM dbo.Transactions;
Цей код не тільки простий та прямолінійний – він і виконується швидко. План цього запиту показано на малюнку:
У таблиці є кластеризований індекс, який відповідає вимогам POC та придатний для використання віконними функціями. Зокрема, список ключів індексу заснований на елементі секціонування (actid), за яким слідує елемент упорядкування (tranid), також для забезпечення покриття індекс включає всі інші стовпці у запиті (val). План містить упорядкований перегляд, за яким слідує обчислення номера рядка для внутрішніх потреб, а потім - віконного агрегату. Оскільки є POC-індекс, оптимізатору не потрібно додавати в план оператор сортування. Це дуже ефективний план. До того ж, він лінійно масштабується. Пізніше, коли я покажу результати порівняння продуктивності, ви побачите, наскільки ефективніший цей спосіб порівняно зі старими рішеннями.
До SQL Server 2012 використовували або вкладені запити, або з'єднання. При використанні вкладеного запиту наростаючі підсумки обчислюються шляхом фільтрації всіх рядків з тим же значенням actid, що і в зовнішньому рядку, і tranid, яке менше або дорівнює значення в зовнішньому рядку. Потім до фільтрованих рядків застосовується агрегування. Ось відповідний запит:
Аналогічний підхід можна реалізувати із застосуванням сполук. Використовується той самий предикат, що й у пропозиції WHERE вкладеного запиту у ON пропозиції з'єднання. У цьому випадку для N-ої транзакції одного і того ж рахунку A в екземплярі, позначеному як T1, ви знаходите N відповідностей в екземплярі T2, при цьому номери транзакцій пробігають від 1 до N. У результаті зіставлення рядка в T1 повторюються, тому потрібно згрупувати рядки по всіх елементах з T1, щоб отримати інформацію про поточну транзакцію та застосувати агрегування до атрибуту val з T2 для обчислення наростаючого результату. Готовий запит виглядає приблизно так:
SELECT T1.actid, T1.tranid, T1.val, SUM(T2.val) AS balance FROM dbo.Transactions AS T1 JOIN dbo.Transactions AS T2 ON T2.actid = T1.actid AND T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;
На малюнку нижче наведено плани обох рішень:
Зауважте, що в обох випадках в екземплярі T1 виконується повний перегляд кластеризованого індексу. Потім для кожного рядка в плані передбачена операція пошуку в індексі початку розділу поточного рахунку на кінцевій сторінці індексу, при цьому зчитуються всі транзакції, в яких менше T2.tranid або дорівнює T1.tranid. Точка, де відбувається агрегування рядків, дещо відрізняється у планах, але кількість лічених рядків однакова.
Щоб зрозуміти, скільки рядків проглядається, треба врахувати кількість елементів даних. Нехай p – число секцій (рахунків), а r – число рядків у секції (транзакції). Тоді число рядків у таблиці приблизно дорівнює p * r, якщо вважати, що транзакції розподілені по рахунках рівномірно. Таким чином, наведений у верхній частині перегляд охоплює рядок p*r. Але найбільше нас цікавить те, що відбувається в ітераторі Nested Loops.
У кожній секції план передбачає читання 1 + 2 + ... + r рядків, що у сумі становить (r + r*2) / 2. Загальна кількість оброблюваних у планах рядків складає p * r + p * (r + r2) / 2. Це означає, що кількість операцій у плані зростає у квадраті зі збільшенням розміру секції, тобто якщо збільшити розмір секції у f разів, обсяг роботи збільшиться приблизно у f 2 разів. Це погано. Наприклад 100 рядків відповідає 10 тис. рядків, а тисячі рядків відповідає мільйон і т.д. Простіше кажучи, це призводить до сильного уповільнення виконання запитів при немаленькому розмірі секції, тому що квадратична функція зростає дуже швидко. Подібні рішення працюють задовільно за кількох десятків рядків на секцію, але не більше.
Рішення з використанням курсору
Рішення з урахуванням курсору реалізуються «в лоб». Оголошується курсор на основі запиту, що впорядковує дані actid і tranid. Після цього виконується ітеративний прохід записів курсору. При виявленні нового рахунку скидається змінна агрегат, що містить. У кожній ітерації до змінної додається сума нової транзакції, після чого рядок зберігається в табличній змінній з інформацією про поточну транзакцію плюс поточне значення наростаючого підсумку. Після ітеративного проходу повертається результат із табличної змінної. Ось код закінченого рішення:
DECLARE @Result AS TABLE (actid INT, tranid INT, val MONEY, balance MONEY); DECLARE @actid AS INT, @prvactid AS INT, @tranid AS INT, @val AS MONEY, @balance AS MONEY; DECLARE C CURSOR FAST_FORWARD FOR SELECT actid, tranid, val FROM dbo. Transactions ORDER BY actid, tranid; OPEN C FETCH NEXT FROM C INTO @actid, @tranid, @val; SELECT @prvactid = @actid, @balance = 0; WHILE @@fetch_status = 0 BEGIN IF @actid<>@prvactid SELECT @prvactid = @actid, @balance = 0; SET @balance = @balance + @val; INSERT INTO @Result VALUES(@actid, @tranid, @val, @balance); FETCH NEXT FROM C INTO @actid, @tranid, @val; END CLOSE C; DEALLOCATE C; SELECT * FROM @Result;
План запиту з використанням курсору показано на малюнку:
Цей план масштабується лінійно, тому що дані з індексу проглядаються лише раз у певному порядку. Також у кожної операції отримання рядка з курсору приблизно однакова вартість для кожного рядка. Якщо прийняти навантаження, створюване при обробці одного рядка курсора, що дорівнює g, вартість цього рішення можна оцінити як p * r + p * r * g (як ви пам'ятаєте, p - це число секцій, а r - число рядків в секції). Отже, якщо збільшити число рядків на секцію в раз, навантаження на систему складе p*r*f + p*r*f*g, тобто буде рости лінійно. Вартість обробки в розрахунку на рядок висока, але через лінійний характер масштабування, з певного розміру секції це рішення демонструватиме кращу масштабованість, ніж рішення на основі вкладених запитів та з'єднань через квадратичне масштабування цих рішень. Проведений мною вимір продуктивності показав, що число, коли рішення з курсором працює швидше, дорівнює декільком сотням рядків на секцію.
Незважаючи на виграш у продуктивності, що забезпечується рішеннями на основі курсору, у загальному випадку їх треба уникати, тому що вони не є реляційними.
Рішення на основі CLR
Одне можливе рішення на основі CLR (Common Language Runtime)по суті є однією із форм рішення з використанням курсору. Різниця в тому, що замість використання курсору T-SQL, який витрачає багато ресурсів на отримання чергового рядка та виконання ітерації, застосовуються ітерації .NET SQLDataReader та .NET, які працюють набагато швидше. Одна з особливостей CLR, яка робить цей варіант швидше, полягає в тому, що результуючий рядок у тимчасовій таблиці не потрібний - результати пересилаються безпосередньо зухвалому процесу. Логіка рішення на основі CLR схожа на логіку рішення з використанням курсору та T-SQL. Ось код C#, що визначає процедуру рішення, що зберігається:
Using System; використовуючи System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; Public partial class StoredProcedures ( public static void AccountBalances() ( using (SqlConnection conn = new SqlConnection("context connection=true;")) ( SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = @" " + "SELECT actid, tranid, val " + "FROM dbo.Transactions " + "ORDER BY actid, tranid;"; SqlMetaData columns = new SqlMetaData; SqlMetaData("tranid" , SqlDbType.Int); columns = new Pipe.SendResultsStart(record); conn.Open(); SqlDataReader reader = comm.ExecuteReader(); ; SqlMoney val = reader.GetSqlMoney(2); if (actid == prvactid) record.SetSqlInt32(1, reader.GetSqlInt32(1)); record.SetSqlMoney(2, val); record.SetSqlMoney(3, balance); SqlContext.Pipe.SendResultsRow(record); ) SqlContext.Pipe.SendResultsEnd(); ) ) )
Щоб мати можливість виконати цю процедуру, що зберігається в SQL Server, спочатку треба на основі цього коду побудувати збірку на ім'я AccountBalances і розгорнути в базі даних TSQL2012. Якщо ви не знайомі з розгортанням збірок у SQL Server, можете прочитати розділ «Зберігання та середовище CLR» у статті «Зберігають процедури» .
Якщо ви назвали складання AccountBalances, а шлях до файлу складання - "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll", завантажити складання в базу даних і зареєструвати збережену процедуру можна наступним кодом:
CREATE ASSEMBLY AccountBalances FROM "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll"; GO CREATE PROCEDURE dbo.AccountBalances AS EXTERNAL NAME AccountBalances.StoredProcedures.AccountBalances;
Після розгортання складання та реєстрації процедури можна її виконати таким кодом:
EXEC dbo.AccountBalances;
Як я вже казав, SQLDataReader є лише однією формою курсору, але в цій версії витрати на читання рядків значно менше, ніж при використанні традиційного курсору в T-SQL. Також в .NET ітерації виконуються набагато швидше, ніж у T-SQL. Таким чином, рішення на основі CLR також масштабуються лінійно. Тестування показало, що продуктивність цього рішення стає вищою за продуктивність рішень з використанням підзапитів і з'єднань, коли число рядків у секції перевалює через 15.
Після завершення слід виконати наступний код очищення:
DROP PROCEDURE dbo.AccountBalances; DROP ASSEMBLY AccountBalances;
Вкладені ітерації
До цього моменту я показував ітеративні рішення та рішення на основі наборів. Наступне рішення ґрунтується на вкладених ітераціях, які є гібридом ітеративного та заснованого на наборах підходів. Ідея полягає в тому, щоб попередньо скопіювати рядки з таблиці-джерела (у нашому випадку це банківські рахунки) у тимчасову таблицю разом із новим атрибутом на ім'я rownum, який обчислюється з використанням функції ROW_NUMBER. Номери рядків секціонуються по actid і впорядковуються по tranid, тому перша транзакція в кожному банківському рахунку призначається номер 1, друга транзакція - 2 і т.д. Потім у часовій таблиці створюється кластеризований індекс зі списком ключів (rownum, actid). Потім використовується рекурсивний вираз CTE або спеціально створений цикл для обробки по одному рядку за ітерацію у всіх рахунках. Потім наростаючий підсумок обчислюється шляхом підсумовування значення, що відповідає поточному рядку, зі значенням, пов'язаним із попереднім рядком. Ось реалізація цієї логіки з використанням рекурсивного CTE:
SELECT actid, tranid, val, ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum INTO # Transactions FROM dbo.Transactions; CREATE UNIQUE CLUSTERED INDEX idx_rownum_actid ON # Transactions (rownum, actid); WITH C AS (SELECT 1 AS rownum, actid, tranid, val, val AS sumqty FROM #Transactions WHERE rownum = 1 UNION ALL SELECT PRV.rownum + 1, PRV.actid, CUR.tranid, CUR.val, PRV.sumq CUR.val FROM C AS PRV JOIN # Transactions AS CUR ON CUR.rownum = PRV.rownum + 1 AND CUR.actid = PRV.actid) SELECT actid, tranid, val, sumqty FROM C OPTION (MAXRECURSION 0); DROP TABLE # Transactions;
А це реалізація з використанням явного циклу:
SELECT ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum, actid, tranid, val, CAST(val AS BIGINT) AS sumqty INTO #Transactions FROM dbo.Transactions; CREATE UNIQUE CLUSTERED INDEX idx_rownum_actid ON # Transactions (rownum, actid); DECLARE @rownum AS INT; SET @rownum = 1; WHILE 1 = 1 BEGIN SET @rownum = @rownum + 1; UPDATE CUR SET sumqty = PRV.sumqty + CUR.val FROM # Transactions AS CUR JOIN # Transactions AS PRV ON CUR.rownum = @rownum AND PRV.rownum = @rownum - 1 AND CUR.actid = PRV.actid; IF @rowcount = 0 BREAK; END SELECT actid, tranid, val, sumqty FROM # Transactions; DROP TABLE # Transactions;
Це рішення забезпечує хорошу продуктивність, коли є велика кількість секцій з невеликою кількістю рядків у секціях. Тоді число ітерацій невелике, а основна робота виконується заснованою на наборах частиною рішення, яка з'єднує рядки, пов'язані з одним номером рядка, рядками, пов'язаними з попереднім номером рядка.
Багаторядкове оновлення зі змінними
Показані на той час прийоми обчислення наростаючих результатів гарантовано дають правильний результат. Описувана в цьому розділі методика неоднозначна, тому що заснована на спостережуваній, а не задокументованій поведінці системи, крім того, вона суперечить принципам релятивності. Висока її привабливість обумовлена великою швидкістю роботи.
У цьому способі використовується інструкція UPDATE із змінними. Інструкція UPDATE може надавати змінним вирази на основі значення стовпця, а також надавати значенням у стовпцях вираз зі змінною. Рішення починається зі створення тимчасової таблиці на ім'я Transactions з атрибутами actid, tranid, val і balance та кластеризованого індексу зі списком ключів (actid, tranid). Потім тимчасова таблиця наповнюється всіма рядками з вихідної БД Transactions, причому стовпець balance всіх рядків вводиться значення 0,00. Потім викликається інструкція UPDATE зі змінними, пов'язаними з тимчасовою таблицею, для обчислення наростаючих підсумків та вставки обчисленого значення стовпець balance.
Використовуються змінні @prevaccount та @prevbalance, а значення в стовпці balance обчислюється із застосуванням наступного виразу:
SET @prevbalance = balance = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END
Вираз CASE перевіряє, чи не збігаються ідентифікатори поточного та попереднього рахунків, і якщо вони рівні, повертає суму попереднього та поточного значень у стовпці balance. Якщо ідентифікатори рахунків є різними, повертається сума поточної транзакції. Далі результат виразу CASE вставляється в стовпець balance і надається змінною @prevbalance. В окремому виразі змінної ©prevaccount надається ідентифікатор поточного рахунку.
Після виразу UPDATE рішення представляє рядки з тимчасової таблиці та видаляє останню. Ось код закінченого рішення:
CREATE TABLE # Transactions (actid INT, tranid INT, val MONEY, balance MONEY); CREATE CLUSTERED INDEX idx_actid_tranid ON # Transactions (actid, tranid); INSERT INTO #Transactions WITH (TABLOCK) (actid, tranid, val, balance) SELECT actid, tranid, val, 0.00 FROM dbo.Transactions ORDER BY actid, tranid; DECLARE @prevaccount AS INT, @prevbalance AS MONEY; UPDATE #Transactions SET @prevbalance = balance = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END, @prevaccount = actid FROM #Transactions WITH(INDEX(1), TABLOCKX) OPTION (MAXDOP 1); SELECT * FROM # Transactions; DROP TABLE # Transactions;
План цього рішення показано на малюнку. Перша частина представлена інструкцією INSERT, друга – UPDATE, а третя – SELECT:
У цьому рішенні передбачається, що при оптимізації виконання UPDATE завжди буде виконуватися впорядкований перегляд кластеризованого індексу, і у рішенні передбачено низку підказок, щоб запобігти обставинам, які можуть завадити цьому, наприклад, паралелізм. Проблема в тому, що немає жодної офіційної гарантії, що оптимізатор завжди переглядатиметься в порядку кластеризованого індексу. Не можна покладатися на особливості фізичних обчислень, коли потрібно забезпечити логічну коректність коду, якщо в коді немає логічних елементів, які за визначенням можуть гарантувати таку поведінку. У цьому коді немає жодних логічних особливостей, які б гарантувати саме таку поведінку. Звичайно вибір, використовувати чи ні цей спосіб, лежить цілком на вашому совісті. Я вважаю, що безвідповідально використовувати його, навіть якщо ви тисячі разів перевіряли і «начебто все працює, як треба».
На щастя, SQL Server 2012 цей вибір стає практично непотрібним. За наявності виключно ефективного рішення з використанням віконних функцій агрегування не доводиться замислюватися про інші рішення.
Вимірювання продуктивності
Я провів вимірювання та порівняння продуктивності різних методик. Результати наведені нижче.
Я розбив результати на два графіки через те, що спосіб з використанням вкладеного запиту або з'єднання настільки повільніший за інших, що мені довелося використовувати для нього інший масштаб. У будь-якому випадку, зверніть увагу, що більшість рішень демонструють лінійну залежність обсягу роботи від розміру секції, і лише рішення на основі вкладеного запиту чи з'єднання показують квадратичну залежність. Також ясно видно, наскільки ефективніше нове рішення на основі віконної функції агрегування. Рішення на основі UPDATE зі змінними теж дуже швидке, але з описаних причин я не рекомендую його використовувати. Рішення з використанням CLR також є цілком швидким, але в ньому потрібно писати весь цей код.NET і розгортати збірку в базі даних. Як не подивися, а засноване на наборах рішення з використанням віконних агрегатів залишається найкращим.
