SQL Server Performance

using fn to evaluate 'when - then' statement

Discussion in 'SQL Server 2005 General Developer Questions' started by gondar, Sep 13, 2006.

  1. gondar New Member

    Hi
    I am looking through a database schema where I have noticed that a particular
    function is code with a lot of

    select @ref = CASE @code
    WHEN agent THEN 46
    WHEN client THEN 47

    this goes on for about 300 WHEN statements.

    My question is would this be better in a table? Is this an eficient use of the function?
    My instinct is to make this into a table, but I am unsure how efficient sql server is with this type of value evaluation statement.

    thanks
    john
  2. FrankKalis Moderator

    From your code snippet, this looks like some really bad use of UDF's. On larger tables it is likely to be a performance killer. I guess I would also rather use a parameter table.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. Adriaan New Member

    Also review from where this UDF is being called. Looks like a poor design choice - like having one entity impersonate 300 different entities.
  4. gondar New Member

    thanks for the replies.

    the udf is being called - typically from other db procedures to evaluate the required number value of the parameter being supplied/called like

    select
    @agent = dbo.fn_GetRef(Ágent')

    I realise that this is poor design - but why is it poor use of udfs. For small evals like less that 10 I'd have no problems but there is 300 of these.

    thanks
  5. Adriaan New Member

    Where does the 'Agent' string come from? Why can't the source supply the numeric value instead?

    Looks like someone has replaced the "Select Case" syntax from VB (or similar) with the same in T-SQL. Evaluating 300 options will be handled much quicker if the options are recorded on an indexed column in a permanent table.

    It also allows you to use more abstract code, where the exact string ("Client" or "Agent") doesn't need to be mentioned, so you can expand the list of options without having to rewrite any code.
  6. Madhivanan Moderator

    If there are so many CASE WHENS then, why dont you store them in lookup table and join it main table?

    Madhivanan

    Failing to plan is Planning to fail
  7. gondar New Member

    thanks All.

    this is just the point - normally I would put this into a table. I need a reason why it shouldn't be designed like this using UDFs.
    I don't have any experience of UDFs so need a solid reason.

    The AGENT paramenter or any parameter being evalustaed in the function can come in as a parameter input from the user or another procedure which requires the evaluated numeric in order to look up some other value in another table or such

    thanks
  8. Adriaan New Member

    The two major points have already been discussed:

    Maintainability
    Adding a new option to the UDF involves changing code, with the risk of coding errors. With a lookup table, it's just data entry on a lookup table - if there's an error there then it will be easier to identify.

    Performance
    The lookup actions against the table may well be cached, whereas UDF evaluations must be repeated each and every time they are called.
  9. gondar New Member

    thanks Adriaan

    point requiring clarification.

    will the UDF be evaluated all the way to the end even if the value has been identified say midway through the When ...Then statement? or will it stop when a value that satisfys the when statement is found?

    once again thanks
  10. Roji. P. Thomas New Member

    quote:Originally posted by gondar

    thanks Adriaan

    point requiring clarification.

    will the UDF be evaluated all the way to the end even if the value has been identified say midway through the When ...Then statement? or will it stop when a value that satisfys the when statement is found?

    It WILL stop evaluating the WHEN , when the first match found.

    To see this in action execute the following code snippet.




    SELECT CASE WHEN 1=1 THEN 'OK'
    WHEN 1/0 = 0 THEN 'Not OK'
    ELSE 'This is unbelievable'
    END




    Note that if SQL Server has to evaluate the second expression, then the "Division by Zero" error will be thrown.

    Anyways scalar udfs are performance killers. The reason is that the udf is a cursor behind the scenes.

    SQL Server MVPs Kalen Delaney and Adam Machanic recently blogged about it here and here.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  11. gondar New Member

    thanks Roji

Share This Page