Про розробку по і ефективності трюки в sql - використовуємо case

Після серії статей "Основи SQL" я вирішив спробувати пригадати деякі нестандартні варіанти використання Transact-SQL і почати серію статей "Трюки в SQL".

База даних для прикладів

Якщо в минулій серії я використав максимально просту БД, яку створив і наповнив скриптами, то тепер я вирішив використовувати стандартні приклади від Microsoft. Причина в тому, що будуть потрібні більш складні структури і дані, та й приклади стануть ближчими до реального життя.

план виконання

Якщо серйозно і не вдаючись в подробиці, движку бази даних необхідно знати, в якій послідовності і яким способом з'єднувати таблиці, які індекси використовувати і т.п. Іншими словами, перед виконанням запиту необхідно побудувати (або взяти з кешу) план виконання.

Актуальний план виконання ви можете подивитися в SSMS (SQL Server Management Studio), вибравши його в пункті меню "Query / Include Actual Execution Plan" або натиснувши "Ctrl + M". В результаті, після виконання пакету запитів ви побачите вкладку "Execution Plan", в якій для кожного запиту з пакета буде виведений план виконання в графічному вигляді.

Аналіз планів виконання це окрема цікава тема, можливо, коли-небудь розповім і про це. Поки ж, якщо вам цікава ця тема (її просто необхідно знати, якщо всерйоз займатися оптимізацією запитів), можете почитати відповідний розділ MSDN. Заодно можна буде прочитати про кешування планів виконання.

синтаксис CASE

Про всяк випадок нагадаю синтаксис "CASE". оскільки його використовують порівняно рідко:

Хочу звернути вашу увагу на те, що "ELSE" можна не вказувати, тоді, в разі відсутності відповідного "WHEN" повернеться NULL.

Також доцільно зазначити, що повертаються значення повинні бути сумісні. Тобто, так написати можна:

Зверніть увагу: той же правило, як неважко здогадатися, працює не тільки для констант, а й для стовпців таблиць. Так що будьте пильні - ваш запит може працювати на одних даних (скажімо, коли код товару зберігається в рядку, але містить тільки цифри) і не працювати на інших (додали в код товару літери).

SUM по CASE

Отже, на першому місці, по праву - підсумовування з умовою всередині (повертає 0 або 1). Цей прийом дозволяє замість декількох однотипних запитів написати один і, що часто буває, непогано поліпшити продуктивність.

Приклад кілька синтетичний, зате простий - уявіть, що потрібно періодично дізнаватися загальну кількість товарів, а також кількість червоних і чорних товарів. Так можна вирішити задачу "в лоб":

А так, використовуючи "SUM по CASE":

Якщо в SSMS (SQL Server Management Studio) включити висновок актуального плану виконання. то можна побачити, що вартість останнього запиту в два рази менше, ніж сумарна вартість перших трьох.

Як ліричного відступу - хороше розуміння роботи Microsoft SQL Server мені найчастіше гріло душу тоді, коли після "непоганого" поліпшення продуктивності запиту, він починав виконуватися в десятки разів швидше. І ще один цікавий факт - ми з моїм колегою (адміністратором SQL) не змовляючись заздалегідь зійшлися на думці, що вільне оперування "SUM по CASE" говорить про добре знання SQL. Так що я вам щойно розповів один із секретів, як зійти за знавця SQL :)

ORDER по CASE

Тепер спробуємо з "CASE":

Вже краще. Однак нам, якщо розібратися, просто пощастило з порядком ідентифікаторів. Якщо ми відсортуємо дочірні елементи за назвою, то відразу побачимо в чому проблема.

Для того, щоб правильно сортувати рядки всередині кожної батьківської групи нам буде потрібно ще один маленький трюк:

Сподіваюся, вам було цікаво дізнатися ці прийоми або освіжити пам'ять про них.

Є ще один маленький нюанс в сортуванні по CASE. Якщо в Case описує кілька можливих полів сортування (у Вашому випадку це ProductCategoryID і ParentProductCategoryID) то тип всіх можливих полів повинен бути таким щоб міг бути неявно наведено сервером до типу ПЕРШОГО поля описаного в CASE-e.
Приклад - скрип нижче.
При значеннях @SortMode рівному 1, 2 і навіть 3 скрипт буде віполнять відмінно, а ось якщо 4 - валить помилку

Msg 245, Level 16, State 1, Line 28
Conversion failed when converting the nvarchar value '3.14159' to data type int.

Так що будьте уважні!
--------
IF object_id ( 'tempdb .. # TmpTbl', 'U') IS NOT NULL
DROP TABLE #TmpTbl

CREATE TABLE #TmpTbl (IId INT, IIdExt INT, NCode NVARCHAR (10), NCodeExt NVARCHAR (10))

; WITH LineNumber AS (
SELECT F1.INo + F2.INo * 10 AS INo
FROM (
SELECT 0 as INo
UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS F1
CROSS JOIN (
SELECT 0 as INo
UNION SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS F2

)
INSERT INTO #TmpTbl
SELECT ln.Ino, ln.Ino * SIN (ln.Ino), STR (ln.Ino), ln.Ino * PI () FROM LineNumber AS ln

DECLARE @SortMode INT
SET @SortMode = 4

SELECT t. * FROM #TmpTbl AS t ORDER BY
CASE @SortMode
WHEN 1 THEN t.IId
WHEN 2 THEN t.IIdExt
WHEN 3 THEN t.NCode
WHEN 4 THEN t.NCodeExt
END
ASC

Не сперечаюся, тим більше, що я про це написав у розділі про CASE :) Або недостатньо акцентував?

Добрий день, а ось таке питання:
Припустимо в процедуру надходить кілька параметрів-фільтрів.
Використовувати Case замість копипаста зручно і приміром виходить так:
select * from table
where country =
case @par
when 1 then 'Ukraine'
when 2 then 'Russia'
.
when null then country

(Тобто останній рядок вказує що всі країни потраплять до вибірки) - наскільки це вірне рішення, чи вплине зайве порівняння на швидкість?

І друге питання: якщо в одному випадку потрібно іспользованть 'like' - для несуворого збіги, а для суворого краще було б '=', то чи можна як то конструкцію case застосувати в цьому випадку; поки доводилося все фільтрувати через like (country like country для суворого) або копіпаст всю процедуру зі зміною 1й рядки (можливо так все і роблять, і нічого такого в цьому немає, в загальному в цьому то полягає і питання))
Дякуємо

У загальному випадку, якщо піклуватися про продуктивність, краще розгортати через "OR".
Для конкретних випадків, можна подивитися план виконання для двох варіантів (за умови, що обсяг даних достатній) і вибрати з огляду на різницю (або її відсутність) вартості виконання.