Вопрос по sql-server – SQLServer: Почему следует избегать пользовательских функций с табличными значениями?

4

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

Хотя я не знаю, что именно делает их медленными, я буду догадываться, что они есть, но, видя, что я не использую этот UDF в соединении, а вместо этого возвращает табличную переменную, я думаю, что это не будет будь таким плохим.

Так что я думаю, вопрос в том, стоит ли мне избегать UDF любой ценой? Кто-нибудь может указать на конкретные доказательства того, что они медленнее?

UDFs, кажется, получают плохой рэп. Мы широко используем UDF с табличными значениями для некоторых сложных вычислений с большими наборами данных. Производительность была превосходной. Многие из наших пользовательских функций также являются многострочными, что, по-видимому, не оказывает существенного влияния на производительность в нашем случае. David Taylor
однако многострочный UDF не может публиковать код. FlySwat
Можете ли вы опубликовать некоторые или все UDF, о которых вы спрашиваете? Это встроенная таблица UDF или многострочная? John Saunders

Ваш Ответ

4   ответа
3

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

Чтобы попытаться объяснить немного о том, почему UDF могут восприниматься как медленные (на самом деле просто используются неправильно), рассмотрим следующий пример;

У нас есть таблица A и таблица B. Скажем, у нас было соединение как

ВЫБРАТЬ     A.col1,     A.col2,     B.ColWhatever ОТ          ПРИСОЕДИНЯЙТЕСЬ К B A. A.id = b.fk_aid ГДЕ     B.someCol = @ param1 И A.anotherCol = @ param2

В этом случае SQL Server будет делать все возможное, чтобы возвращать результаты наиболее эффективным способом, который он знает как. Основным фактором в этом является сокращение чтения с диска. Таким образом, он будет использовать условия в JOIN и предложение where для оценки (возможно, с индексом) количества строк, которые нужно вернуть.

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

Допустим, мы создали UDF, чтобы выбрать строки из таблицы B, которые соответствуют предложению where. Если в таблице B 100 тыс. Строк, и 50% из них соответствуют критериям предложения where, то все эти строки будут возвращены в sproc для сравнения с таблицей A. Теперь, если только 10% из них имеют совпадения в таблице A, сейчас мы говорим только о 5% таблицы B, с которой мы хотим работать, но мы уже откатили 50%, большинство из которых мы не хотим!

Если это выглядит как полное извинение - пожалуйста, дайте мне знать!

Итак, вы говорите, что не используете скалярные UDF в предложении WHERE JOIN. В каких других ситуациях они замедляют ход событий? Также, если быть точным, в этом случае вы, кажется, говорите, что дело не в том, что сам UDF медленный, а в том, что в этом случае он препятствует оптимизации оптимизатора, что замедляет работу.
4

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

Повторно используйте ваш код с табличными UDF

Многие вложенные встроенные UDF работают очень быстро

Дополнительные ссылки на медлительность скалярных UDF:

Шаблоны производительности SQL Server UDF с параметрами datetime

Не все UDF плохо влияют на производительность

@AlexKuznetsov: не могли бы вы опубликовать ссылку, объясняющую, как и почему скалярный UDF работает медленно?
По моему опыту, вложенные встроенные UDF могут очень хорошо уменьшить сложность. Многократные операторы обычно (не всегда) несколько медленнее. Только сравнительный анализ может показать, насколько медленнее в вашем конкретном случае. Я бы сначала попробовал встроенные UDF.
Я использую мультиоператор возврата UDF, а не выбор одной таблицы, из-за сложности запроса ... Как вы думаете, это все еще быстро? FlySwat
@ Джон Сондерс: Ссылки добавлены. Первая, третья и четвертая ссылки объясняют это.
@AlexKuznetsov: все еще не уверен. Не могли бы вы проверить & quot; большой комментарий & quot; Я добавил к своему ответу и дал мне знать, если вы чувствуете разницу между двумя SELECTs?
-2
Извините, не можете сделать то, что в SQL Server? Хранимая процедура может вызвать другую хранимую процедуру.
Но вам не нужно делать SELECT * FROM EXEC Sproc. Просто сделайте EXEC SPROC.
Главным образом потому, что вы не можете сделать это в SQLServer, вы должны вставить возврат sproc во временную таблицу. FlySwat
@Flyswat: хорошая информация, которую вы могли бы включить для начала ...
Не могу выбрать SELECT * FROM EXEC Sproc, как в FireBird. Вместо этого вы должны создать временную таблицу и выбрать в нее. Я бы хотел этого избежать. FlySwat
0

если вы используете скалярный udf в предложении select запроса, операторы внутри udf будут выполняться один раз для каждой строки, возвращаемой из запроса. Было бы лучше выполнить соединение с табличным udf или найти какой-либо способ выполнить логику в вашем udf, используя соединение в вашем основном операторе SQL.

Я не использую это в соединении. Это UDF, который возвращает табличное значение. FlySwat

Похожие вопросы