Я использую UDF's SQL для инкапсуляции простого создания отчетов/бизнес-логики. Я должен избежать этого?

В какой-то момент широкое использование UDFS начнет вызывать проблемы с производительностью, поскольку они выполняются для каждой строки в ваших результатах и ​​неясной логике из оптимизатора, что делает его трудно использовать индексы (то есть я Не понимаю, как производительность не может быть проблемой, но вы знаете, что ваши требования лучше всего). Для определенных функций они великолепны; Но используйте их экономно.

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

Я часто говорю так далеко, что и инкапсулируя логика из процедуры SQL, которая используется только в том, что одна процедура в хорошо называемую UDF для повышения удодоровимости.

Посмотрите на эту статью эта статья MSDN на UDFS - возможно, она даст вам еще несколько представлений об их использовании?

Существует различные соображения производительности, которые вам нужно будет знать, если вы намерены Чтобы использовать UDFS в значительной степени - такие вещи, как производительность скалярных, таблиц UDF и возможные преимущества CLR UDFS.

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

Сохранение логики со стороны БД почти всегда является правильным.

Как Вы уже упоминали в своем вопросе, большинство правил ведения бизнеса подразумевает достаточно простую логику, но, как правило, она касается огромных объемов данных.

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

Некоторое время назад я написал очень субъективный пост в блоге на эту тему:

• Schema Junk

Одно примечание: UDF - это не то же самое, что хранимая процедура.

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

Вы можете сделать гораздо больше - это хранимая процедура.

Обновление:

В приведенном вами примере, как и изменение логики, вычисляющей "производное поле", UDF, вычисляющий поле, является нормальным.

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

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

Один пример: ваш запрос имеет нечто вроде "оценки пользователя", которую вы считаете подверженной изменениям, и оберните ее в UDF

SELECT  user_id, fn_getUserScore(user_id)
FROM    users

Изначально это простое поле в таблице:

CREATE FUNCTION fn_getUserScore(@user_id INT) RETURNS INT
AS
BEGIN
        DECLARE @ret INT
        SELECT  user_score
        INTO    @ret
        FROM    users
        WHERE   user_id = @user_id
        RETURN @ret
END

, затем вы решаете вычислить его, используя данные из другой таблицы:

CREATE FUNCTION fn_getUserScore(@user_id INT) RETURNS INT
AS
BEGIN
        DECLARE @ret INT
        SELECT  SUM(vote)
        INTO    @ret
        FROM    user_votes
        WHERE   user_id = @user_id
        RETURN @ret
END

Это обрекает движок на использование наименее эффективного алгоритма NESTED LOOPS в обоих случаях.

Но если бы вы создали представление и переписали лежащие в его основе запросы следующим образом:

SELECT  user_id, user_score
FROM    users

SELECT  user_id, SUM(vote) AS user_score
FROM    users u
LEFT JOIN
        user_votes uv
ON uv.user_id = u.user_id

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

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

SSIS - очень мощный инструмент для ETL, который поставляется с SQL-сервером для таких вещей.