Избегайте связанных подзапросов
Обоснование
На заре SQL оптимизаторы плохо справлялись с упрощением сложных SQL-выражений со связанными подзапросами. Они вслепую запускали циклы внутри циклов, снова и снова просматривая таблицу из самого внутреннего цикла. Далее рассмотрен пример, иллюстрирующий подобное поведение. В этих двух запросах столбец “х” не может принимать значение NULL, а таблица “Foo” много больше таблицы “Bar” Оба запроса приводят к одинаковому результату. Сравните:
SELECT a, b, с
FROM Foo
WHERE Foo.x IN (SELECT x FROM Bar);
и
SELECT a, b, с
FROM Foo
WHERE EXISTS (SELECT *
FROM Bar
WHERE Foo.x = Bar.x;
В старых реализациях SQL предикат EXISTS() объединил бы две таблицы, что заняло бы много времени. Предикат IN() поместил бы меньшую таблицу в главное хранилище и просмотрел бы ее, возможно, сортируя для ускорения поиска. Но теперь это делается не совсем так. В зависимости от конкретного оптимизатора и методов доступа, связанные подзапросы могут уже не быть такими громоздкими, какими были раньше. В некоторых продуктах допускается создание индексов, которые “предварительно” соединяют таблицы, позволяя обрабатывать такие запросы быстрее.
Однако людям трудно читать связанные подзапросы, да и оптимизаторы тоже до сих пор не всегда достаточно сообразительны. Рассмотрим таблицу, которая моделирует ссуды и платежи с кодом состояния для каждого платежа. Это классическое отношение “один ко многим”. Задача заключается в том, чтобы выбрать ссуды, у которых все платежи имеют статус “F”:
CREATE TABLE Loans
(loan_nbr INTEGER NOT NULL,
payment_nbr INTEGER NOT NULL,
payment_status CHAR(1) NOT NULL
CHECK (payment_status IN ('F', 'U', 'S')),
PRIMARY KEY (loan.nbr, paymentjibr));
Одно решение задачи состоит в использовании связанного скалярного подзапроса в списке SELECT:
SELECT DISTINCT (SELECT loan_nbr FROM Loans AS L1 GROUP BY L1.loan_nbr HAVING COUNT(L1.payment_status) = C0UNT(L2.loan_nbr)) AS parent FROM Loans AS L2 WHERE L2.payment_status = 'F' GROUP BY L2.1oan_nbr;
В этом подходе задача решается в обратном направлении: в отношении “один ко многим” она идет от “многих” к “одному”. Немного подумав и начав с другой стороны, вы получите следующий ответ:
SELECT loan_nbr
FROM Loans
GROUP BY loan_nbr
HAVING MAX(payment_status) = "F"
AND MIN(payment_status) = 'F';
Разобраться в рекурсивных ссылках и корреляциях трудно как человеку, так и машине. Большинство оптимизаторов недостаточно сообразительны, чтобы упростить запрос описанным образом.
Исключения
Если к вашей задаче проще подойти с использованием связанных подзапросов, и у вас хороший оптимизатор, то в таком случае их не стоит опасаться.
Избегайте предложений UNION
Обоснование
Предложения UNION обычно не поддаются хорошей оптимизации. Поскольку в них требуется отбрасывать избыточные дубликаты, они заставляют большинство ядер SQL производить сортировку, прежде чем представить результаты пользователю. По возможности вместо UNION используйте UNION ALL. И уж, конечно, никогда не запускайте цепочку предложений UNION, основанных на одной и той же базовой таблице, Такой код можно написать с использованием предикатов OR или с выражениями CASE.
Рассмотрим в качестве примера ужасающе неправильного употребления SQL процедуру, создающую динамический SQL, который затем выдает отчет. Кроме очевидного нарушения основных правил разработки ПО, код был настолько велик, что превышал предельный размер текстового файла в SQL Server. В нем производилась попытка создать полный отчет по базе данных, используя предложения UNION. Чтобы расположить 12 строк отчета в правильном порядке, каждор! присваивается буква алфавита. Чтобы показать полную версию кода, потребовалось бы несколько страниц, поэтому здесь приведен фрагмент, отвечающий за вывод данных на печать. Я не пытался переделать этот код, поэтому в приведенном кусочке содержится много нарушений принципов хорошего кодирования.
UNION
SELECT DISTINCT 'J' AS section,
'NUMBER CHECKS' AS description,
' ' AS branch,
COUNT(DISTINCT GL.source) AS totali,
0 AS total2
FROM GeneralLedger AS GL
WHERE GL.period >= :start_period
AND GL.period <= :end_period
AND GL.year_for_period = :period_yr
AND GLaccountjiumber IN ('3020')
AND GL.journal_id IN ('CD')
UNION
SELECT DISTINCT 'С1 AS section,
'CASH RECEIPTS' AS description,
'' AS branch,
SUM(GL.amount) * -1 AS totali,
0 AS total2
FROM GeneralLedger AS GL
WHERE GL.period >= :start_period
AND GL.period <= :end_period
AND GL.year_for_period = :period_yr
AND GL.account_number = '1050'
AND GL.journal_id IN ('CR')
UNION
SELECT DISTINCT 'D' AS section,
'NUMBER INVOICES' AS description,
' ' AS branch,
COUNT(DISTINCT GL.source) AS totali,
0 AS total2
FROM GeneralLedger AS GL
WHERE GL.period >= :start_period
AND GL.period <= :end_period
AND GL.year_for_period = :period_yr
AND GL.account_number IN ('6010', '6090')
AND GL.journal.id IN ('SJ')
UNION
SELECT DISTINCT
'E1 AS section,
'VOUCHER TOTAL1 AS description,
' ' AS branch,
SUM(GL.amount) * -1 AS totali,
0 AS total2
FROM GeneralLedger AS GL
WHERE GL.period >= :start_period
AND GL.period <= :end_period
AND GL.year_for_period = :period_yr
AND GL.account_number = '3020'
AND GL.journal_id IN ('PJ',TJ1)
UNION
SELECT DISTINCT
'F' AS section,
'CHECKS PRINTED' AS description,
' ' AS branch,
SUM(GL.amount) AS totali,
0 AS total2
FROM GeneralLedger AS GL
WHERE GL.period >= :start_period
AND GL.period <= :end_period
AND GL.year_for_period = :period_yr
AND GL.account_number IN ('3020')
AND GL.journal_id IN ('CD')
UNION
SELECT DISTINCT
'K' AS section,
'NUMBER VOUCHERS' AS description,
' ' AS branch,
COUNT(DISTINCT GL.source) AS totali,
0 AS total2
FROM GeneralLedger AS GL
WHERE GL.period >= :start_period
AND GL.period <= :end_period
AND GL.year_for_period = :period_yr
AND GL.account_number IN ('3020')
AND GL.journal_id IN ('PJ', 'TJ');
Обратите внимание, что “section”, “description” и “branch” — это “заглушки”, выделяющие место для столбцов в других предложениях UNION, не показанных здесь. Последнюю часть кода можно оформить в виде связной отдельной процедуры:
CREATE PROCEDURE GeneralLedgeSummary (start_period DATE, end_period DATE)
SELECT
COUNT(DISTINCT CASE WHEN acct_nbr = '3020' AND journal_code = 'CD'
THEN source ELSE NULL END),
-SUM(CASE WHEN acct._nbr = '1050' AND journal_code ='CR'
THEN amount ELSE 0.00 END),
COUNT(DISTINCT CASE WHEN acct_nbr IN ('6010', '6090') AND journal_code = 'SJ'
THEN source ELSE NULL END),
-SUM(CASE WHEN acct__nbr = '1050' AND journal_code = 'CR'
THEN amount ELSE 0.00 END),
SUM(CASE WHEN acctjibr = '3020' AND journal_code = 'CD1
THEN amount ELSE 0.00 END),
COUNT(DISTINCT CASE WHEN acct_nbr = '3020' AND journal_code IN ('PJ', 'TJ')
THEN source ELSE NULL END)
INTO j_tally, c_total, d_tally, e_total, f_total, k_tally
FROM GeneralLedger AS GL
WHERE period BETWEEN start_period AND end_period;
Исключения
Иногда UNION (или UNION ALL) — это действительно то, что вам нужно. Другие подобные операции в SQL-92 — EXCEPT (EXCEPT ALL) и INTERSECT (INTERSECT ALL) —- до сих пор не нашли широкого применения.
Тестирование SQL
Когда вы впервые пишете схему, вы, возможно, сгенерируете для нее некоторые тестовые данные. Полистав литературу, вы узнаете о так называемом наборе Армстронга (Armstrong set) — минимальном числе строк, которое необходимо для тестирования всех ограничений схемы. Построить набор Армстронга непросто, но проверить схему можно и с меньшими усилиями.