SQL performance improvement when choosing to perform calculations

Question

In the query below, is it better for performance to move the case statement to UDF? If so, why?

SELECT id,
       name,
       case
         when v1 = 'Y' then 'something'
         when v2 = 'K' then 'something else'
         when v3 is null then 'dont know'
         else 'default'
       end
from table

Answer 1

No, I would leave it as it is. Using UDF can (in general) have some wierd side effects and limitations , and this request does not justify using them. UDF performance should be no better than what you get from this direct request.

: http://www.bennadel.com/blog/964-SQL-User-Defined-Functions-Are-Slower-Than-Inline-Logic.htm

Answer 2

UDF , case . SQL .

Answer 3

, , UDF. Sql Server , UDF, , "inlined".

Answer 4

(, , ) "" case. , , , .

case , 3 , v1, v2 v3. , , v1, :

SELECT
   id,
   name,
   case Coalesce(v1, 'dont know')
      when 'Y' then 'something'
      when 'K' then 'something else'
      when 'dont know' then 'dont know'
      else 'default'
   end
from table

, , - , . , , .

, , UDF . , , ( ).

, , :

SELECT
   id,
   name,
   coalesce(x.result, 'dont know')
from
   table t
   left join (
       select 'Y', 'something'
       union all select 'K', 'something else'
       union all select '%', 'default'
   ) x (value, result) on t.v1 like x.value

, , LIKE. .

:

• , , . , 100 000 , , , . / .

• , , UDF cpu/read.

• It’s usually best to spend a bit of processor if it avoids even a few reads. The “constant crawl” displayed in the execution plans for views like I used above will always have a minimal cost. This cost is much less than almost any number of views associated with disk access.