Рекомендации по структурированию хранимых процедур

Как разработчик, в основном пишущий С#, я применил некоторые хорошие практики при написании кода С#. Когда я иногда пишу хранимые процедуры, мне не удается применить эти методы к коду хранимой процедуры.

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

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

По-видимому, мои товарищи по команде также разделяют мой недостаток хороших методов написания SQL.

Итак... (и здесь возникает реальный вопрос)... что такое хорошая практика для написания модульных поддерживаемых хранимых процедур?

Приветствуются как домашние методы, так и ссылки на книги/блоги. Методы, а также инструменты, которые помогают с определенными задачами.

Давайте кратко изложим некоторые области, в которых я не нашел хороших практик

Модуляция и инкапсуляция (хранится ли процедура обмена данными через временные таблицы на самом деле, как идти?)
- В С# я использую сборки, классы и методы, украшенные модификаторами доступа, чтобы выполнить это.
Отладка/тестирование (лучше, чем изменение цели отладки?)
- Инструменты отладки
- Отладка трассировки?
- Испытательные светильники?
Подчеркивание кода/логики/данных/потока управления с использованием кода структуры кода
- В С# я рефакторинг и выходим из более мелких методов, которые выполняют только одну логическую задачу.
Дублирование кода

В основном я сталкиваюсь с SQL Server как СУБД, но ответы или ответы на агенты DBMS указывают на особенности других СУБД: es, которые также помогают в вышеуказанных случаях.

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

Ответ 1

Я пишу много сложных хранимых процедур. Некоторые вещи я бы рассмотрел лучшие практики:

Не используйте динамический SQl в сохраненном proc, если вы не выполняете поисковый процесс с большим количеством параметров, которые могут или не нужны (тогда это в настоящее время одно из лучших решений). Если вы должны использовать динамический SQl в proc, всегда есть параметр ввода отладки, и если параметр debug установлен, тогда распечатайте инструкцию SQL, созданную вместо ее выполнения. Это позволит сэкономить время отладки!

Если вы выполняете более одного запроса действия в proc (insert/update/delete), используйте блоки Try Cacth и обработку транзакций. Добавьте параметр теста к входным параметрам, и когда он установлен в 1, всегда откатывайте всю транзакцию. Перед откатом в тестовом режиме у меня обычно есть раздел, который возвращает значения в таблицах, которые я затрагиваю, чтобы убедиться, что то, что я думаю, что я делаю с базой данных, на самом деле то, что я сделал. Или вы можете проверить, как показано ниже. Это так же просто, как вставить следующий код в ваш текущий комментарий, который выберет (и раскомментирует их), когда у вас есть параметр @test.

If @test =1
Begin
Select * from table1 where field1 = @myfirstparameter
End

Теперь вам не нужно проходить и комментировать и раскомментировать каждый раз, когда вы проверяете.

@test или @debuig всегда должны быть установлены со значением по умолчанию 0 и помещены последним в список. Таким образом, их добавление не нарушит существующие вызовы proc.

Учитывайте наличие журналов ведения журналов и/или ошибок для procs, выполняющих вставки/обновления/удаления. Если вы записываете шаги и/или ошибки в табличных переменных по мере их поступления, они по-прежнему доступны после откат, который необходимо вставить в таблицу протоколирования. Знание того, какая часть сложного процесса провалилась, и какая ошибка была бесценна позже.

По возможности не встраивайте хранимые процедуры. Если вам нужно запустить несколько записей в цикле, замените сохраненный proc на тот, у которого есть параметр таблицы, и настройте proc для запуска в режиме набора, а не в отдельной записи. Это будет работать, если параметр table-value имеет одну запись или много записей.

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

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

Все сохраненные procs (и другие коды базы данных) должны находиться в исходном элементе управления.

Табличные переменные хороши для небольших наборов данных, но временные таблицы (реальные, которые начинаются С# или ## не промежуточных таблиц) могут быть лучше для производительности в больших наборах данных. Если использование temp-таблиц отбрасывает их, когда они вам больше не нужны. Старайтесь избегать использования глобальных временных таблиц.

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

Ответ 2

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

Сохраненные процессы, которые передают данные друг другу в SQL Server вообще (больше параметров), могут быть проблематичными. Теперь есть табличные параметры, и многие вещи, которые ранее выполнялись с помощью procs, теперь можно выполнить с помощью встроенных табличных функций или (и обычно предпочтительных) многозначных табличных функций.

В SQL Server избегайте интенсивного использования скалярных функций и многозначной табличной функции на больших наборах строк - они не работают очень хорошо, поэтому модульные методы, которые могут показаться очевидными в С#, действительно не применимы здесь.

Я бы порекомендовал вам взглянуть на Руководство для гуру Ken Henderson для хранимых процедур SQL Server - опубликовано в 2002 году, оно по-прежнему содержит массу полезной информации при разработке приложений баз данных.

Ответ 3

Это такой хороший вопрос. Поскольку С# dev, которому приходится проваливаться в SQL, кажется, что SQL по самой своей природе мешает лучшим практикам, с которыми я привык с С#.

Общие выражения таблицы отлично подходят для изоляции запросов в хранимой процедуре, но вы можете использовать их только один раз! Это приводит к определению представлений, но затем вы потеряли инкапсуляцию.

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

Все это заставило меня со временем придерживаться простых хранимых процедур CRUD (которые не называют друг друга) в базе данных и несколько изолированных запросов, когда отношения сложны. Больше BI-материала. Все остальное находится в BLL.

Физически SQL изолирован в отдельных файлах по функциям или таблице, в которой они вращаются и управляются в исходном управлении.

Избегайте SELECT * и предпочитайте указывать столбцы. Это избавит вас от проблем во время работы при изменении таблицы и не трогайте все процессы. Да, есть перекомпиляция для procs, но она НЕ ПРОПУСТИТ, особенно если мнения задействованы. Кроме того, SELECT * почти всегда возвращает больше столбцов, чем вам действительно нужно, и что трата полосы пропускания.