SQL Server Performance

User defined function

Discussion in 'Performance Tuning for DBAs' started by durgesh, Sep 1, 2005.

  1. durgesh New Member

    We all know that if we use a user defined function it slows down the performance of the query.
    Could you please tell why it slows down the query and which "hint" should be used to optimise this?

    Durgesh.
  2. Madhivanan Moderator

    I think Functions are somewhat faster
    The performance depends on the way you write it

    Madhivanan

    Failing to plan is Planning to fail
  3. Adriaan New Member

    No, there's no guarantee that it will slow down all queries - there are many situations where a function outperforms a straight-forward construct, like serialization which otherwise would require looping through at least one cursor.<br /><br />No, there is no hint available. Check the Articles section for more information on how to optimize UDF performance. Like using a nested derived table that selects the distinct combinations of the function parameters (filtered with criteria) and then an outer derived table that calls the UDF for the distinct combinations, and then the main query that joins the outer derived table.<br /><br />But of course YMMV.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  4. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by durgesh</i><br /><br />We all know that if we use a user defined function it slows down the performance of the query.<br />Could you please tell why it slows down the query and which "hint" should be used to optimise this?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Do we? <br />A scalar UDF is evaluated, pretty much like a cursor, on a row-by-row basis. On larger tables this could slow down performance massively. <br /><br />Actually there is one hint how to optimise this: Evaluate on a case by case basis if there is not a better alternative. scnr [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  5. durgesh New Member

    Thanks Adriaan.
    But I have faced practical problem where in I am using a decryting function in the where clause and also the SDFuntion "UPPER()". If I remove this and compare directly without the decrypting function and the UPPER function then the query works faster.

    Durgesh.
  6. Adriaan New Member

    Why are you decrypting in the where clause - can't you do that in the UDF?
  7. FrankKalis Moderator

    ...or even better, do the decryption/encryption client-side and send them over the wire. Likely that there are stronger and faster algorithms available.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  8. durgesh New Member

    Yes exactly I am decrypting in a UDF which i am using the where clause.

    Durgesh.
  9. Adriaan New Member

    The data you're decrypting in the WHERE clause, is that unique for each row or does it repeat between rows? If it repeats, then you might try do the decryption in a derived table that uses a nested derived table that gives you the distinct encrypted data.

    If at all possible, filter the nested derived table, and use that for filtering the whole query.

    Something like this:

    [EDIT: This one needed more nesting levels than I thought ...]

    SELECT Tbl.*
    FROM Tbl
    INNER JOIN
    (SELECT T1.col1, T1.col2
    FROM (SELECT T2.col1, T2.col2, dbo.fnDecrypt(T2.col1, T2.col2)
    FROM (SELECT DISTINCT T3.col1, T3.Col2
    FROM Tbl AS T3
    -- And here's where the major filtering should take place!
    WHERE T3.col3 = <whatever>
    ) AS T2
    -- And here you can have the criteria for the decryption ...
    WHERE dbo.fnDecrypt(T2.col1, T2.col2) = 'x') AS T1) AS T
    -- And here you apply the filter on the main table by way of a join.
    ON Tbl.col1 = T.col1 AND Tbl.col2 = T.col2
  10. durgesh New Member

    I will elaborate with an example.............
    SELECT
    E.EmployeeID,
    dbo.Dec(E.EmployeeNumber) AS EmployeeNumber,
    dbo.Dec(E.LastName) AS LastName,
    dbo.Dec(E.FirstName) AS FirstName
    FROM
    Employee E
    INNER JOIN
    EmployeeDetail ED
    ON
    E.EmployeeID = ED.EmployeeID
    WHERE
    UPPER(dbo.Dec(ED.LastName)) = UPPER('Durgesh')
    AND
    dbo.Dec(E.Active) = 'Y'

    Where dbo.Dec() is a UDF for decrypting already encrypted string values(which were encrypted while inserting)....this reaslly makes the query slow.....

    Now if I use a variable for the same then it works faster........like below..

    DECLARE @LastName VARCHAR(50) , @Active CHAR(1)
    SELECT @LastName = UPPER(dbo.Enc('Durgesh')) , @Active = dbo.Enc('Y')

    SELECT
    E.EmployeeID,
    dbo.Dec(E.EmployeeNumber) AS EmployeeNumber,
    dbo.Dec(E.LastName) AS LastName,
    dbo.Dec(E.FirstName) AS FirstName
    FROM
    Employee E
    INNER JOIN
    EmployeeDetail ED
    ON
    E.EmployeeID = ED.EmployeeID
    WHERE
    ED.LastName = @LastName
    AND
    E.Active = @Active
    as now UDF is there in the where clause the query is faster......
    I am sure there is a "HINT" to work around such cases........
    dbo.Enc and dbo.Dec are optimised functions......with optimised encryption and decryption logic.......

    Thanks in advance......








    Durgesh.
  11. mmarovic Active Member

    The problem is not udf. To prove that try:
    SELECT
    E.EmployeeID,
    dbo.Dec(E.EmployeeNumber) AS EmployeeNumber,
    dbo.Dec(E.LastName) AS LastName,
    dbo.Dec(E.FirstName) AS FirstName
    FROM
    Employee E
    INNER JOIN
    EmployeeDetail ED
    ON
    E.EmployeeID = ED.EmployeeID
    WHERE
    ED.LastName = UPPER(dbo.Enc('Durgesh'))
    AND
    E.Active = dbo.Enc('Y')
    Btw what is the purpose of encripting Yes and No flag?

    The problem with original query is that you can't effectively use index. To be able to take advantage of indexes you have to have condition like:

    <column name> <relational operator> <expression>

    where relational operator is either =, <=, >= or like.

    On top of that <column name> between <expression 1> and <expression 2> also takes advantage of indexes because it is translated to <column> >= <expression 1> and <column> <= <expression 2>

    <column> like <expression> can effectively use index only if it is <column> like <constant string> + <wildcards symbols>
  12. durgesh New Member

    The Flag need not be encrypted but for coding convention of encrypting strings it has been written but can be removed also.......

    Durgesh.

Share This Page