Results 1 to 9 of 9
  1. #1
    Join Date
    May 2005
    Posts
    33

    Unanswered: Data access by creating functions

    a number of time I have come accross developers using functions like

    GetProduct()
    GetProductTitle()
    GetProductCategory()

    to get the value of the data instead of just using queries / stored procedures

    I have not understood why

    can you please point to some good forum messages / blogposts / articles on this ?

    Whats your take on this ?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dealing with functions that give direct results is easier programmatically than dealing with OUTPUT parameters in stored procedures.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    The concept of user defined functions is a great way to centralize code and they can come in very handy.

    But in my experience they have one major drawback: performance.

    When a user defined function needs to be performed on over a thousend rows (arbitrary boundery) the performance seems to decrease dramaticly. My guess it is because SQL Server has to switch from set-processing to row-by-row prossing.

    AmitGeorge: look in the BOL under CREATE FUNCTION. That doesn't say anything about performance though.

    Lex

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The use of UDFs will not cause the engine to stop using set-based processing. It's more likely due to the fact that UDF's tend to complex application-specific algorithms, otherwise they would be included in the standard stock of functions, right? And of course, there is no guarantee that the code in the UDF is GOOD code. MS hasn't yet come up with an error message that goes "You want me to run that? You can't be serious. Click OK to continue, or click Jeez What Was I Thinking to cancel."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Hi Blindman,

    A few months back I had to tune a stp which did a select which returned about 50 columns and about 25000 rows. Of these 50 columns 10 called a udf which only did the following:
    Code:
    CREATE FUNCTION convdate (@dtMyDate DATETIME)
    RETURNS VARCHAR(8)
    AS
    BEGIN
       RETURN CONVERT(VARCHAR(8), @dtMyDate, 112)
    END
    After replacing the functions calls with the CONVERT-statement in the select the procedure was about 10 times as fast!

    If SQL is still using set-based processing I can't explain why! Got any ideas?

    Lex

  6. #6
    Join Date
    May 2005
    Posts
    33
    i was not talking about using UDFs

    I am talking about functions in the client language like VB, PHP, Asp, Java etc

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Lexiflex
    After replacing the functions calls with the CONVERT-statement in the select the procedure was about 10 times as fast!

    If SQL is still using set-based processing I can't explain why! Got any ideas?
    Sorry. I just don't get the same results. I used these objects:
    Code:
    set nocount on
    go
    
    CREATE FUNCTION dbo.convdate (@dtMyDate DATETIME)
    RETURNS VARCHAR(8)
    AS
    BEGIN
       RETURN CONVERT(VARCHAR(8), @dtMyDate, 112)
    END
    
    GO
    
    CREATE TABLE TestDates (DateID int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED, DateValue datetime NOT NULL)
    
    Insert into TestDates (DateValue)
    select dateadd(Hour, Ones + Tens + Hundreds + Thousands + TenThousands, '2000-01-01')
    from	(select 0 as Ones
    	Union select 1
    	Union select 2
    	Union select 3
    	Union select 4
    	Union select 5
    	Union select 6
    	Union select 7
    	Union select 8
    	Union select 9) Ones,
    	(select 0 as Tens
    	Union select 10
    	Union select 20
    	Union select 30
    	Union select 40
    	Union select 50
    	Union select 60
    	Union select 70
    	Union select 80
    	Union select 90) Tens,
    	(select 0 as Hundreds
    	Union select 100
    	Union select 200
    	Union select 300
    	Union select 400
    	Union select 500
    	Union select 600
    	Union select 700
    	Union select 800
    	Union select 900) Hundreds,
    	(select 0 as Thousands
    	Union select 1000
    	Union select 2000
    	Union select 3000
    	Union select 4000
    	Union select 5000
    	Union select 6000
    	Union select 7000
    	Union select 8000
    	Union select 9000) Thousands,
    	(select 0 as TenThousands
    	Union select 10000
    	Union select 20000
    	Union select 30000
    	Union select 40000
    	Union select 50000
    	Union select 60000
    	Union select 70000
    	Union select 80000
    	Union select 90000) TenThousands
    order by Ones + Tens + Hundreds + Thousands + TenThousands
    Running these statements, I get virtually no difference in execution times:
    Code:
    declare	@StartTime datetime
    set	@StartTime = getdate()
    
    -- select	CONVERT(VARCHAR(8), DateValue, 112)
    -- from	TestDates
    
    select	dbo.convdate(DateValue)
    from	TestDates
    
    select	datediff(ms, @StartTime, GetDate())
    Regardless, you can check the execution plans for each statement and see that they are identical.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by AmitGeorge
    i was not talking about using UDFs

    I am talking about functions in the client language like VB, PHP, Asp, Java etc
    you got the wrong forum then.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by blindman
    MS hasn't yet come up with an error message that goes "You want me to run that? You can't be serious. Click OK to continue, or click Jeez What Was I Thinking to cancel."
    it appears 2005 does this by making sucky code suck more after the "upgrade".
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •