Database Function VS Case Statement

Question

Yesterday we got a scenario where had to get type of a db field and on base of that we had to write the description of the field. Like

Select ( Case DB_Type When 'I' Then 'Intermediate'
                      When 'P' Then 'Pending'
                      Else 'Basic'
         End)
From DB_table

I suggested to write a db function instead of this case statement because that would be more reusable. Like

Select dbo.GetTypeName(DB_Type) 
from DB_table

The interesting part is, One of our developer said using database function will be inefficient as database functions are slower than Case statement. I searched over the internet to find the answer which is better approach in terms of efficiency but unfortunately I found nothing that could be considered satisfied answer. Please enlighten me with your thoughts, which approach is better?

Is this `dbo.GetTypeName(DB_Type)` have the same `CASE` expression? i.e: You are asking about the difference between writing `CASE` expression directly into a query, and putting it in a user defined function ***in terms of performance and re-usability*** . Right? — Mahmoud Gamal, Nov 08 '12 at 07:17

Thangamani Palanisamy · Accepted Answer · 2012-11-08T07:55:07.220

UDF function is always slower than case statements

Please refer the article

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/10/06/performance-benefits-of-using-expression-over-user-defined-functions.aspx

The following article suggests you when to use UDF

http://www.sql-server-performance.com/2005/sql-server-udfs/

Summary :

There is a large performance penalty paid when User defined functions is used.This penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more. The penalty is incurred because the SQL Server database engine must create its own internal cursor like processing. It must invoke each UDF on each row. If the UDF is used in the WHERE clause, this may happen as part of the filtering the rows. If the UDF is used in the select list, this happens when creating the results of the query to pass to the next stage of query processing. It's the row by row processing that slows SQL Server the most.

So predefined functions also falling in this case. if yes then let start avoiding sql predefined function like IsNull. — AGH, Aug 23 '19 at 11:34

score 1 · Answer 2 · edited Nov 13 '18 at 23:16

1

Your devolper is right. Functions will slow down your query.

https://sqlserverfast.com/?s=user+defined+ugly

Calling functionsis like:
wrap parts into paper
put it into a bag
carry it to the mechanics
let him unwrap, do something, wrapt then result
carry it back
use it

edited Nov 13 '18 at 23:16

Community

1
1

answered Nov 08 '12 at 07:19

bummi

27,123
14
62
101

1

Even if it has the same `CASE` expression? – Mahmoud Gamal Nov 08 '12 at 07:22

score 1 · Answer 3 · answered Nov 08 '12 at 07:33

When using a scalar function (a function that returns one value) the contents of the function will be executed once per row but the case statement will be executed across the entire set.

By operating against the entire set you allow the server to optimise your query more efficiently.

So the theory goes that the same query run both ways against a large dataset then the function should be slower. However, the difference may be trivial when operating against your data so you should try both methods and test them to determine if any performance trade off is worth the increased utility of a function.

Database Function VS Case Statement

3 Answers3

Linked