Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts

    eliminating repeated function calls for each record

    I have to optimize a (MSSQL 2000) query which will perform a call to a user defined function for each field in the returned recordset, like this:

    Code:
    select
      a * dbo.functionname(field10, field11, field12),
      b * dbo.functionname(field10, field11, field12),
      c * dbo.functionname(field10, field11, field12).
      .
      .
      .
    from
      thetable
    where field10, field11 and field12 are fields of the current record, as well as a, b and c.
    The function called is a bit heavy though, and there are really many records to return, and a lot of fields which need this multiplication

    What i want to accomplish is that for each record, the function is called once, and the result of the call will be used for the fields i want to retrieve.

    So something like:
    Code:
    declare @varname numeric (18,7)
    select
      -- first determine the result of the function and store that into a variable
      set @varname = dbo.functionname(field10, field11, field12)
      a * @varname,
      b * @varname,
      c * @varname.
      .
      .
      .
    from
      thetable
    But this doesn't work. Is this even possible in some way? I have spent half a day googling and diggin into sql server books online.
    I am the luckiest man in the world

  • #2
    New Coder
    Join Date
    May 2007
    Location
    SF, CA
    Posts
    55
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Have you tried something like this

    Code:
    select a * result,
             b * result,
             c * result
    from 
    (
      select a, b, c, dbo.functionname(field10, field11, field12) as result
        from thetable
    ) tmp
    semper fi...

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    No, not yet. I've tried to give the function result an alias in the select part of the query, that did not work because sql server does not know the alias 'result' before the resultset is sent to the client. That is also the reason why you cannot use aliases in join conditions etc. But i have not tried to apply the alias in the from clause of the query. Will do that.
    I am the luckiest man in the world

  • #4
    New Coder
    Join Date
    May 2007
    Location
    SF, CA
    Posts
    55
    Thanks
    0
    Thanked 4 Times in 4 Posts
    What's happening is that the query inside of the parentheses is creating what is called a derived table. If you were to break it down the query inside the parentheses is done first and then a table is created from the results which is used for the outer query.

    The other alternative is to use temporary tables. I've seen people resort to temporary tables, personally I'm not a big fan of it but to each their own.

    semper fi...

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    javabits,

    this took a while to incorporate that in my original query, because the original query did not fetch from one table, but from a rather complex join. But i was able to get it to work and the performance gain was more than 50% so thanks for your advice. i did not know the trick of the derived table, but it workes like a charm.

    Thanks again!

    Roelf
    I am the luckiest man in the world

  • #6
    New Coder
    Join Date
    May 2007
    Location
    SF, CA
    Posts
    55
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Glad I could be of assistance. I've found that by helping answer questions here, it actually helps me keeps my skills sharp as I've probably forgotten about as much as I learned. Honestly I didn't remember that it was called a derived table (thank goodness for google), but at least I still remembered how to use it.

    I've also found that within the industry developers who can communicate a solution are far more valuable.

    It's cool that you got a 50 percent performance gain too. Appreciate that type of feedback as that way everyone learns something.

    semper fi...


  •  

    Posting Permissions

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