Performance of CROSS/OUTER APPLY against CLR UDF that calls a web service RRS feed

  • Question

  • For various reasons, our application employs a CLR UDF that accesses an external (SOAP) web service. This web service takes a few parameters, looks up the appropriate customer record, and returns a few columns of information. In most cases, we're doing an OUTER APPLY against this function to add supplemental columns to just a couple of rows. Obviously, SQL Server invokes the function once for each row, and has to call the web service multiple times accordingly. When dealing with only two or three rows, performance is adequate.

    Now we have a need to bind a GridView in an ASP.NET front-end using data from this web service, which of course means getting similar functionality with a much larger data set. Naturally, using OUTER APPLY and making 50 or more calls to the web service performs horribly, so I'm looking for alternatives. Ideally, I'd like to be able to collect all the values that need to be looked up, make a single call to the web service (we could potentially modify it to accept multiple sets of arguments to support this), and return a single set of data that's joined to the initial results originating within SQL Server.

    As far as I can tell, it's not possible to use table-valued parameters with a CLR UDF, nor do I know of any way to do CROSS/OUTER APPLY such that the other tables in your query are passed entirely as a single parameter. I could live with stuffing the initial query into a temporary table, which is then passed as a parameter to the UDF and joined right back to the temporary table, but that doesn't get around the inability to pass a table-valued parameter to a CLR UDF.

    Anybody done anything like this before and have any good recommendations for keeping the performance reasonable?

    Tuesday, June 28, 2011 7:02 PM


  • Hi davidbrit2,

    Yes, I agree with you that returning a single result is a better way. In your case, calling the Web Service once for each row in the table will inevitably impact the query performance.  

    I think we try using a temporary or permanent to hold the returned result set and then replace the CROSS/OUTER APPLY with JOINs in your current query.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    • Marked as answer by KJian_ Friday, July 8, 2011 4:55 AM
    Tuesday, July 5, 2011 5:43 AM