Sql optimization: Xml or shared string

Question

This is hopefully just a performance optimization issue when it comes to queries in Sql 2008.

I worked for companies that reused Stored Procs for their ETL processes, as well as for some of their websites. I saw a scenario where they needed to get specific records based on a finite set of key values. I saw how it was processed in three different ways, illustrated using pseudo code below.

Dynamic Sql that concatenates a string and executes it.

EXEC('SELECT * FROM TableX WHERE xId IN (' + @Parameter + ')' 

Using a custom function to split table-delimited rows

 SELECT * FROM TableY INNER JOIN SPLIT(@Parameter) ON yID = splitId 

USING XML as a parameter instead of a varchar separable value

 SELECT * FROM TableZ JOIN @Parameter.Nodes(xpath) AS x (y) ON ... 

Although I know that creating dynamic sql in the first fragment is a bad idea for a number of reasons, my curiosity comes from the last two examples. Is it more appropriate to do the due diligence in my code to pass such lists through XML, like in fragment 3, or is it better to just delimit the values ​​and use udf to take care of this?

Answer 1

Currently there is a 4th option - table value parameters , through which you can actually pass the value table to sproc as a parameter and then use it, as usual, for a table variable. I would prefer this approach regarding XML (or the approach to parsing CSV)

I cannot bring performance indicators between all the different approaches, but I would try - I would recommend doing some real performance tests on them.

Edit:
A little more on TVP. To pass values ​​to your sproc, you simply define SqlParameter (SqlDbType.Structured) - this value can be set for any IEnumerable, DataTable, or DbDataReader source. Presumably, you already have a list of values ​​in a list / array of some type - you don't need to do anything to convert it to XML or CSV.

I think this also makes sproc cleaner, easier and more convenient to maintain, providing a more natural way to achieve the end result. One of the highlights is that SQL works best when dealing with a variety of grounded / non-circular / non-string operations.

Not to say that it does a great job with a large set of values ​​passed in. But with smaller sets (up to ~ 1000) this should be good.

Answer 2

UDF calling is a bit more expensive than breaking XML into an inline function.

However, this needs to be done only once per request, so the difference in performance will be negligible.