3

I have a situation where I have to decide between receiving XML as an input parameter to the stored procedure or a list of comma separated values and parse them using a multi-table valued function.

Logically speaking, I would prefer XML over udfs coz' udfs can cause a lot of performance issues. But I see that XML in the backend also uses table valued functions to parse the xml. I wanted to know if XML is indeed better than Multi table UDF and if so, what makes it better?

CREATE FUNCTION [dbo].[tmp_array_commaValues] ( @string varchar(4000))
RETURNS  @final TABLE(Value varchar(100))
AS
begin

    WITH tmp_cte(start, stop) AS

    (
      SELECT  1, CHARINDEX(',' , @string )
      
      UNION ALL
      
      SELECT  stop + 1, CHARINDEX(',' ,@string  , stop + 1)
      FROM tmp_cte
      WHERE stop > 0
    )

    insert into @final
    SELECT   SUBSTRING(@string , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue
    FROM tmp_cte   

    return
end   

/* query */
Select value 
  From [dbo].[tmp_array_commaValues]('TAG,ABC,ZYX,BAG,TRY,LAG,LEAD,NETHERLANDS,TAG1,ABC1,ZYX1,BAG1,TRY1,SUN12,NANGE1')

/********** XML *********/
Declare @xml XML = '<root><id>Tag</id><id>Lag</id>
<id>Tag1</id><id>Lag1</id>
<id>Tag2</id><id>Lag2</id>
<id>Tag3</id><id>Lag3</id>
<id>Tag4</id><id>Lag4</id>
<id>Tag5</id><id>Lag5</id>
<id>Tag6</id><id>Lag6</id>
<id>Tag7</id><id>Lag7</id>
</root>'

select t.c.value('.','varchar(100)') as string
  From @xml.nodes('root/id/text()') As t(c)

Execution Plan for UDF: https://www.brentozar.com/pastetheplan/?id=SJpX2DMwi

Execution Plan for XML: https://www.brentozar.com/pastetheplan/?id=ByQv2PMvo

0

1 Answer 1

4

udfs can cause a lot of performance issues

This isn't necessarily true. Poor code is going to cause performance problems regardless of where it's put. There are some drawbacks with certain types of UDFs though:

  1. Scalar functions are ran RBAR (row by agonizing row) up until SQL Server 2019, where certain scalar functions are inlinable.
  2. Scalar functions prevent parallelism in any plan of any object that references them directly or any dependency object at any part of the call stack.
  3. Multi-Statement Table Valued Functions prevent parallelism in the zone of which they are referenced in the query plan.

It's best practice to generally not use scalar functions, because of their multiple drawbacks, whereas a single-statement table valued function is quite fine. Multi-statement table valued functions are less problematic, since they only are serialized where they are referenced in the query plan, but the query plan as a whole can still have other parallelized zones.

For further reading on parallelism inhibiting things in SQL Server, please see Paul White's article Forcing a Parallel Query Execution Plan .

I wanted to know if XML is indeed better than Multi table UDF and if so, what makes it better?

As with most performance things, I'd say it depends on exactly what you're doing with XML vs your multi-statement table valued function. With the execution plans you've provided, at a quick glance, it seems like the MSTVF may perform better (as it's processing less data, and just a simpler execution plan with less steps, in general). But I can't say conclusively.

You can use Profiler or Extended Events with the SQL:BatchCompleted event or run SET IO, TIME STATISTICS ON and look in the Messages window in SSMS to see CPU Time, Elapsed Time, and other TIME STATISTICS and IO STATISTICS to help gauge which one executes more efficiently. The one with less CPU Time, lower Elapsed Time, and / or less Logical Reads are general factors you can use to determine which one is more performant.

Also, Erik Darkling's suggestion to use Table-Valued Parameters in the procedure is usually a good practice to do. This would be my go-to of the 3 choices.

Does table valued parameters have any advantages over XML or string parsing?

Yes, the data is already presented in a tabular format and doesn't need to be processed (on the SQL Server) to get there. The only tangible downside to using them is they're presented as a table variable which SQL Server doesn't maintain statistics on. My usual workaround for that is to immediately SELECT the table variable into a temp table, and then use that temp table throughout the procedure instead.


We're still on SQL Server 2012

I highly recommend looking to upgrade soon as that's four major versions behind the latest, and is also no longer in support by Microsoft. The extended support date ended last July (2022).

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.