Post reply

Performance of UDF inside a VIEW

  • November 8, 2007 at 10:20 am

    #183809

    Hello,

    I have two views one, which contains a UDF call to decrypt a specific column, and another that does not have the UDF but decrypt directly in the view.

    Code is provided below, but basically vwEncryption1 (with UDF) runs a lot slower than vwEncryption2.

    Can someone explain why? When I generate an Actual Query Execution Plan, they are both performing a table scan (as expected) with the same costs and overheads, and I have no indication as to how the UDF is being called.

    I would guess for every row scanned there is a single call to the UDF?

    Can anyone see a more efficient way to write this?

    By the way the reason I have a UDF is because I want the decryption code to run as the DBO, and not give the user permissions to do this. So I am seperating it out, and only giving the user select on the view.

    Thanks for any help...

    Code Example:-

    CREATE FUNCTION [dbo].[udf_Decrypt]

    (@Encrypted VARBINARY(8000))

    RETURNS VARCHAR(8000)

    WITH EXECUTE AS 'DBO'

    AS

    BEGIN

    RETURN CONVERT(VARCHAR(8000),DecryptByKeyAutoAsymKey(AsymKey_ID('TESTAsyKey'),NULL,@Encrypted))

    END

    GO

    CREATE VIEW [dbo].[vwEncryption1]

    AS

    SELECT

    *,

    dbo.udf_Decrypt(Encrypted) AS [Decrypted]

    FROM dbo.tblEncryption

    GO

    CREATE VIEW [dbo].[vwEncryption2]

    AS

    SELECT

    *,

    CONVERT(VARCHAR(8000),DecryptByKeyAutoAsymKey(AsymKey_ID('TESTAsyKey'),NULL,Encrypted)) AS [Decrypted]

    FROM dbo.tblEncryption

  • November 9, 2007 at 6:44 am

    #749824

    inline functions tend to turn set based queries into cursor based queries, this would be probably worse within a view.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • November 12, 2007 at 2:39 am

    #750174

    Thanks Colin,

    Hope things are going well for you, you probably remember me from one of your previous contracts. 🙂

  • November 12, 2007 at 3:50 am

    #750185

    can you give me a clue? phone a friend ? ask the audience ?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply