Sql 优化:Xml 或分隔字符串 [英] Sql Optimization: Xml or Delimited String

问题描述

当涉及到 Sql 2008 中的查询时，这希望只是一个涉及性能优化的简单问题.

This is hopefully just a simple question involving performance optimizations when it comes to queries in Sql 2008.

我曾为那些在 ETL 流程以及一些网站中使用存储过程的公司工作过很多次.我见过他们需要根据一组有限的键值检索特定记录的场景.我已经看到它以 3 种不同的方式处理，如下面的伪代码所示.

I've worked for companies that use Stored Procs a lot for their ETL processes as well as some of their websites. I've seen the scenario where they need to retrieve specific records based on a finite set of key values. I've seen it handled in 3 different ways, illustrated via pseudo-code below.

连接字符串并执行它的动态 Sql.

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

使用用户定义的函数将分隔的字符串拆分成表格

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

使用 XML 作为参数而不是分隔的 varchar 值

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

虽然我知道在第一个片段中创建动态 sql 出于多种原因是一个坏主意，但我的好奇心来自最后两个示例.在我的代码中进行尽职调查以通过 XML 传递此类列表是否更熟练，如代码段 3 中那样，还是仅分隔值并使用 udf 来处理它更好?

While I know creating the dynamic sql in the first snippet is a bad idea for a large number of reasons, my curiosity comes from the last 2 examples. Is it more proficient to do the due diligence in my code to pass such lists via XML as in snippet 3 or is it better to just delimit the values and use an udf to take care of it?

推荐答案

现在有第四个选项 - 表值参数，您实际上可以将值表作为参数传递给 sproc，然后像通常的表变量一样使用它.我更喜欢这种方法而不是 XML(或 CSV 解析方法)

There is now a 4th option - table valued parameters, whereby you can actually pass a table of values in to a sproc as a parameter and then use that as you would normally a table variable. I'd be preferring this approach over the XML (or CSV parsing approach)

我无法引用所有不同方法之间的性能数据，但这是我会尝试的一种 - 我建议对它们进行一些实际的性能测试.

I can't quote performance figures between all the different approaches, but that's one I'd be trying - I'd recommend doing some real performance tests on them.

关于 TVP 的更多信息.为了将值传递到您的 sproc，您只需定义一个 SqlParameter (SqlDbType.Structured) - 这个值可以设置为任何 IEnumerable、DataTable 或 DbDataReader 源.因此，大概您已经拥有某种列表/数组中的值列表 - 您无需执行任何操作即可将其转换为 XML 或 CSV.

A little more on TVPs. In order to pass the values in to your sproc, you just define a SqlParameter (SqlDbType.Structured) - the value of this can be set to any IEnumerable, DataTable or DbDataReader source. So presumably, you already have the list of values in a list/array of some sort - you don't need to do anything to transform it into XML or CSV.

我认为这也使 sproc 更清晰、更简单和更易于维护，提供了一种更自然的方式来实现最终结果.要点之一是 SQL 在基于集合/非循环/非字符串操作活动中表现最佳.

I think this also makes the sproc clearer, simpler and more maintainable, providing a more natural way to achieve the end result. One of the main points is that SQL performs best at set based/not looping/non string manipulation activities.

这并不是说它在传入大量值时会表现出色.但使用较小的值集(最多约 1000 个)应该没问题.

That's not to say it will perform great with a large set of values passed in. But with smaller sets (up to ~1000) it should be fine.

这篇关于Sql 优化:Xml 或分隔字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！