Чистий код, тестування і продуктивність SQLScript

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

Розділена на три частини:

Читабельність — формування, імена, декомпозиція, коментарі.
Продуктивність — практичні правила оптимізації.
Тестування і аналіз — юніт-тести, дебаг, plan visualizer, code analyzer.

Читабельність коду

Форматування

Правило	Чому
Один statement = один логічний блок коду з відступами	Око має триматись за вертикальну вісь
Ключові слова — у верхньому регістрі (`SELECT`, `FROM`, `WHERE`)	Розрізняє синтаксичні елементи від ідентифікаторів
Імена обʼєктів — у нижньому	Однорідність
Коми в кінці рядка, не на початку наступного	Стандарт у HANA-екосистемі
Один SELECT-вираз — один рядок	Легко мовчки прочитати кожну колонку

-- Погано: усе в одну стрічку
SELECT a.id,a.title,b.firstname||' '||b.lastname AS name FROM tasks a INNER JOIN users b ON a.assignee=b.id WHERE a.status=3 ORDER BY a.due_date;

-- Добре: вертикально, з відступами
SELECT a.id,
       a.title,
       b.firstname || ' ' || b.lastname AS name
FROM tasks AS a
   INNER JOIN users AS b ON a.assignee = b.id
WHERE a.status = 3
ORDER BY a.due_date;

Імена

Mnemonic — імʼя має пояснювати суть. lt_overdue_tasks, не lt_x чи lt_tmp1.
Префікси типу — традиція в SAP-екосистемі: lv_ (local scalar var), lt_ (local table var), iv_/it_ (importing), ev_/et_ (exporting), rv_/rt_ (returning), cv_/ct_ (changing). Не обовʼязкові, але упорядковують код.
Уникай скорочень, які зрозумілі тільки автору. lt_tasks_to_close краще за lt_t2c.

Декомпозиція складних запитів

Великий SELECT з 5 join-ами і 3 підзапитами — антипаттерн. Розбий на кроки через table variables:

-- Погано: один монстр
SELECT t.id, t.title, u.firstname, p.name, COALESCE(s.cnt, 0) AS log_cnt
FROM tasks t
   LEFT JOIN users u ON u.id = t.assignee
   LEFT JOIN projects p ON p.id = t.project
   LEFT JOIN ( SELECT task_id, COUNT(*) AS cnt FROM logs
               WHERE log_type IN ('ERR','WARN')
               GROUP BY task_id ) s
        ON s.task_id = t.id
WHERE t.status NOT IN (5, 6)
  AND t.due_date < CURRENT_DATE;

-- Добре: проміжні кроки з говорящими іменами
DO BEGIN
   lt_active_tasks =
      SELECT id, title, assignee, project
      FROM tasks
      WHERE status NOT IN (5, 6)
        AND due_date < CURRENT_DATE;

   lt_log_counts =
      SELECT task_id, COUNT(*) AS cnt
      FROM logs
      WHERE log_type IN ('ERR', 'WARN')
      GROUP BY task_id;

   lt_result =
      SELECT t.id, t.title, u.firstname, p.name,
             COALESCE(l.cnt, 0) AS log_cnt
      FROM :lt_active_tasks AS t
         LEFT JOIN users     AS u ON u.id = t.assignee
         LEFT JOIN projects  AS p ON p.id = t.project
         LEFT JOIN :lt_log_counts AS l ON l.task_id = t.id;

   SELECT * FROM :lt_result;
END;

Бонус: кожний проміжний крок легко перевірити окремо — SELECT * FROM :lt_active_tasks показує точно те, що ввійде в наступний join.

Гранулярність процедур і функцій

“Перше правило функцій — вони мають бути малі. Друге — навіть менші, ніж це.” — Robert C. Martin, Clean Code

Декілька правил-орієнтирів:

Процедура більше 30–40 рядків — кандидат на декомпозицію.
Якщо процедура робить >1 справу (читає → перетворює → пише → логує) — розбити по відповідальностях.
Якщо для рев’ю треба двічі прокручувати екран — зашироко.

Коментарі

Тип	Чи треба
Header (метадані: автор, дата, версія)	Часто зайві — Git і ім’я процедури вже все говорять
Section dividers (`-- =====`)	Ознака, що процедура завелика — розбий
Опис намірів (чому, не що)	✅ Корисні. `-- округлюємо вгору, бо bilable hours`
Опис того, що видно з коду	❌ Шум. `-- збільшуємо лічильник` біля `i = i + 1`

⚠️ Грабля: застарілі коментарі — гірше за відсутні

Код оновлюється частіше, ніж коментарі. Коментар, що описує неактуальну логіку, активно вводить в оману. Якщо коментар не додає нічого — видали.

Дужки і повний синтаксис

-- Без дужок: оператор пріоритету ламає інтуїцію
WHERE a OR b AND c          -- це a OR (b AND c), не (a OR b) AND c

-- З дужками: однозначно
WHERE (a OR b) AND c

Те саме для арифметики: a + b * c і (a + b) * c — різні. Не покладайся на пам’ять читача про пріоритети — пиши явно.

Без скорочень. INNER JOIN явно краще за JOIN. LEFT OUTER JOIN — за LEFT JOIN. UNION ALL — обов’язково повністю (бо UNION без ALL має зовсім інший сенс — дедуплікує).

Рекомендації по продуктивності

Зменшуй обʼєм даних якомога раніше

-- Погано: фільтрація після join
SELECT t.*, u.firstname
FROM tasks t
   INNER JOIN users u ON u.id = t.assignee
WHERE t.status = 3                          -- HANA, ймовірно, оптимізує, але не завжди
  AND t.created_at > ADD_DAYS(CURRENT_DATE, -7);

-- Добре: явна фільтрація до join (через CTE або table variable)
DO BEGIN
   lt_recent = SELECT id, assignee, title FROM tasks
               WHERE status = 3
                 AND created_at > ADD_DAYS(CURRENT_DATE, -7);

   SELECT t.*, u.firstname
   FROM :lt_recent AS t
   INNER JOIN users AS u ON u.id = t.assignee;
END;

Крім фільтрації рядків, фільтруй колонки — SELECT id, name краще за SELECT *, особливо для широких таблиць у column store.

Уникай переключення row ↔ column store

HANA зберігає таблиці у двох engine-ах: row store (швидкий для одиничних рядків, наприклад config-таблиці) і column store (швидкий для аналітики). Якщо запит змішує таблиці з різних store-ів, дані доводиться матеріалізувати — це коштує часу.

Симптом у Plan Visualizer: вузол Materialize Result між Row Search і Column Search. Лікування — переглянути архітектуру: робочі великі таблиці мають бути в одному store (зазвичай column).

Декларативні запити > імперативні

Повтор з Декларативного SQLScript, бо критично:

Декларативний код HANA може паралелізувати, оптимізатор обрізає непотрібні гілки.
Імперативний (FOR, WHILE, курсор, IF навколо statement-ів) виконується послідовно як написано.

Перед тим, як вставити цикл, спитай себе: чи можна те саме зробити одним SELECT-ом з window-функціями, JOIN-ами або recursive CTE? Майже завжди — можна.

Скалярні UDF — повільні

Scalar UDF у SELECT-list викликається для кожного рядка результату. Навіть проста функція може помітно сповільнити запит на сотнях тисяч рядків.

Альтернативи:

Заміни scalar UDF на CASE-вираз або вбудовану SQL-функцію.
Якщо логіка складна — переведи у table UDF з join-ом.
Виміри runtime з і без функції на репрезентативному обʼємі.

⚠️ Грабля: DETERMINISTIC більше не оптимізує

Раніше керівництво SAP радило позначати чисті scalar UDF ключовим словом DETERMINISTIC — теоретично HANA могла кешувати результати. На практиці тести показали, що DETERMINISTIC в нинішніх версіях скоріше сповільнює, ніж пришвидшує. Не використовуй.

DML у циклах — стоп

-- Антипаттерн — N round-trip-ів до БД
FOR i IN 1..1000 DO
   INSERT INTO target VALUES (:i, 'value ' || :i);
END FOR;

-- Правильно — один statement
INSERT INTO target
   SELECT GENERATED_PERIOD_START AS i,
          'value ' || TO_NVARCHAR(GENERATED_PERIOD_START) AS val
   FROM SERIES_GENERATE_INTEGER(1, 1, 1001);

Code Analyzer (див. нижче) ловить це через правило DML_STATEMENTS_IN_LOOPS.

CE-функції — застарілі

Старі CE_*-функції (CE_COLUMN_TABLE, CE_PROJECTION і ін.) — попередники SQL-операторів з ранньої HANA. У сучасних версіях вони перешкоджають оптимізатору. Використовуй еквівалентні SQL-statement-и.

Code Analyzer-правило: USE_OF_CE_FUNCTIONS.

Тестування і дебаг

SQL Console — швидкі перевірки

Найпростіша форма тесту — виконати анонімний блок у SQL Console SAP HANA Database Explorer (або SAP HANA Studio):

DO BEGIN
   DECLARE lt_test TABLE (id INT, name NVARCHAR(50));
   lt_test = SELECT 1 AS id, 'A' AS name FROM dummy
             UNION ALL SELECT 2, 'B' FROM dummy;

   CALL my_procedure(:lt_test, ?);
END;

?-placeholder для вихідних таблиць — Console сама покаже результат.

SQLSCRIPT_LOGGING — логування

Вбудована бібліотека для structured-логів з рівнями. Використання:

-- 1) Створити власну таблицю логу
CREATE TABLE mylog LIKE sys.sqlscript_logging_table_type;

-- 2) Налаштувати конфігурацію (одноразово)
CALL sys.create_configuration('my_config');
CALL sys.add_sqlscript_object('my_config', 'JBRANDEIS', 'DO_SOMETHING');
CALL sys.set_output_table('my_config', 'JBRANDEIS', 'MYLOG');
CALL sys.set_level('my_config', 'DEBUG');
CALL sys.start_logging('my_config');

-- 3) Логувати з коду
CREATE PROCEDURE do_something (IN iv_text NVARCHAR(15))
READS SQL DATA
AS BEGIN
   USING sqlscript_logging AS logger;
   logger:log(logger:level_debug, 'c1', 'Procedure parameter: ' || :iv_text);
END;

Рівні: FATAL, ERROR, WARNING, INFO, DEBUG.

SQLSCRIPT_TEST — юніт-тести

Бібліотека для unit-тестів усередині HANA. Тести оголошуються в окремій test library:

CREATE LIBRARY test_demo_dm
   LANGUAGE SQLSCRIPT TEST
AS BEGIN

   PRIVATE PROCEDURE check_table_count (
      IN iv_tabname        NVARCHAR(30),
      IN iv_expected_count INTEGER)
   AS BEGIN
      USING sqlscript_test AS test;
      DECLARE lv_query NVARCHAR(5000);
      DECLARE lv_count INTEGER;

      lv_query = 'SELECT COUNT(*) FROM ' || :iv_tabname;
      EXEC :lv_query INTO lv_count;
      test:expect_eq(:lv_count, :iv_expected_count);
   END;

   @test()
   PUBLIC PROCEDURE t_table_count
   AS BEGIN
      check_table_count('TASKS', 1000);
      check_table_count('USERS',   30);
      check_table_count('STATUS',   6);
   END;

END;

Pragma @test() позначає процедуру як тестову. Налаштування і прибирання — @TestSetUpLibrary(), @TestTearDownLibrary().

EXPECT_*-функції

Функція	Перевіряє
`EXPECT_EQ(a, b)`	`a = b`
`EXPECT_NE(a, b)`	`a ≠ b`
`EXPECT_GE(a, b)` / `EXPECT_GT(a, b)`	`a ≥ b` / `a > b`
`EXPECT_LE(a, b)` / `EXPECT_LT(a, b)`	`a ≤ b` / `a < b`
`EXPECT_NULL(a)`	`a IS NULL`
`EXPECT_TABLE_EQ(t1, t2[, ignore_order])`	Дві таблиці рівні
`FAIL(msg)`	Безумовний провал з повідомленням

SQLScript Debugger

Дебагер доступний з SAP HANA Database Explorer (View → Debugger, або Ctrl+A) і з ABAP Development Tools для AMDP.

Можливості:

Step-by-step виконання процедури/функції.
Breakpoints.
Перегляд значень scalar і table variables.
Conditional breakpoints.

⚠️ Грабля: дебаг-режим відключає оптимізації

Щоб дебагер міг крокувати по коду, процедура перекомпільовується у debug-режимі з вимкненими оптимізаціями. Тобто не міряй продуктивність у дебагу — реальний runtime в production буде іншим.

Performance analysis

Runtime measurement

SQL Console показує час виконання внизу під результатами кожного запиту. Найпростіший спосіб порівняти варіанти — виконати кожен 3–5 разів (бо перший запуск часто прогріває кеш) і дивитись медіану.

-- Variant A
SELECT a.id, a.title, b.firstname, b.lastname, b.email, t.team_text
FROM tasks AS a
   LEFT JOIN users     AS b ON a.assignee = b.id
   LEFT JOIN team_text AS t ON b.team     = t.id;

-- Variant B (зі * замість списку колонок) — у виводі видно різницю в мс
SELECT *
FROM tasks AS a
   LEFT JOIN users     AS b ON a.assignee = b.id
   LEFT JOIN team_text AS t ON b.team     = t.id;

Execution Plan і Plan Visualizer (PlanViz)

Виконавчий план показує, як саме HANA обробляє запит — які операції, в якому порядку, скільки рядків на кожному кроці. Доступ:

EXPLAIN PLAN FOR <query> — текстовий вивід.
Plan Visualizer в HANA Studio або Web IDE — графічна форма з timing-ами.

На що дивитись у плані:

Час кожного оператора — найповільніший = вузьке місце.
К-сть рядків на кожному етапі — якщо JOIN обробляє 10 млн × 10 млн рядків, перевір filter pushdown.
Materialize Result між engine-ами — переключення row/column store.
Hash Join vs Nested Loop Join — на великих обʼємах перевага у hash; nested loop означає, що оптимізатор не зміг вибрати ефективніший варіант.

SQL Analyzer (HANA Database Explorer)

Більш сучасний інструмент від PlanViz — інтегрований у HANA Database Explorer. Дає тимчасову шкалу виконання, performance trace по операторам, tables used, key bottlenecks.

Workflow для повільного запиту:

Виконай у SQL Console з ввімкненим SQL Analyzer.
Перейди в Performance Trace — побачиш, які оператори зайняли найбільше часу.
Перейди в Operator List — детальніша інформація про кожен.
Перейди в Tables Used — які таблиці і як читалися.

PlanViz для SAP BW

BW-запити не виконуються в SQL Console напряму. Щоб отримати PlanViz-файл:

Відкрий транзакцію RSRT у SAP GUI.
Вибери запит → Execute + Debug.
У debug options постав галочку Generate PlanViz File.
Виконай. Збережи файл з розширенням .plv.
Відкрий у HANA Studio.

SQLScript Code Analyzer

Статичний аналізатор, доступний як SQL-процедура ANALYZE_SQLSCRIPT_OBJECTS. Перевіряє код на типові антипаттерни. Список доступних правил:

SELECT * FROM sys.sqlscript_analyzer_rules;

Найважливіші правила:

Правило	Що ловить
`UNNECESSARY_VARIABLE`	Змінна оголошена, але не впливає на результат
`UNUSED_VARIABLE_VALUE`	Присвоєння, після якого змінна не читається
`UNCHECKED_SQL_INJECTION_SAFETY`	Параметр у динамічному SQL без `ESCAPE_*` чи `IS_SQL_INJECTION_SAFE`
`USE_OF_UNASSIGNED_SCALAR_VARIABLE`	Читання змінної до присвоєння (буде NULL)
`DML_STATEMENTS_IN_LOOPS`	INSERT/UPDATE/DELETE у циклі
`USE_OF_CE_FUNCTIONS`	Старі `CE_*`-оператори
`USE_OF_SELECT_IN_SCALAR_UDF`	SELECT у scalar UDF (= повільно)
`COMMIT_OR_ROLLBACK_IN_DYNAMIC_SQL`	Управління транзакцією в динаміці

Виклик аналізатора:

DO BEGIN
   lt_rules = SELECT rule_namespace, rule_name, category
              FROM sqlscript_analyzer_rules;

   lt_objects = SELECT schema_name,
                       procedure_name AS object_name,
                       definition
                FROM procedures
                WHERE schema_name = 'MY_SCHEMA';

   CALL analyze_sqlscript_objects(:lt_objects, :lt_rules,
                                  lt_result_objects, lt_result_findings);

   SELECT objects.*, findings.*
   FROM :lt_result_objects  AS objects
   INNER JOIN :lt_result_findings AS findings
      ON objects.object_definition_id = findings.object_definition_id;
END;

Результат — таблиця знайдених проблем з посиланнями на конкретні рядки коду.

Чек-лист готовності SQLScript-коду

Перед тим як йти у production:

Що далі

Основи мови SQLScript — синтаксис, процедури, функції
Декларативне SQLScript — SELECT, JOIN, CTE
Типи даних і функції — рядки, дати, числа
CRUD у SQLScript — INSERT, UPDATE, MERGE
Імперативне SQLScript — змінні, цикли, винятки
AMDP — ABAP-обгортка для SQLScript