В мире IT есть много разных концепций и подходов, которые облегчают процесс разработки, расширения архитектуры и создания прочных продуктов. KISS, DRY, SOLID и прочие умные слова - это то, что должен знать программист для того, чтобы считаться как минимум неплохим. Но в данном посте будет затронута и без того известная тема: все эти подходы - это рекомендации, а не безукоризненный закон.

Здесь я хочу рассказать о конкретных ситуациях, с которыми я сталкивался, работая на должности разработчика MS SQL.

DRY

С плюсами тут все понятно:

• Меньше кода на решение задачи = больше сделанного функционала за ту же зарплату.

• Если дубликат появился 1 раз, то это следствие не самого лучше подхода к архитектуре, что может привести к появлению ещё большего числа повторяющегося кода.

• Чем больше дублей, тем выше шанс упустить очередное повторение при модификации или дебагинге модуля. Каждая следующая встреча с таким куском кода будет вынуждать проходить по всем возможным местам, где тоже нужно вносить те же изменения.

Однако, в закромах у меня найдётся пара ситуаций, когда мне пришлось посягнуть на святое и нарушить этот принцип, дабы использование сделанного мной функционала не приносило боль уважаемым пользователям.

Дублирование фильтра

Нужно было добавить сгруппированные данные для уже имеющегося запроса. Всех деталей уже не упомнить, но суть задачи была такова, что оконные функции не смогли бы помочь, так что пришлось городить отдельный SELECT.

;WITH CTE AS (
	SELECT ПолеДляСвязки , SUM(Поле1) AS Сумма, AVG(Поле2) AS Среднее
  FROM Table1 t1
  ...десятки джоинов...
  ...какие-то группировки...
)
SELECT p.Поле1, p.Поле2, .. p.ПолеХ, r.Сумма, r.Среднее
FROM Table1 t1
JOIN CTE r ON t1.ПолеДляСвязки = r.ПолеДляСвязки
...десятки джоинов...
WHERE t1.ПолеДляФильтрации = 'Какое-то значение'

И тут возникла проблема - в таблице Table1 были десятки миллионов записей, а куча дополнительных джоинов повышала время выполнения в дополнительном селекте до бесконечности. Решить эту проблему очень легко - передать фильтр из основного запроса во внутренний.

;WITH CTE AS (
  SELECT ПолеДляСвязки , SUM(Поле1) AS Сумма, AVG(Поле2) AS Среднее
  FROM Table1 t1
  ...десятки джоинов...
  ...какие-то группировки...
  WHERE t1.ПолеДляФильтрации = 'Какое-то значение'
)
SELECT p.Поле1, p.Поле2, .. p.ПолеХ, r.Сумма, r.Среднее
FROM Table1 t1
JOIN CTE r ON t1.ПолеДляСвязки = r.ПолеДляСвязки
...десятки джоинов...
WHERE t1.ПолеДляФильтрации = 'Какое-то значение'

Конечно, многие заметят, что можно использовать подзапрос, тогда бы сработал Join push predicate down, и дублей можно было избежать, но:

• Может и не сработать, особенно если в запросе и без того много разных действий, тогда придется в любом случае дублировать фильтр.

• CTE имеет свои meta данные, что позволяет оптимизатору обрабатывать эти данные как отдельную таблицу. Это сильно может ускорить работу запроса.

• Если не повезет, то оптимизатор выберет такой план выполнения, что для каждой строки внешнего запроса, подзапрос будет перевыполняться.

• CTE выглядит более читабельно и мне нравится визуально, так что я так хочу

Не использовать хинты

Разработчик пишет что хочет получить, оптимизатор решает как это выполнить на уровне данных, это и так все знают. Ему видно лучше, и не стоит ему оставлять хинты.

В принципе, с этим тяжело не согласиться. Оставленный очередной NOLOCK для ускорения, может привести к неточным вычислениям спустя много лет в новом модуле, а указание INNER LOOP JOIN может испортить вам жизнь, когда количество записей в таблице нельзя будет посчитать по пальцам двух рук. Однако господа из Microsoft зачем-то нам оставили возможность их использовать? Примеры ситуаций с уместными подсказками можно придумывать бесконечно, так что рассмотрим две ситуации, где без подсказки работает абсолютно неправильно, и где её отсутствие вызывает лишь мелкие неудобства

CURSOR HINTS

У меня как минимум 2 раза возникала ситуация (что с моим кодом, что с унаследованным), когда после обновления статистики для процедуры генерировался новый план выполнения, в котором курсор становился генератором проблем.

Предположим у нас есть максимально простой курсор:

DECLARE OrdersCursor CURSOR FOR
SELECT id  
FROM dbo.Orders 
WHERE NextOrder IS NULL  

OPEN OrdersCursor   
FETCH NEXT FROM OrdersCursor INTO @id    
WHILE @@FETCH_STATUS = 0   
	BEGIN         
	
	EXEC dbo.CreateEmptyNextOrder @parent = @id
	
	FETCH NEXT FROM OrdersCursor INTO @id  
END   
CLOSE OrdersCursor   
DEALLOCATE OrdersCursor 

Пример своеобразный, но простой и понятный: у нас есть таблица с заказами, и у каждого заказа есть последующий по цепочке. Допустим мы хотим взять все текущие финальные заказы и создать им продолжения, но пустые, для последующего заполнения клиентами. Если звёзды сойдутся и вам повезет, то оптимизатор именно так и сделает. Однако может не повезти. В момент первого выполнения, или, что ещё хуже, когда на сервере поменяется сохранённый план выполнения, мы можем получить бесконечно выполняющийся курсор, который будет создавать новую строку в таблицу Orders, добавлять эту же строку в самого себя и создавать уже для нее потомков, и так до бесконечности.

Решение простое - добавить hint о том, что этот курсор STATIC, и нас интересуют только данные на момент открытия, а последующие обновления таблицы не должны влиять на работу. Данные сохранятся в tempdb во временную таблицу, по которой мы и пройдёмся курсором.

DECLARE OrdersCursor CURSOR 
STATIC --Подсказка серверу
FOR
SELECT id  
FROM dbo.Orders 
WHERE NextOrder IS NULL  

OPEN OrdersCursor --Момент фиксирования данных
FETCH NEXT FROM OrdersCursor INTO @id    
WHILE @@FETCH_STATUS = 0   
	BEGIN         
	
	EXEC dbo.CreateEmptyNextOrder @parent = @id
	
	FETCH NEXT FROM OrdersCursor INTO @id  
END   
CLOSE OrdersCursor   
DEALLOCATE OrdersCursor 

WITH (INDEX)

Оптимизатор, по ведомым лишь ему причинам, отказывается использовать нужный индекс - не частая проблема, но и палка ведь стреляет. Тут нужно быть осторожным и отталкиваться от ситуации. Конкретно у меня была отдельная процедура, внутри который оптимизатор упорно проводил INDEX SCAN кластерного ключа, игнорируя созданный мной индекс. Таблица была не слишком большой, так что сканирование + прочий функционал выполнялся за 1-2 секунды, но почему бы не довести это до пары миллисекунд, добавив всего один hint?

Бизнес логика превыше адекватности

Не совсем про методологии разработки, а скорее про ситуации, когда пользователи хотят всё больше удобств, а руководство орудует логикой "ну если просят, то надо сделать".

Ситуация следующая - приходит товар на склад, работники его принимают, создают накладные на каждую партию, запускают обработку этого документа (внутри этой обработки куча бизнес логики, каждая часть которой видна конкретному отделу, но на складе интересна только генерация штрих-кодов), печатают штрих-коды, наклеивают и ставят по местам. Вроде бы простой и отлаженный процесс, всё шло хорошо, пока работники не подумали "чёт долго обрабатывается".

Честно говоря, обработка была не особо долгой, если брать объём накладной в несколько десятков строк, всего несколько секунд. Но когда счёт шёл на сотни или тысячи записей, то могло вырастать и до 3-х минут. На наш совет "создавать накладные меньшим объёмом, ведь разницы никакой" мы услышали лишь "хотим так".

Делать нечего, пришлось убрать общую транзакцию, заменив на построчные. После этого пользователи могли прерывать обработку накладной в любой момент, клеить наклейки на обработанную продукцию и ставить.

Но тут возникла другая проблема - в начале обработки, статус накладной меняется на "в процессе", запрещающий трогать этот документ другим людям. Теперь этот функционал тоже нужно как-то учесть в переработанном модуле, добавив статусу возможность поменяться обратно в случае отмены со стороны клиента. Тут на помощь пришла возможность создавать вложенные транзакции.

• Если происходит ошибка во внутренней транзакции (такое могло быть из-за внутренних проверок) или процедуру прервали во время её выполнения - откатываем к точке сохранения

• Внешнюю транзакцию подтверждаем на момент точки сохранения + меняем статус обратно

Пользователи конечно остались довольны, а мы добавили в нашу систему кучу костылей и необходимость помнить о том, что процедура может прерываться в любой момент, и какие-то её части должны откатываться, а какие-то сохраняться в любом случае.

Заключение

Это лишь пара примеров, подтверждающих потребность хоть иногда делать кривые системы или писать код не по шаблону, так как того просит бизнес. У меня есть ещё несколько историй, когда мне приходилось идти на сделку со внутренним чистоплюем и делать "как надо", и, если этот пост не сильно закидают тапками, напишу продолжение.