sp_executesql против пользовательской скалярной функции

Question

В таблице ниже я храню некоторые условия, подобные этим:

Затем, как правило, во второй таблице у меня есть следующие записи:

и мне нужно сравнить эти значения, используя правильное условие, и сохранить результат (скажем, "0" для "ложь" и "1" для "истина" в дополнительном столбце).

Я собираюсь сделать это в процедуре магазина, и в основном я собираюсь сравнить от нескольких до сотен записей.

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

Кто-нибудь может сказать, что является более эффективным способом?

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

Answer 1

Я не вижу необходимости в использовании sp_executesql в этом случае. Вы можете получить результат для всех записей одновременно в одном выражении:

select Result = case
    when ct.Abbreviation='=' and t.ValueOne=t.ValueTwo then 1
    when ct.Abbreviation='>' and t.ValueOne>t.ValueTwo then 1
    when ct.Abbreviation='>=' and t.ValueOne>=t.ValueTwo then 1
    when ct.Abbreviation='<=' and t.ValueOne<=t.ValueTwo then 1
    when ct.Abbreviation='<>' and t.ValueOne<>t.ValueTwo then 1
    when ct.Abbreviation='<' and t.ValueOne<t.ValueTwo then 1
    else 0 end
from YourTable t
    join ConditionType ct on ct.ID = t.ConditionTypeID

и обновите дополнительный столбец чем-то вроде:

;with cte as (
    select t.AdditionalColumn, Result = case
        when ct.Abbreviation='=' and t.ValueOne=t.ValueTwo then 1
        when ct.Abbreviation='>' and t.ValueOne>t.ValueTwo then 1
        when ct.Abbreviation='>=' and t.ValueOne>=t.ValueTwo then 1
        when ct.Abbreviation='<=' and t.ValueOne<=t.ValueTwo then 1
        when ct.Abbreviation='<>' and t.ValueOne<>t.ValueTwo then 1
        when ct.Abbreviation='<' and t.ValueOne<t.ValueTwo then 1
        else 0 end
    from YourTable t
        join ConditionType ct on ct.ID = t.ConditionTypeID
)
update cte
set AdditionalColumn = Result

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

create function ftComparison
(
    @v1 float,
    @v2 float,
    @cType int
)
returns table
as return
    select
        Result = case
            when ct.Abbreviation='=' and @v1=@v2 then 1
            when ct.Abbreviation='>' and @v1>@v2 then 1
            when ct.Abbreviation='>=' and @v1>=@v2 then 1
            when ct.Abbreviation='<=' and @v1<=@v2 then 1
            when ct.Abbreviation='<>' and @v1<>@v2 then 1
            when ct.Abbreviation='<' and @v1<@v2 then 1
            else 0
        end
    from ConditionType ct
    where ct.ID = @cType

который может быть применен тогда как:

select f.Result
from YourTable t
    cross apply ftComparison(ValueOne, ValueTwo, t.ConditionTypeID) f

или же

select f.Result
from YourAnotherTable t
    cross apply ftComparison(SomeValueColumn, SomeOtherValueColumn, @someConditionType) f