SQL Functions VS JOINS

  • So we are thinking about about creating SQL functions to call look up data for our application. Are there any drawbacks in using functions instead of doing a JOIN. Does this effect preformance at all?

  • In my opinion, functions are OK if you are getting a single row of data. but if you are getting more than one row, a functions should be replaced with a join;

    functions are called fo reach row, and lose out on the performance advantage a join would use.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I'm a little confused on the idea of a function vs. a join. If you have two sets of data that you want to put together, you have to do a join.

    But, that said, assuming you're talking about user defined functions, they can be very problematic. It really depends on how much data you're moving and whether or not the function is inline. If it's inline, a single statement UDF, it can be very fast, more or less like a parameterized view. If it's a multi-statement UDF, it's going to tend to be very problematic because it uses table variables. Table variables have no statistics, which means that execution plans made for them are based off of one row. As long as you're only returning one row, or a few rows, you'll be fine. Once it's hundreds or thousands... performance gets very bad.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Lowell (9/18/2009)


    In my opinion, functions are OK if you are getting a single row of data. but if you are getting more than one row, a functions should be replaced with a join;

    functions are called fo reach row, and lose out on the performance advantage a join would use.

    I'm not sure I folow this argument. If the function itself returns all of the information you need, and there is no need to join this data to anything else, then the frunction is called just once, and essentially acts as a parameterized view.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Yes, but what is occuring inside the function? Is it a single statement function with a query against a single table just with parameters or are multiple tables involved in which case, you have a function, but it has joins... I'm still stuck on this comparison between a join and a function.

    A join in TSQL:

    SELECT *

    FROM TableA as A

    JOIN TableB as B

    ON A.ID = B.ID

    LEFT JOIN TableC as C

    ON B.ID2 = C.ID2

    WHERE A.ID = 42

    A user defined function in TSQL:

    CREATE FUNCTION MyFunction

    (@Param1 int)

    RETURNS TABLE

    AS

    RETURN

    (SELECT *

    FROM TableA as A

    JOIN TableB as B

    ON A.ID = B.ID

    LEFT JOIN TableC as C

    ON B.ID2 = C.ID2

    WHERE A.ID = @Param1)

    Joins are there in both. Where is the comparison between the two?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The short answer is they are not related JOINs in ANSI SQL are compared to Subqueries and UNION because very long ago JOINs are two or more UNIONs combined. I think you will still find code doing that I have seen some here not very long ago.

    In relational algebra JOINs are idempotent but most vendor extentions comes with updatable JOINs there is a movement to remove that in future SQL Server.

    🙂

    Kind regards,
    Gift Peddie

  • The big problem with functions that perform queries, is that they cause hidden joins, hence should be transformed to (well tunable) regular joins !

    Functions aren't always visible at first sight when a dev or dba is interpreting a query.

    The same question goes for "functional views". They have their purpose, but are far to often abused, causing performance problems.

    Once these abuses are rewritten to optimal sql performance problems disapear.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Would putting the queries in a Sproc be faster? I thought both functions and sprocs parsed and pre-compiled the data. Meaning that performance would be better? Well maybe not functions but sprocs for sure.

    As long as the underlying tables were frequently changing I would have thought performance would have been better.. with a sproc that is ( not sure about UDF)

  • raym85 (9/20/2009)


    Would putting the queries in a Sproc be faster? I thought both functions and sprocs parsed and pre-compiled the data. Meaning that performance would be better? Well maybe not functions but sprocs for sure.

    As long as the underlying tables were frequently changing I would have thought performance would have been better.. with a sproc that is ( not sure about UDF)

    The huge difference of a UDF vs a SPROC is that a UDF is called by a query statement (or at column level (scalar udf) or at from level (table tvf), a sproc is executed by a exec statement.

    You cannot use a sproc within a query like you can use a udf.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • cm62597 (9/18/2009)


    So we are thinking about about creating SQL functions to call look up data for our application. Are there any drawbacks in using functions instead of doing a JOIN. Does this effect preformance at all?

    I'm not going to guess... tell us just exactly what you mean about using SQL functions to call lookup data for your application. Give us an example of what you have in mind.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • raym85 (9/20/2009)


    Would putting the queries in a Sproc be faster? I thought both functions and sprocs parsed and pre-compiled the data. Meaning that performance would be better? Well maybe not functions but sprocs for sure.

    As long as the underlying tables were frequently changing I would have thought performance would have been better.. with a sproc that is ( not sure about UDF)

    There's no "pre". It's parsed and compiled on first execution, and then the plan is reused on future executions (in most cases).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • This really is one of my pet hates. By using scalar udfs , you are seriously handicapping your system. Apart from the obvious overhead of calling a function, SQLServer will not be able to pick an optimal query plan. The overhead can be easily demonstrated in AdventureWorks

    create function GetSalesTotal(@SalesOrderId integer)

    returns money

    as

    begin

    declare @Total money

    Select @total = sum(LineTotal)

    from Sales.SalesOrderDetail

    where SalesOrderId = @SalesOrderID

    return @Total

    end

    go

    select SalesOrderHeader.SalesOrderID,SUM(Linetotal)

    from Sales.SalesOrderHeader

    join Sales.SalesOrderDetail

    on Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID

    group by Sales.SalesOrderHeader.SalesOrderID

    go

    Select SalesOrderID,dbo.GetSalesTotal(SalesOrderId)

    from Sales.SalesOrderHeader

    Using profiler :

    Join CPU 297 Reads 1941 Dur 294

    Udf CPU 1391 Reads 95960 Dur 1387.

    So in that respect it really is a no brainer.

    If you really must use a udf then try to use an inline one, they are much more efficient as they are effectively views.

    create function dbo.GetSalesTotalinline(@SalesOrderId integer)

    returns table

    as

    return(

    Select SalesTotal = sum(LineTotal)

    from Sales.SalesOrderDetail

    where SalesOrderId = @SalesOrderID

    )

    go

    Select SalesOrderID, SalesTotal

    from Sales.SalesOrderHeader cross apply dbo.GetSalesTotalinline(SalesOrderId)

    Complex logic can usually be broken down into a series of CTE's.



    Clear Sky SQL
    My Blog[/url]

Viewing 12 posts - 1 through 11 (of 11 total)

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