Що швидше 0 або NULL
При використанні агрегатних функцій на плані виконання, залежно від вхідного потоку, може зустрічатися два оператори:Stream AggregateіHash Match.
Для виконання першого може вимагатися попередньо відсортований вхідний набір значень і при цьому Stream Aggregate не блокує виконання наступних за ним операторів.
У свою чергу,Hash Matchє блокуючим оператором (за рідкісним винятком) і не вимагає сортування вхідного потоку. Для роботиHash Matchвикористовується хеш-таблиця, яка створюється в пам'яті та у разі неправильної оцінки очікуваної кількості рядків, оператор може зливати результати вtempdb.
Разом виходить, що Stream Aggregate добре працює на невеликих відсортованих наборах даних, аHash Matchдобре справляється з великими не відсортованими наборами і добре піддається паралельній обробці.
Тепер, коли ми подолали теорію, почнемо дивитися як працюють агрегатні функції.
Припустимо, що нам потрібно підрахувати середню ціну серед усіх продуктів:
За таблицею із досить простою структурою:
Оскільки у нас відбувається скалярна агрегація, на плані виконання ми очікуємо побачимоStream Aggregate:
Всередині цей оператор виконує дві операції, що агрегуютьCOUNT_BIGіSUM(хоча фізично виконується це як одна операція) по стовпцю Price:
Не забуваємо, що середнє обчислюється тільки дляNOT NULL, оскільки операціяCOUNT_BIGйде по стовпцю, а не із зірочкою. Відповідно, такий запит:
поверне як результат не 4, а 6.
Тепер подивимося наCompute Scalar, всередині якого є цікавий вираз для перевірки поділу на нуль:
Спробуємо підрахувати загальну суму:
План виконання залишиться тим самим:
Але якщо подивитися на операції, що виконуєStream Aggregate…
можна трохи здивуватися. НавіщоSQL Serverпідраховує кількість, якщо мені потрібна лише сума? Відповідь криється вCompute Scalar:
Якщо не брати до увагиCOUNT, то згідно з семантикою мовиT-SQL, коли немає рядків у вхідному потоці, то ми повинні повертатиNULL, а не0. Така поведінка працює як для скалярної, так і для векторної агрегації:
Більше того, така перевірка робиться як дляNULL, так і дляNOT NULLстовпців. Тепер розглянемо приклади, в яких будуть корисні описані вище особливостіSUMіAVG.
Якщо ми хочемо порахувати середнє, то не потрібно використовуватиCOUNT + SUM:
Оскільки такий запит буде менш ефективним, ніж явне використання AVG.
Далі… Явно передаватиNULLв агрегатну функцію немає необхідності:
Бо в такій конструкції:
Оптимізатор підстановку робить автоматично:
Але що якщо я хочу отримати 0 в результатах замістьNULL? Дуже часто використовуютьELSEі не замислюються:
Очевидно, що в такому разі ми досягнемо бажаного ... та й одне попередження перестане муляти очі:
Хоча найкраще писати запит ось так:
І це добре не тому, що операторCASEпрацюватиме швидше. Ми товже знаємо, що оптимізатор туди підставляєELSE NULLавтоматом… Тож у чому переваги останнього варіанта?
Як виявилося, операції агрегування, в яких переважають NULL значення обробляються швидше.
Виконання у мене зайняло:
І виконуємо повторно:
Не так суттєво, але привід для оптимізації, проте, це дає в певних ситуаціях.
Кінець вистави та завіса? Ні. Це ще не все…
Як казав один мій знайомий: «Немає ні чорного, ні білого… Світ багатобарвний» і тому наостанок наведу цікавий приклад, коли NULL може шкодити.
Створимо повільну функцію та тестову таблицю:
І виконаємо запит:
Тепер спробуємо результат виразу, який передається вSUM, обернути вISNULL:
Швидкість виконання скоротилася вдвічі. Відразу скажу, що це не магія… А баг у движкуSQL Server-а, якийMicrosoftвже «начебто» виправила вSQL Server 2012 CTP.
Суть проблеми в наступному: результат виразу всередині функційSUMабоAVGможе виконуватися двічі, якщо оптимізатор вважає, що може повернутисяNULL.
Всім дякую за увагу.
Все тестувалося на Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) . Плани виконання брав ізdbForge Studio.
Тепер дозвольте пару слів сказати з іншої теми.
Вже давно мене відвідувала думка зібрати в одному місці людей, яким цікаві бази даних і все, що з ними пов'язано. Наприкінці цього місяця, я планую організувати невелику зустріч користувача-групи по SQL Server . У рамках неї планується 2 доповіді. Один буде від мене, в рамках якого я постараюся розповісти про «підводне каміння» під час роботи зXMLіXQuery, торкнутися питань продуктивності і показати кілька цікавих трюків. Друга доповідь теж є, але поки що під питанням… тому якщо є бажаючі виступити та поділитися чимось цікавим поSQL Serverпишіть мені на пошту вказану у профілі.
Участь повністюбезкоштовна, але кількість місця обмежена. З цієї причини потрібно пройти попередню реєстрацію, щоб я міг знати, скільки людей планує відвідати зустріч.